Nested Loops; Loop down rows then loop through columns

[Marmot329]

Afternoon All,

I'm hitting a bit of a brick wall trying to create some code to do some very onerous data manipulation in excel 2010. I was hopeful someone here might be able to able to point me in the right direction. My VBA is very much a beginners standard so please bear with me.

I am attempting to nest a loop within another loop; below is a snapshot of my spreadsheet.

[/URL]

I need to insert a formula in the empty columns to the right hand side of the data labels (e.g. columns B, D, F etc), if there is a data label to the left (e.g activecell.offset(0,-1). Once it has looped down the rows to a point there is no label to the left, I need it to repeat the process for the next empty column (i.e. jump 2 columns to the right and repeat).

I have some code to loop down which works for the first column, but i want to remove the fixed cell reference B3 to be in the form "For I = 3 To FinalRow" and then I want to wrap it in another loop to go to the next column, eg "For J = 2 to FinalColumn Step 2"

Sub InsertFormulaTest()

Range("B3").Select
Do
ActiveCell.FormulaR1C1 = _
"=BDP(R1C1&"" ""&RC[-1]&"" Equity"",""volume_avg_6m"")"
'Loops through all the rows in a column to download data for each code
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub

The formula itself relates to an Excel add in, so the formula will populate with an error - what i'm most concerned with is getting the formula in each of the required cells. The formula references the code in row 1 for every other column (eg SAI, BKN, EBS etc) and the labels going down the rows (eg AH, AT, PQ etc).

Please help!

 

[ryansclev]

Are you getting an error in the code or as a result of the formula? I looked some stuff up, I am assuming that is the Bllomberg Data Point Add-in? I can't install add-ins at work, but when I run the code, it puts the formula correctly into the cell.

Another wierd thing, when I copy and pasted my code that I posted in the previous post, I got a compile error, but when I typed this all out, I didn't.

ActiveCell.Formula = "=BDP(" & Cells(1, ActiveCell.Column - 1).Address & "&" & Chr(34) & " " & Chr(34) & _
"&" & ActiveCell.Offset(0, -1).Address & "&" & Chr(34) & " Equity" & Chr(34) & "," & Chr(34) & "VOLUME_AVG_6M" & Chr(34) & ")"

I can also copy and paste this one with no error, though it looks identical to the one that is giving me the error. Maybe try this one or try typing it out?

ETA - I figured out why I was getting a compile error, the closing quotes around BDP( were not the correct quotes to use in the first post from this morning.

 

[Marmot329]

That works perfectly now - thank you very much for your assistance, much appreciated.

And yes BDP is Bloomberg Data Point.