Using VBA set arrays in vlookup

[RobbertH]

Gurus,

I have a piece of code that determines positions in an worksheet and performs actions at relative places from these positions. Now i need to put in a vlookup formula that uses these position or arrays in this case. The line of code i'm talking about is shown below. Obviously this is not working but I cant think of how to make it work.

Rng.Offset(, 6).FormulaR1C1 = "=VLookup(Rng, RngRng.Resize(, 3), 2, False) - VLookup(Rng, RngRngRng.Resize(, 3), 3, False)"

Anybody any idea how to get this work.

background:
Basically what is happening is the following:
I have three tables with data. These table vary in size and thus the relative place where the table starts and ends. The first two tables contrain data that is compared in table three.

I figured that the whole code that is running wont make my explanation more clear.

Rng = the Range of key indicators used for the lookup value in the vlookup formula
RngRng = the Array within the vlookup should find its data (table 1)
RngRngRng= the Array with the vlookup should find its data (table 2)

Sorry for the confusing range names ;P

 

[RobbertH]

Not being an expert in vba syntax, I'm not entirely sure why this was failing, but I have found a cure.

For some reason "rngrng.Resize(,3)" doesn't return a valid range, use of .Address cured the code error, but the formula was written incorrectly.

I could be reading your code wrong somewhere, but it looks like your range is always in columns A:C, with the rows changing, which the following should take care of.

rng.Offset(, 6).Formula = "=VLOOKUP(RC[-6],R" & RngRng.Row & "C1:R" & RngRng.Rows.Count & _
        "C3,2,0)-VLOOKUP(RC[-6],R" & RngRngRng.Row & "C1:R" & RngRngRng.Rows.Count & "C3,3,0)"

if I have misread it then the code will need editing for column variation too.

I'm going to look into this a bit more as I'm sure your method should work with correct syntax, hopefully this will get you working though.

Thanks Jasonb75

I have managed to get the code working (not in a very pretty way I have to say thought). Please find the code below that is working for me, it might help others with the same problem.

