Help needed understanding VBA in Excel

[charlie84]

Hi, I've had the unfortunate task of trying to understand a piece of VBA written by another user (clearly more proficient than me) and amend it.

See below

Sub Agency()
Dim i As Long, tgt As Long
Dim cols As Variant

cols = Array(2, 15, 28, 41, 54, 67, 80, 93)

For i = 3 To 502

tgt = Cells(i, 28) - 1

cl = cols(tgt) + (Cells(i, 22) + 2)

rw = ((5 * Cells(i, 23)) + 3) + (Cells(i, 24) - 1)

Cells(i, 15) = Round(Worksheets("Info").Cells(rw, cl) * 100, 1)
Next i

The section in red I'm having the most problems with. I know this is probably simple, but I have very limited VBA experience. Any advice would be great.

Thanks!!!

 

[Richard Schollar]

Hi Charlie & Welcome to the Board!

Firstly, that is poorly written code without explanatory comments so please don't feel bad either about asking for help or not being able to understand it - it isn't obvious by any means what it is doing.

That being said, the purpose of the code is to take values on the currently active sheet when the code is run from rows 3 to 502 and lookup a specific cell on sheet Info. The specific cell on sheet Info is determined by the values in the activesheet in columns 28 (AB), 22 (V), 23 (W) and 24 (X). The value from sheet Info is returned to column 15 (O) of the Activesheet (having first performed a mathematical opearation on it - ie multiplication and rounding).

That's the summary version. To see exactly what it is doing I would open up the VBE, and with the corretc sheet active in Excel (ie visible in Excel) just step thru each line of code using the F8 function key. This will execute each line sequentially. You will be bale to see what the value of the variables (eg rw and cl) are by hovering your mouse over the variables in the VBE.

 

[shiguera]

The line:
tgt = Cells(i, 28) - 1

reads the value in row i, column 28 (or column AB) and subtracts 1, and assigns the result to tgt.

Let's see the first pass, when i=3 and for example purposes write a value of 5 in AB3 (cells(3,28)) and write a 10 in cell V3 (cells(3,22)):
tgt = 5-1 = 4
cols(tgt)=cols(4), which is the 4th item in the array cols. The first value in the array is col(0)=2 in this case, so col(4)=54
Therefore,
cl = 54 + (10+2) = 66
rw=(5*0+3) -1=2 because we didn't write any values in cells (3,23) nor (3,24), which are W3 and X3 respectively.

Then, cell (3,15) is assigned the value of cell (2,66) of sheet2, multiplied by 100 and rounded off to one decimal place.

The process repeats for the next value of i.

Hope this helps

 

[charlie84]

I don't want to speak to soon...but you've both helped alot much. I was about to give up!!

Just have to try and amend it now, but understanding it is half the battle won.

I'm use to SAS/SQL coding and the logic is slightly different. VBA is something I would like to understand better just don't know where to start!

Thanks again!