1. ## Simplifying VBA

Hi Guys,

I am trying to come up with ways to simplify my code and I am sort of stuck, is there a better way to do the bold lines?

Code:
```'...........................................................................
Case 10, 11, 12, 13, 14, 17, 18, 19, 20, 21                                 'change detected in Cost or Return Item Price
'...........................................................................

Select Case zCol
'.......................................................................
Case [f1].Column, [m1].Column, [t1].Column, [aa1].Column                'price entry
'.......................................................................
Application.EnableEvents = False                                        'turn events OFF during changes
If Target.Offset(0, -2) <> "" Then                                      'check for blank in Qty cell..
If Target <> "" Then                                                'check for blank in Price cell..
Target.Offset(0, 1) = Target.Offset(0, -2).Value * Target.Value     'calculate total cost..
End If
End If

'.......................................................................'when Cost > Price is updated, update Total Cost, Profit and Profit Margin..
If zRow = 10 Then
LID = Target.Offset(-8, -4)
End If
If zRow = 11 Then
LID = Target.Offset(-9, -4)
End If
If zRow = 12 Then
LID = Target.Offset(-10, -4)
End If
If zRow = 13 Then
LID = Target.Offset(-11, -4)
End If
If zRow = 14 Then
LID = Target.Offset(-12, -4)
End If
If zRow = 17 Then
LID = Target.Offset(-15, -4)
End If
If zRow = 18 Then
LID = Target.Offset(-16, -4)
End If
If zRow = 19 Then
LID = Target.Offset(-17, -4)
End If
If zRow = 20 Then
LID = Target.Offset(-18, -4)
End If
If zRow = 21 Then
LID = Target.Offset(-19, -4)
End If```

Thanks for looking...
Ferenc

2. Figured it out...

'................................................. ..........................
Case 10, 11, 12, 13, 14, 17, 18, 19, 20, 21 'change detected in Cost or Return Item Price
'................................................. ..........................

Select Case zCol
'................................................. ......................
Case [f1].Column, [m1].Column, [t1].Column, [aa1].Column 'price entry
'................................................. ......................
Application.EnableEvents = False 'turn events OFF during changes
If Target.Offset(0, -2) <> "" Then 'check for blank in Qty cell..
If Target <> "" Then 'check for blank in Price cell..
Target.Offset(0, 1) = Target.Offset(0, -2).Value * Target.Value 'calculate total cost..
End If
End If

'................................................. ......................'when Cost > Price is updated, update Total Cost, Profit and Profit Margin..
r = zRow - 2
LID = Target.Offset(-r, -4)

3. Ferenc,

Unless you need the value r for some other purpose simply LID = Target.Offset(-(zRow-2),-4)

HTH

4. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Ferenc Nagy (2015-07-19)

5. Originally Posted by RetiredGeek
Ferenc,

Unless you need the value r for some other purpose simply LID = Target.Offset(-(zRow-2),-4)

HTH

You are right...

6. I have one that I could not figure out...

Code:
```    If zRow = 4 Then
LID = Target.Offset(-11, 4)
End If
If zRow = 5 Then
LID = Target.Offset(-10, 4)
End If
If zRow = 6 Then
LID = Target.Offset(-9, 4)
End If
If zRow = 7 Then
LID = Target.Offset(-8, 4)
End If
If zRow = 8 Then
LID = Target.Offset(-7, 4)
End If```
Any ideas for an easier VBA here?

7. Figured it out again

If zRow = 4 Then
LID = Target.Offset(-11, 4)
End If
If zRow = 5 Then
LID = Target.Offset(-10, 4)
End If
If zRow = 6 Then
LID = Target.Offset(-9, 4)
End If
If zRow = 7 Then
LID = Target.Offset(-8, 4)
End If
If zRow = 8 Then
LID = Target.Offset(-7, 4)
End If

Code:
`LID = Target.Offset(-(15-zRow), 4)`

8. Hi Ferenc

..you are making great progress with your coding!
It's not too important to eliminate all steps, if it makes it easier to follow.
So, although RG is correct in post#3, your post#2 would perhaps be easier to document.
Top marks for reporting back - it is sure to help others too.

zeddy
•Pickled Banana Historian

9. Thank you for the kind words

The code was just an example, I actually had it wrong and in the original r = target.Row so it makes sense to simplify it, as it is my notes next to the code that makes it easy to follow...

10. Originally Posted by Ferenc Nagy
as it is my notes next to the code that makes it easy to follow...
Ferenc,

That is the greatest coding advance you could possibly make IMHO! Code all looks great when it is first coded six months later w/o comments it is mostly gibberish even to the coder, at my age it doesn't even take that long!

