lundi 20 avril 2015

VBA increasing dynamic range in VLOOKUP

I'm having trouble with setting properly dynamic range for VLOOKUP which should be increasing by 58 with each loop (I have 96 different ranges). VLOOKUP table is in other ("Gefco") sheet. The error I'm getting is:

Application-defined or object-defined error

Code:

Sub vlookup_rates()

Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim rr, dupa As Range
Dim ws, wws As Worksheet
Dim wb As Workbook


c = 1
a = 2
b = 59
d = 2
e = 59

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Gefco")
With ws
Set rr = Range(Cells(d, 4), Cells(e, 8))
End With

Set wws = wb.Sheets("Waberers")
wws.Activate

    Do While c < 97
        Cells(4, a).Select
        Cells(4, a).Formula = "=VLOOKUP($A$4;" & rr.Address & ";5;0)"  <-ERROR

        c = c + 1
        a = a + 1
        b = b + 1
        d = d + 58
        e = e + 58

    Loop
End Sub

I think range is somehow wrongly defined but I can't crack it. Please halp.

Thanks!

Aucun commentaire:

Enregistrer un commentaire