Results 1 to 15 of 15
Thread: Simplifying VBA

20150719, 11:01 #1
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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) updateTrade End If If zRow = 11 Then LID = Target.Offset(9, 4) updateTrade End If If zRow = 12 Then LID = Target.Offset(10, 4) updateTrade End If If zRow = 13 Then LID = Target.Offset(11, 4) updateTrade End If If zRow = 14 Then LID = Target.Offset(12, 4) updateTrade End If If zRow = 17 Then LID = Target.Offset(15, 4) updateTrade End If If zRow = 18 Then LID = Target.Offset(16, 4) updateTrade End If If zRow = 19 Then LID = Target.Offset(17, 4) updateTrade End If If zRow = 20 Then LID = Target.Offset(18, 4) updateTrade End If If zRow = 21 Then LID = Target.Offset(19, 4) updateTrade End If
Thanks for looking...
FerencLast edited by Ferenc Nagy; 20150719 at 11:05.

20150719, 16:42 #2
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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)
updateTrade

20150719, 17:00 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 412
 Thanked 1,573 Times in 1,424 Posts
Ferenc,
Unless you need the value r for some other purpose simply LID = Target.Offset((zRow2),4)
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
Ferenc Nagy (20150719)

20150719, 17:36 #4
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts

20150719, 23:20 #5
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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
Last edited by Ferenc Nagy; 20150719 at 23:43.

20150719, 23:45 #6
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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((15zRow), 4)

20150720, 03:06 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,238
 Thanks
 154
 Thanked 612 Times in 582 Posts
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.
Your post#6 shows you have learnt from RG's advice.
Top marks for reporting back  it is sure to help others too.
zeddy
•Pickled Banana Historian

20150720, 04:17 #8
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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...

20150720, 08:59 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 412
 Thanked 1,573 Times in 1,424 Posts
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150720, 09:29 #10
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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
Last edited by Ferenc Nagy; 20150720 at 09:32.

20150720, 09:54 #11
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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
Last edited by Ferenc Nagy; 20150720 at 10:00.

20150720, 10:29 #12
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 412
 Thanked 1,573 Times in 1,424 Posts
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
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150720, 10:35 #13
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts
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
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 With shtTrades .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
Last edited by Ferenc Nagy; 20150720 at 10:53.

20150720, 11:06 #14
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 412
 Thanked 1,573 Times in 1,424 Posts
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
Code:I = LID * 6  (4 + ( (InStr("BIPW", UCase(J) )  1) * 6))
 Ucase makes sure you're comparing Uppercase to uppercase.
 Instr finds the location of J in the string "BIPW" so returns 14.
 We subtract 1 to get 03 so the multiplication part works as 4 is the one that is not up by 6 from the last.
 Now it's just a formula to do the math (remember 0 times anything is 0!)
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150721, 09:39 #15
 Join Date
 May 2015
 Posts
 104
 Thanks
 15
 Thanked 0 Times in 0 Posts