11. Back again, but this time I have a wild one

I am able to identify the location ID of the item and I know the column.
I want to figure out the row matching the location ID without searching all the trades...
Any thoughts would be welcome...

Code:
```  |   B |   I  |   P  |  W
---------------------------------
2|  1  |   2  |   3  |   4
26|  5  |   6  |   7  |   8
50|  9  |  10  |  11  |  12
74|  13 |  14  |  15  |  16
98|  17 |  18  |  19  |  20
etc```
Thanks for looking...

12. Worked it out again

Code:
```x = Location ID
y = Target.Row

Case B
y = x * 6 - 4

Case I
y = (x - 1)* 6 - 4

Case P
y = (x - 2) * 6 - 4

Case W
y = (x - 3) * 6 - 4```

13. Ferenc,

Here's a little test code you might find interesting. Both Subs will return the same results.
Code:
```Option Explicit

Sub Test1()

'x = Location ID
'y = Target.Row

Dim x      As Long
Dim y      As Long

x = 10

Select Case Chr(ActiveCell.Column + 64)

Case "B"
y = x * 6 - 4

Case "I"
y = x * 6 - 10

Case "P"
y = x * 6 - 16

Case "W"
y = x * 6 - 22
End Select

Debug.Print Chr(ActiveCell.Column + 64), y

End Sub 'Test1

Sub Test2()

'x = Location ID
'y = Target.Row

Dim x      As Long
Dim y      As Long
Dim z      As Long

x = 10
z = InStr("BIPW", Chr(ActiveCell.Column + 64)) - 1 'Sub 1 so if Col B = 0
y = x * 6 - (4 + (z * 6))

Debug.Print Chr(ActiveCell.Column + 64), y

End Sub  'Test2```
HTH

14. In the Worksheet_Change sub this is the part that would call in the function or subroutine to refresh the cost total, return total, total profit & profit margin...

Code:
```    'Cost total..
LID = Cells(8 - r, zCol)
J = zCol
TotalUpdate```
but I am now sure which one this should be...

Code:
```If J = "b" Then
I = LID * 6 - 4
ElseIf J = "i" Then
I = LID * 6 - 10
ElseIf J = "p" Then
I = LID * 6 - 16
ElseIf J = "w" Then
I = LID * 6 - 22
End If

.Cells(I + 13, J + 5) = WorksheetFunction.Sum(Range(.Cells(I + 8, J + 5), .Cells(I + 12, J + 5)))   'Cost Total
.Cells(I + 20, J + 5) = WorksheetFunction.Sum(Range(.Cells(I + 15, J + 5), .Cells(I + 19, J + 5)))  'Return Total
.Cells(I + 22, J + 5) = .Cells(I + 20, J + 5) - .Cells(I + 13, J + 5)                               'Total Profit
.Cells(I + 22, J) = .Cells(I + 22, J + 5) / .Cells(I + 13, J + 5)                                   'Profit Margin
End With```
Still not familiar enough with the basics...

15. Ferenc,

You can replace this:
Code:
```If J = "b" Then
I = LID * 6 - 4
Else If J = "i" Then
I = LID * 6 - 10
Else If J = "p" Then
I = LID * 6 - 16
Else If J = "w" Then
I = LID * 6 - 22
End If```
With this:
Code:
` I = LID * 6 - (4 +  ( (InStr("BIPW", UCase(J) ) - 1) * 6))`
Here's what's going on:
1. Ucase makes sure you're comparing Uppercase to uppercase.
2. Instr finds the location of J in the string "BIPW" so returns 1-4.
3. We subtract 1 to get 0-3 so the multiplication part works as 4 is the one that is not up by 6 from the last.
4. Now it's just a formula to do the math (remember 0 times anything is 0!)

HTH

16. Originally Posted by RetiredGeek
Ferenc,

You can replace this:
Code:
```If J = "b" Then
I = LID * 6 - 4
Else If J = "i" Then
I = LID * 6 - 10
Else If J = "p" Then
I = LID * 6 - 16
Else If J = "w" Then
I = LID * 6 - 22
End If```
With this:
Code:
` I = LID * 6 - (4 +  ( (InStr("BIPW", UCase(J) ) - 1) * 6))`
Here's what's going on:
1. Ucase makes sure you're comparing Uppercase to uppercase.
2. Instr finds the location of J in the string "BIPW" so returns 1-4.
3. We subtract 1 to get 0-3 so the multiplication part works as 4 is the one that is not up by 6 from the last.
4. Now it's just a formula to do the math (remember 0 times anything is 0!)

HTH

While I do understand what is happening thanks to your description, I rather keep the other one because I would not remember it 6 months down the line

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•