Loops within loops?

Hi all,

Hope all is well in your macro worlds and beyond!

Trying to accomplish a loop within a loop for LOOKUP data from 12 worksheets into a summary sheet.

The macro is meant to do the following:
1. Check if column B in the summary sheet contains a value - if so:
a) Input into column G in the summary sheet a VLOOKUP to that data sheet to pull in a value (or 0.00 IFERROR).
b) Do as (1a) for each of the 12 data sheets (columns G to R in total).
c) Cycle through each code in column B completing (1a) and (1b).
2. Otherwise cycle through to the next code in column B.

I get an object defined error on my FormulaR1C1 - I think I have not correctly written it so the macro can use the k variable for each data worksheet number (1-12).

I have posted the formula and worksheet:

 Sub VLOOKUP_DATA()

    'Turns off screen updating.
    Application.ScreenUpdating = False
    
    
    '****************************** LOOKUP LOOP *****************************

    'Variable for the codes column.
    For i = 14 To 1000
    
    'Check a code exists in the code column.
    If Sheets("Summary").Cells(i, 2) <> 0 Then
    
    'Variable for the monthly data columns.
    For j = 6 To 16
    
    'Variable for the montly data sheet names.
    For k = 1 To 12
    
    'Formula to look up the 7th column in each monthly data sheet.
    Sheets("Summary").Cells(i, j).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-j+1], 'Data - Month & k'!C[-j]:C, 7, FALSE),0)"
    
    Next k
    
    Next j
    
    End If
    
    Next i
    

    '****************************** CLOSE *****************************

    'Turns on screen updating.
    Application.ScreenUpdating = True


End Sub

I would greatly apprecaite any input on correcting / improving my code if willing!


Many thanks for any help in advance!


Coeus.

'Formula to look up the 7th column in each monthly data sheet.
Do you mean the return values comes from column G if column B of summary sheet and column A of the rest of sheets match.
In other words, do you just want to return a single value? What would happen if there are duplicate codes on sheets?

Hi mikerickson,

I have input that but then the workbook loops through and tries to open another workbook!

Had to CTRL ALT DELETE to close the Excel programme :-(

Hi AB33. There will never be duplicate codes on the sheet - the data comes from a database where each code is a primary key.

Yeah your explanation matches what is the desired outcome!


Thanks for taking a look :D

Coeus,
If the return values comes from column G, Am I right this is copied in to summary G14 and down or is it G14 across?
You mentioned "columns G to R in total)".

Hi Coeus,
try it

Sub VLOOKUP_DATA()
Dim i&, j&
Application.ScreenUpdating = False
With Sheets("Summary")
    For i = 14 To .Cells(Rows.Count, 2).End(xlUp).Row
        For j = 1 To 12
            .Cells(i, j + 6).FormulaR1C1 = _
            "=IFERROR(VLOOKUP(RC2, 'Data - Month " & j & "'!C1:C7, 7, 0),0)"
        Next j
    Next i
End With
Application.ScreenUpdating = True
End Sub

Try the attached.

Many many thanks to all that have input!

I have gone with nilem's approach as looks the simplest and got the result!

If I can ask a few questions on your code so I understand?


Dim i&, j& - do these need to be defined? In my original code I just noted 'For i = 1 to 1000'?

With Sheets("Summary") - I never use the 'With' function mainly out of ignorance - is this better than directly defining the sheet as in my original code ' If Sheets("Summary").Cells(i, 2) <> 0 Then'?

.Cells(Rows.Count, 2).End(xlUp).Row - now this looks like it removes my limit on i being 1000. 1000 was more than likely ever to be required - yours more efficiently limits to the end of the row if I understand which is much more efficient! If you could clarify a bit for me on this:

- Rows.Count - this counts to the final row i.e. ignores and blanks and keeps going until no data exists below a point in the column?

- .End(xlUp).Row - why is this necessary? I would imagine the 'Rows.Count' defines the very last row in the column so .Cells(Rows.Count, 2) would be sufficient?

" & j & " - this is why my VLOOKUP was not working - I have to seperate any variable within a code using & VARIABLE &.

Lastly you removed my k variable and instead noted j + 6 - genius!


Again many thanks learning so much!


Coeus.

Dim i&, j& - do these need to be defined? In my original code I just noted 'For i = 1 to 1000'?
No necessary! In VBA, unlike some other languages, do not need to define your variables, but for efficiency and tracing of errors, it is a good practice to declare your variables with option explicit.
'For i = 1 to 1000'?
you are looping 1000 times. For speed purpose, you should only loop up to the last non empty row with data.

With Sheets("Summary")
Again, you can work with out WITH END WITH, but for efficiency running of code and to avoid repeating the same statement again and again, it is a good practice to use if you understand it.

.Cells(Rows.Count, 2).End(xlUp).Row - now this looks like it removes my limit on i being 1000. 1000
this avoids using a fixed loop 1 to 1000. Your data may only gone down to ten rows, yet you are looping 1000 times, so use the last non empty row with data.


" & j & " - this is why my VLOOKUP was not working
if you have a string with your code, you should put them in quotation mark

j + 6
means increase the column by for each loop.
I hope this helps a bit.

@AB33 Thanks AB, it's great

Nilem,
It is a beer time!
Any Russian beer you can recommend?