Sub Report_Z_1()
    Dim Cell As Range
    Dim Rng As Range
    Dim C As Range
    Dim R As Range
    Dim S As Range
    Dim CellCell As Range
    Dim RngRng As Range
    Dim CC As Range
    Dim RR As Range
    Dim SS As Range
    Dim CellCellCell As Range
    Dim RngRngRng As Range
    Dim CCC As Range
    Dim RRR As Range
    Dim SSS As Range
    Const Formula1 As String = "VLOOKUP(A"
    Const Formula2 As String = ",$A$"
    Const Formula3 As String = ":$D$"
    Const Formula4 As String = ",2,false))"
    Const Formula5 As String = "-IF(ISERROR(VLOOKUP(A"
    Const Formula6 As String = ",3,false))"
    Const Formula7 As String = "=(IF(ISERROR(VLOOKUP(A"
    Const Formula8 As String = ",2,false)),0,"
    Const Formula9 As String = ",3,false)),0,"
    Const Formula10 As String = ")/F"
    Dim FormulaAll1 As String
    Dim FormulaAll2 As String
    Dim ro As Range
 
 
    Application.EnableEvents = False
 
 If Not Worksheets("3b. Analyse migratieresultaten").Range("G61").Value = Worksheets("Settings").Range("A1").Value Then
 
    'Debet data
 
       With Worksheets("Z1")
 
        Set CellCell = .Columns(1).Find(What:="Financial Totals For Side 1", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set RngRng = CellCell.Offset(2)
        If IsEmpty(RngRng) Then
        MsgBox "The financials for Side 1 are empty"
        Else
            Set RngRng = .Range(CellCell, CellCell.End(xlDown))
            If RngRng.Count = 3 Then
            Set RngRng = CellCell.Offset(2)
            Else
            Set RngRng = .Range(CellCell.Offset(2), CellCell.Offset(2).End(xlDown))
        End If
        End If
 
        RngRng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
        RngRng.Offset(, 6).FormulaR1C1 = "=RC[-5]/RC[-1]"
        RngRng.Offset(, 7).FormulaR1C1 = "=RC[-5]/RC[-2]"
        CellCell.Offset(, 5).Value = "Financial Totals For Side 1 in Euros"
        CellCell.Offset(1, 5).Value = "Exchange Rate"
        CellCell.Offset(1, 6).Value = "Debit"
        CellCell.Offset(1, 7).Value = "Credit"
    End With
 
 
    With Worksheets("Z1")
        Set CC = .Columns(6).Find(What:="Financial Totals For Side 1 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set RR = .Range(CC.Offset(2), CC.End(xlDown))
        Set SS = CC.End(xlDown).Offset(1, 1)
        SS.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & RR.Rows.Count & "]C:R[-1]C)"
 
    End With
 
    ' Credit data
           With Worksheets("Z1")
 
        Set CellCellCell = .Columns(1).Find(What:="Financial Totals For Side 2", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set RngRngRng = CellCellCell.Offset(2)
        If IsEmpty(RngRngRng) Then
        MsgBox "The financials for Side 2 are empty"
        Else
            Set RngRngRng = .Range(CellCellCell, CellCellCell.End(xlDown))
            If RngRngRng.Count = 3 Then
            Set RngRngRng = CellCellCell.Offset(2)
            Else
            Set RngRngRng = .Range(CellCellCell.Offset(2), CellCellCell.Offset(2).End(xlDown))
        End If
        End If
 
        RngRngRng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
        RngRngRng.Offset(, 6).FormulaR1C1 = "=RC[-5]/RC[-1]"
        RngRngRng.Offset(, 7).FormulaR1C1 = "=RC[-5]/RC[-2]"
        CellCellCell.Offset(, 5).Value = "Financial Totals For Side 2 in Euros"
        CellCellCell.Offset(1, 5).Value = "Exchange Rate"
        CellCellCell.Offset(1, 6).Value = "Debit"
        CellCellCell.Offset(1, 7).Value = "Credit"
    End With
 
    With Worksheets("Z1")
        Set CCC = .Columns(6).Find(What:="Financial Totals For Side 2 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set RRR = .Range(CCC.Offset(2), CCC.End(xlDown))
        Set SSS = CCC.End(xlDown).Offset(1, 1)
        SSS.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & RRR.Rows.Count & "]C:R[-1]C)"
 
    End With
 
    'difference data
 
    With Worksheets("Z1")
 
        Set Cell = .Columns(1).Find(What:="Delta Between Side1 and Side2  ", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set Rng = Cell.Offset(2)
        If IsEmpty(Rng) Then
        MsgBox "The Reconciliation does not result in a financial difference between Side 1 and Side 2"
        Else
            Set Rng = .Range(Cell, Cell.End(xlDown))
            If Rng.Count = 3 Then
            Set Rng = Cell.Offset(2)
            Else
            Set Rng = .Range(Cell.Offset(2), Cell.Offset(2).End(xlDown))
        End If
        End If
 
 
        Rng.Offset(, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE)),1,VLOOKUP(RC[-5],'AAB FX EOM Rates'!C3:C8,6,FALSE))"
 
 
        Cell.Offset(, 5).Value = "Delta Between Side1 and Side2 in Euros"
        Cell.Offset(1, 5).Value = "Exchange Rate"
        Cell.Offset(1, 6).Value = "Debit"
        Cell.Offset(1, 7).Value = "Credit"
 
 
        FormulaAll1 = Formula7 & Cell.Offset(2).row & Formula2 & CellCell.row & Formula3 & CellCell.End(xlDown).row & Formula8 & Formula1 & Cell.Offset(2).row & Formula2 & CellCell.row & Formula3 & CellCell.End(xlDown).row & Formula4 & Formula5 & Cell.Offset(2).row & Formula2 & CellCellCell.row & Formula3 & CellCellCell.End(xlDown).row & Formula9 & Formula1 & Cell.Offset(2).row & Formula2 & CellCellCell.row & Formula3 & CellCellCell.End(xlDown).row & Formula6 & Formula10 & Cell.Offset(2).row
        Rng.Offset(, 6).Formula = FormulaAll1
        FormulaAll2 = Formula7 & Cell.Offset(2).row & Formula2 & CellCell.row & Formula3 & CellCell.End(xlDown).row & Formula9 & Formula1 & Cell.Offset(2).row & Formula2 & CellCell.row & Formula3 & CellCell.End(xlDown).row & Formula6 & Formula5 & Cell.Offset(2).row & Formula2 & CellCellCell.row & Formula3 & CellCellCell.End(xlDown).row & Formula8 & Formula1 & Cell.Offset(2).row & Formula2 & CellCellCell.row & Formula3 & CellCellCell.End(xlDown).row & Formula4 & Formula10 & Cell.Offset(2).row
        Rng.Offset(, 7).Formula = FormulaAll2
 
    End With
 
 
    With Worksheets("Z1")
        Set C = .Columns(6).Find(What:="Delta Between Side1 and Side2 in Euros", After:=.Cells(1, 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set R = .Range(C.Offset(2), C.End(xlDown))
        Set S = C.End(xlDown).Offset(1, 1)
        S.Resize(, 2).FormulaR1C1 = "=SUM(R[-" & R.Rows.Count & "]C:R[-1]C)"
 
    End With
    Worksheets("3b. Analyse migratieresultaten").Range("I61").Value = SS.Value
    Worksheets("3b. Analyse migratieresultaten").Range("J61").Value = SS.Offset(, 1).Value
    Worksheets("3b. Analyse migratieresultaten").Range("L61").Value = S.Value
    Worksheets("3b. Analyse migratieresultaten").Range("M61").Value = S.Offset(, 1).Value
    Else
    Worksheets("3b. Analyse migratieresultaten").Range("I61").Value = ""
    Worksheets("3b. Analyse migratieresultaten").Range("J61").Value = ""
    Worksheets("3b. Analyse migratieresultaten").Range("L61").Value = ""
    Worksheets("3b. Analyse migratieresultaten").Range("M61").Value = ""
End If
Application.EnableEvents = True
End Sub

ps. Note that the formula being constructed is a bit more complex that the originally posted vlookup. You can however edit it simple by taking off the if(iserror( part of the code.

Many thanks for everybody that put forward some suggestions