[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Excel VBA Help with Dynamic VLOOKUP Range

I have a large macro that gets data from many sheets and combines it into a template monthly.

At this point in the macro I have a workbook open with 2 sheets of data and the focus is at the last row of Sheet2

I also have a template open that has 4 columns that will reference 2 cols in each data sheets.
I wanted to use Vlookup from the Template which will always be in the same cells.
From that I will reference the data sheets which also always has the same columns but
the data is DYNAMIC monthly so I need a lookup down to a varaible number of rows.

On the Template, Col C has the lookup value for all 4 data columns.
The reference ranges on the data sheets will be Cols K to T or K to U.

I have tried to reuse some code that worked earlier in the sheet and have posted it below.  
Thank you for your assistance and direction.
I would like to learn as I go so any help there is also appreciated.
' Copy OGM into Template using Vlookup
    ' OGM Part$ Plan from ("MakePLGFP.xls") ColU
    Set rngFormula2 = Range(Selection, Selection.End(xlDown))
'** Values recorded =VLOOKUP(C7,[MakePLGFP.xls]Sheet1!$K$1:$X$126,10,FALSE)
     strLookup2 = "=VLOOKUP(RC[-4],[MakePLGFP.xls]Sheet1!R1C11:R200C20,10,FALSE)"
     strFormula2 = "=IF(ISERROR(" & strLookup2 & "), 0, " & strLookup2 & ")"
     rngFormula2.FormulaR1C1 = strFormula2
** Application defined error at the line above   
 ' OGM Part$ Actual from ("MakePLGFP.xls")  ColT
    Set rngFormula = Range(Selection, Selection.End(xlDown))
     strLookup3 = "=VLOOKUP(RC[-5],[MakePLGFP.xls]Sheet1!C11:C19,9,FALSE)"
     strFormula3 = "=IF(ISERROR(" & strLookup3 & "), 0, " & strLookup3 & ")"
     rngFormula.FormulaR1C1 = strFormula3
 ' Copy CRU into Template 
    ' CRU Part$ Plan from ("MakePLGFP.xls")  ColU
    Set rngFormula = Range(Selection, Selection.End(xlDown))
     strLookup4 = "=VLOOKUP(RC[-4],[MakePLGFP.xls]Sheet2!C11:C20,10,FALSE)"
     strFormula4 = "=IF(ISERROR(" & strLookup4 & "), 0, " & strLookup4 & ")"
     rngFormula.FormulaR1C1 = strFormula4
    ' CRU Part$ Actual from ("MakePLGFP.xls")  ColT
    Set rngFormula = Range(Selection, Selection.End(xlDown))
     strLookup5 = "=VLOOKUP(RC[-5],[MakePLGFP.xls]Sheet2!C11:C19,9,FALSE)"
     strFormula5 = "=IF(ISERROR(" & strLookup5 & "), 0, " & strLookup5 & ")"
     rngFormula.FormulaR1C1 = strFormula5

Open in new window

  • 3
  • 2
1 Solution
can you please ask a more specific question, I am not sure what your question is.
MswetskyAuthor Commented:
Thanks for the reply.
Line 9 gets an error and I don't know why or what to change.
try removing the equals sign from
strLookup2 = "=VLOOKUP(RC[-4],[MakePLGFP.xls]Sheet1!R1C11:R200C20,10,FALSE)"

to make it
strLookup2 = "VLOOKUP(RC[-4],[MakePLGFP.xls]Sheet1!R1C11:R200C20,10,FALSE)"
i would also consider changing your code to the following, if it does the same;

     strLookup2 = "VLOOKUP(RC[-4],[MakePLGFP.xls]Sheet1!R1C11:R200C20,10,FALSE)"
     strFormula2 = "=IF(ISERROR(" & strLookup2 & "), 0, " & strLookup2 & ")"
     Range("G7", Cells(Sheets("Sheet1").UsedRange.Rows.Count, 7).Address).FormulaR1C1 = strFormula2
MswetskyAuthor Commented:
Thank you very much!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now