VBA - Run same logic on values in columns 2 & 3

Joined
May 3, 2013
Messages
1
Reaction score
0
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top