Hello there! This is my first post in a forum like this for VBA advice. I'm certainly new at this. Any feedback is welcome and very appreciated!
I inherited an expense spreadsheet project from someone else and was tasked with improving it. The original macro would analyze the values in columns J (and sometimes H) of rows 10-end and based on the values of those columns it would take the cost amount stored in column B and transcribe it to the appropriate cost-center column (M-AB). I was asked to get the same logic to run on any cost values in column C (It's worth mentioning that if C is not a zero-string, B is, so no data will be overwritten).
I started by adding the following statements:
Sub Load_Values()
Dim colarray(1) As Variant
Dim ws As Worksheet
Dim ap As Application
colarray(0) = 2
colarray(1) = 3
Set ws = ActiveSheet
For kk = 0 To 1
Then I altered the existing select case logic to use the variables above:
i = 10
Do Until ws.Range("A" & i).Value = ""
Select Case Left(ws.Range("J" & i).Value, 7)
Case " 112142", " 112151", " 112136", " 112134"
ws.Range("W" & i).Value = Format(ws.Cells(i, colarray(kk)).Value, "Currency")
...
At the end of the case logic, which I'm not changing and is very long, so I'm abbreviating it, here's how I ended the macro:
End Select
i = i + 1
Loop
Next
End Sub
The whole thing works EXCEPT it only runs on column C now, not B and C. I can only guess there's something wrong about the way I'm specifying that the macro run on kk = 0 and kk=1 (and, thus colarray(0) = 2), but I can't figure it out. Any advice would be greatly appreciated; I'll even take suggestions for a completely different method. Thanks!
I inherited an expense spreadsheet project from someone else and was tasked with improving it. The original macro would analyze the values in columns J (and sometimes H) of rows 10-end and based on the values of those columns it would take the cost amount stored in column B and transcribe it to the appropriate cost-center column (M-AB). I was asked to get the same logic to run on any cost values in column C (It's worth mentioning that if C is not a zero-string, B is, so no data will be overwritten).
I started by adding the following statements:
Sub Load_Values()
Dim colarray(1) As Variant
Dim ws As Worksheet
Dim ap As Application
colarray(0) = 2
colarray(1) = 3
Set ws = ActiveSheet
For kk = 0 To 1
Then I altered the existing select case logic to use the variables above:
i = 10
Do Until ws.Range("A" & i).Value = ""
Select Case Left(ws.Range("J" & i).Value, 7)
Case " 112142", " 112151", " 112136", " 112134"
ws.Range("W" & i).Value = Format(ws.Cells(i, colarray(kk)).Value, "Currency")
...
At the end of the case logic, which I'm not changing and is very long, so I'm abbreviating it, here's how I ended the macro:
End Select
i = i + 1
Loop
Next
End Sub
The whole thing works EXCEPT it only runs on column C now, not B and C. I can only guess there's something wrong about the way I'm specifying that the macro run on kk = 0 and kk=1 (and, thus colarray(0) = 2), but I can't figure it out. Any advice would be greatly appreciated; I'll even take suggestions for a completely different method. Thanks!