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

 

[gsbelbin]

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

I'm not sure what you want but I'll have a guess at what the problem is

Is the problem that you want to use VLOOKUPs to read data in tables whose size can vary?

 

[RobbertH]

Basically yes.

Also, this vlookup formula is written by the vba code.

I can determine the arrays and the lookup value but i dont know how to put this in the formula that is written by the vba code. With the above code the formula is written at the right place but looks like:
=VLOOKUP(Rng; RngRng.Resize(; 3); 2; FALSE) - VLOOKUP(Rng; RngRngRng.Resize(; 3); 3; FALSE)
This doesn't work in a workbook.

Note: the lookup value can also be written as RC[-6]. But than still the array gives a problem.

 

[gsbelbin]

Basically yes.

Also, this vlookup formula is written by the vba code.

I can determine the arrays and the lookup value but i dont know how to put this in the formula that is written by the vba code. With the above code the formula is written at the right place but looks like:
=VLOOKUP(Rng; RngRng.Resize(; 3); 2; FALSE) - VLOOKUP(Rng; RngRngRng.Resize(; 3); 3; FALSE)
This doesn't work in a workbook.

Note: the lookup value can also be written as RC[-6]. But than still the array gives a problem.

If that's the case then you could use dynamic ranges to define the tables that you are going to look up, as long as they start from the same top left cell. The number of rows used in the VLOOKUP would then alter as the size of the table alters. Then you could just type the VLOOKUP into the cell and avoid having to run VBA code. Does this sound promising?

 

[jasonb75]

Without the rest of your code I'm guessing that the rng parameters are only declared in VBA, not named ranges in the sheet, meaning they need to be concatenated into the formula, something like

rng.Offset(, 6).FormulaR1C1 = "=VLookup(" & rng & "," & rngrng.Resize(, 3) & ",2,False)-VLookup(" _
& rng & "," & rngrngrng.Resize(, 3) & ",3,False)"

Note that this is not meant to be a solution, it's a point in the right direction given the limited information.

 

[RobbertH]

Jasonb75
Thats right, the parameters are only declared in VBA. However your suggestion doesnt seem to work. I tried the same thing but without result. Any idea why?

 

[jasonb75]

Possible I've missed some keywords in that reply, maybe a wrong declaration type giving invalid ranges, without the rest of the code it would be pure guesswork all the way.

 

[gsbelbin]

I don't want to get protective about my suggestion but I think that by using code you're over complicating the problem. You'll have to run the code each time you change the size of the table. If you use dynamic ranges the formula in the cell will not need to change and the results will automatically update any time the table size changes. If this is what you want then let me know and I'll tell you what to do.

 

[RobbertH]

I don't want to get protective about my suggestion but I think that by using code you're over complicating the problem. You'll have to run the code each time you change the size of the table. If you use dynamic ranges the formula in the cell will not need to change and the results will automatically update any time the table size changes. If this is what you want then let me know and I'll tell you what to do.

The code might indeed be somewhat complicated, however manual ajustments to sheets cannot be done. The reason being that i receive templates that contain the tables but not the calculations performed on them. The tables contain data in various currencies. Table 3 is the difference between table 1 and table 2. However, in some cases this table 3 doesnt compare the right numbers becuase debet and credit are switched. What i need to do here is subtracting credit table 2 from debet table 1. Because it is possible that different templates contain a different set of currencies and that table 1 and table 2 contain different currencies the vba code needs to determine the position of the tables. The vlookup is used to make sure all currencies are picked from either table 1 or 2. (note: table 3 always contains all possible currencies either from table 1 or 2.

Below the code i'm using. The part with the vlookup in differences (the last "with") is where my code stops. I cant figure out why.

Note: This vlookup is not how it should be set in the end but i drilled it down slightly to understand where the error comes in.

Hope you guys have any idea to work this out.

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
    
    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))"
        Rng.Offset(, 6).FormulaR1C1 = "=VLOOKUP(RC[-6]," & RngRng.Resize(, 3) & ",2,0)-VLOOKUP(RC[-6]," & RngRngRng.Resize(, 3) & ",3,0)"
        Rng.Offset(, 7).FormulaR1C1 = "=VLOOKUP(RC[-7]," & RngRng.Resize(, 3) & ",3,FALSE)-VLOOKUP(RC[-7]," & RngRngRng.Resize(, 3) & ",2,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"
    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

 

[jasonb75]

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.