Macro using Vlookup formula

[cpisthedbb]

Hi,

I'm fairly new to macros and am struggling writing one despite spending hours searching google for the answers. I'm hoping someone here could help.

Most of my spreasheet is already populated but a couple of cells I have to enter my own data into.

The following vlookup formula fills in the missing data I need:

=IFERROR(VLOOKUP(C2,'U:\[Monthly Reporting.xlsm]Monthly Score Tracker'!$A$2:$I$1000,2,FALSE),0)

As its only required for a few cells I want to only put this in the missing ones and don't want to fill the formula all the way down my spreadsheet.

What I'm after is the macro is needed for this to fill the blank cell when I run the macro.
It will always be the cell to the right of it as well.
For example if cell D5 is missing I would need to run the following vlookup:


=IFERROR(VLOOKUP(C5,'U:\[Monthly Reporting.xlsm]Monthly Score Tracker'!$A$2:$I$1000,2,FALSE),0)



Is is possible for anyone to help with this? It's probably really simple when you know how!

 

[Joe4]

What range of cells are you looking in for blanks?
Is the lookup value it should use always be one cell to the left of where the formula is being placed (i.e. formula in D5 looking up value in C5)?

 

[cpisthedbb]

What range of cells are you looking in for blanks?
Is the lookup value it should use always be one cell to the left of where the formula is being placed (i.e. formula in D5 looking up value in C5)?

Yes the blanks are all in the D column and the vlookup will be pulling from the C.
For example if D46 is empty I need the vlookup to be:
=IFERROR(VLOOKUP(C46,'U:\[Monthly Reporting.xlsm]Monthly Score Tracker'!$A$2:$I$1000,2,FALSE),0)

 

[Joe4]

Try this:

Sub MyLookupMacro()

    Dim lr As Long
    
'   Find last row with daa in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row

'   Populate all blank cells in column D with formula
    Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],'U:\[Monthly Reporting.xlsm]Monthly Score Tracker'!R2C1:R1000C9,2,FALSE),0)"
        
End Sub

 

[cpisthedbb]

Try this:

Sub MyLookupMacro()

    Dim lr As Long
    
'   Find last row with daa in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row

'   Populate all blank cells in column D with formula
    Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],'U:\[Monthly Reporting.xlsm]Monthly Score Tracker'!R2C1:R1000C9,2,FALSE),0)"
        
End Sub

That's absolutely brilliant - Thanks for this, worked first time. Looks so simple as well.

Quick question so I know were I'm going wrong in future - why does the vlookup formula range change when doing a macro? I would never have been able to work this out!

 

[Joe4]

why does the vlookup formula range change when doing a macro? I would never have been able to work this out!

It is simply using R1C1 range referencing. This allows you to create dynamic formulas, where certain range references are determined relative to where the formula is being placed, i.e.
RC[-1] simply pulls the value from one cell to the left of where the formula is placed
R2C1:R1000 is the hard-coded range $A$2:$I$1000
(a number in square brackets means relative reference, where numbers outside are hard-coded; see: https://excelchamps.com/blog/r1c1-reference-style/)

The good news is that you do not need to know how to write it - to get that formula in VBA, simply turn on your Macro Recorder, and record yourself entering the formula in a cell on your worksheet, then stop the Recorder and view your code. You will have the formula in that format. Let Excel do the work for you!

 

[cpisthedbb]

It is simply using R1C1 range referencing. This allows you to create dynamic formulas, where certain range references are determined relative to where the formula is being placed, i.e.
RC[-1] simply pulls the value from one cell to the left of where the formula is placed
R2C1:R1000 is the hard-coded range $A$2:$I$1000
(a number in square brackets means relative reference, where numbers outside are hard-coded; see: https://excelchamps.com/blog/r1c1-reference-style/)

The good news is that you do not need to know how to write it - to get that formula in VBA, simply turn on your Macro Recorder, and record yourself entering the formula in a cell on your worksheet, then stop the Recorder and view your code. You will have the formula in that format. Let Excel do the work for you!

That's great thanks for your help - it's one of the areas I need to improve.

 

[Joe4]

You are welcome.

The Macro Recorder is a great little tool to get little snippets of code, especially things like this, converting worksheet formula to VBA format.