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]

This should work for you:

Sub nestedloop()
Dim intLastColNum As Integer
Range("A1").Select
'Need to add 1 to last column number to enter a formula to the right of the last column
intLastColNum = ActiveSheet.UsedRange.Columns.Count + 1
Do While ActiveCell.Column <= intLastColNum
    If ActiveCell = "" Then
        'Go to Row 3 of current column
        ActiveCell.Offset(2, 0).Select
        'Insert formulas in rows of current column
        Do While ActiveCell.Offset(0, -1) <> ""
            ActiveCell.Formula = "=YourFormula"
            ActiveCell.Offset(1, 0).Select
        Loop
        'go to first row of next column
        ActiveCell.Offset(-ActiveCell.Row + 1, 1).Select
    Else
        'go to next column
        ActiveCell.Offset(0, 1).Select
    End If
Loop
End Sub

 

[Marmot329]

Thank you so much, I spent all day on this yesterday and achieved nothing and this very nearly does everything I need. It's also very different from what i was trying to attempt!

Just a couple more questions if I may?

1) The loop does not appear to come to an end once it's been run, it continues until i crash out the macro. Is there a way to code it so that it stops when there is no data in the left hand side column of where the formula goes?

2) Is there a way to write my formula using R1C1 referencing so that I 'fix' part of it to a particular cell, but that cell then moves across as it loops through the columns?

For example, my formula for column B is basically creating a new look up code, using the code in cell A1. This is the formula I want it to create and loop down the rows:

=$A$1&" "&A3&" Equity"
=$A$1&" "&A4&" Equity"
=$A$1&" "&A5&" Equity"

To create the following codes from the data above:

SAI AH Equity
SAI AT Equity
SAI PQ Equity...

Then in column D I want it to be:

=$C$1&" "&C3&" Equity"
=$C$1&" "&C4&" Equity"
=$C$1&" "&C5&" Equity"

To create the codes as follows:

BKN AH Equity
BKN AT Equity
BKN GF Equity

If i fix my formula to be say R1C1 the loop across the sheet is referencing cell A1 for all columns, and I need this to move across to the next columns code.

Many thanks again for the help.

 

[ryansclev]

For number 1, the code

intLastColNum = ActiveSheet.UsedRange.Columns.Count + 1

looks for the last column with data or formatting and adds one. It assumes that the column to the right of the last column with data or formatting is the last cell you want to populate. Do you have any formatting to the right of your data? Does the highlighting in row 2 go throughout the whole sheet? If so that could be why. You can delete all formatting to the right and it should work. Also, if your last column is static and will never change, say its column S, then the last column that you want to enter your formula would be column T, you can force the code to stop when it reaches column U:

Do While ActiveCell.Column <= 21

For your second issue, this will work. But it creates a string, not a formula, so if you wanted to change the value in column a and have the value in column b update you would have to run the macro again:

Do While ActiveCell.Offset(0, -1) <> ""
            strEquityString = Cells(1, ActiveCell.Column - 1) & " " & ActiveCell.Offset(0, -1) & " Equity"
            ActiveCell = strEquityString
            ActiveCell.Offset(1, 0).Select
        Loop

If you would rather have your code enter a formula you can use:

Do While ActiveCell.Offset(0, -1) <> ""
            ActiveCell.Formula = "=" & Cells(1, ActiveCell.Column - 1).Address & "&" & Chr(34) & " " & Chr(34) & _
                                "&" & ActiveCell.Offset(0, -1).Address & "&" & Chr(34) & " Equity" & Chr(34)
            ActiveCell.Offset(1, 0).Select
        Loop

So your entire code (stopping at column U and using a string) would be:

Sub nestedloop()
Dim strEquityString As String
Range("A1").Select
'Enter Last Column Number to enter formula
Do While ActiveCell.Column <= 21
    If ActiveCell = "" Then
        'Go to Row 3 of current column
        ActiveCell.Offset(2, 0).Select
        'Insert formulas in rows of current column
        Do While ActiveCell.Offset(0, -1) <> ""
        'create a string of the first row of the prvious column and the value of the cell in the
        'current row or the previous column
            strEquityString = Cells(1, ActiveCell.Column - 1) & " " & ActiveCell.Offset(0, -1) & " Equity"
            ActiveCell = strEquityString
            ActiveCell.Offset(1, 0).Select
        Loop
        'go to first row of next column
        ActiveCell.Offset(-ActiveCell.Row + 1, 1).Select
    Else
        'go to next column
        ActiveCell.Offset(0, 1).Select
    End If
Loop
End Sub

 

[Marmot329]

Magic! This works perfectly, thank you so much I opted to use the formula rather than the string, as I need to edit it to pull data from another source.

And you were right about point 1, i did have some formatting extend past my data range.

This little bit of code will save me having to type out 850 numbers into Excel every month!

 

[Marmot329]

Could I ask one further question please RyansClev? I thought it would be really straight forward to edit the formula to be something else and I've run into difficulty.

So the formula currently puts in the code =$A$1&" "&A3&" Equity" - i tried to edit this so it's =BDP(A$1&" "&A3&" Equity","VOLUME_AVG_6M") and I'm getting a debug message.

This is what I tried to put in:

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

 

[ryansclev]

chr(34) is the ascii character code for quotation marks. Since the code expexts a string using actual quotation marks in the code will tell it to stop the string instead of what you are meaning to do. So thats why in the formula, " Equity" is Chr(34) & " Equity" & Chr(34) in code. So in the last line you added, you don't have a starting quote and you are placing closing quotes outside of the closing parenthesis. I'm just looking at this real quick but in the last line should probably be:

& "," & Chr(34) & "Volume_AVG_6m" & chr(34) & ")"

 

[Marmot329]

Understood re chr(34).

I copied the code into word and tried to follow the same logic as above, and I came to the same conclusion as you for the last line, however I'm still getting the error...

ActiveCell.Formula = "=" & Chr(34) & “BDP” & Chr(34) & "(" & 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 shall do some trial and error to try and figure out what's going on here.

 

[ryansclev]

BDP shouldn't have chr(34) quotes around it, that's the name of your function and not a literal string you want displayed. Try:

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) & ")"

 

[Marmot329]

BDP shouldn't have chr(34) quotes around it, that's the name of your function and not a literal string you want displayed. Try:

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) & ")"

It doesn't seem to like the edits on either end of the formula, I added the two new components separately as well as altogether

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

ActiveCell.Formula = "=" & 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) & ")"



Both give an error, and when I copy in the formula as you've written it above. Is it possible there's some kind of restriction at work with what I'm trying to do?