Vlookup Function using Macro-Please Help!!

[blf16890]

Hi all,

I'm trying to perform an vlookup funciont via macro but I'm not very good at vba

I have a workbook with 2 sheets, I need that my macro calculates an vlookup of each cell of column E (beginning with e2), until it finds a blank cell in column E
Then I need that the calculated values by the vlookup get pasted in column K, beginning with K2 (each of them corresponding with e2-k2, e3-k3, etc)
Then I need the macro to multiply the values of col g * values of col K, beginnign both in row 2, and then paste them in col L, beginning with l2.

I tried this just for the lookup, but I get failures and the macro doesnt even run

Sub Vertical_lookup_test()
Dim Result As Variant
Dim myVal As String ' Can be Integer, long, double etc.
Dim Rng As Range
Dim Clm As Integer
Rng = Sheets("cambioTC").Range("a24:a100000") ' Set Range
myVal = Sheets("DeltaFX").Range("e2:e100000") ' Value to be searched
Clm = 4 ' Column to be fetched
Result = Application.VLookup(myVal, Rng, Clm, False)
If IsError(Result) Then
Result = ""
End If
'MsgBox Result
End Sub

Could you please help me??

Thank you very much

 

[ranman256]

wouldnt it be easier to put a combo box on a sheet,
and let it do the vlookup. No code.
Its all provided in the combo box, rather than do all this code.

 

[blf16890]

wouldnt it be easier to put a combo box on a sheet,
and let it do the vlookup. No code.
Its all provided in the combo box, rather than do all this code.

I dont know how to do that...
I've been requested to do it via code, so the macro calculates everything and then paste the values in the requested worksheet and col.

I tired this but it doesn't work either

Sub SearchVlookup()


Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
If Cells(i, 1).Value <> "" Then
Cells(i, 7).Select
Range("G" & i).Value = Application.VLookup(Cells(i, 3), Sheet2!Range("A:D"), 4, False)
Else
Exit Sub
End If
Next
End Sub

 

[blf16890]

Anybody?

Please it's a job issue and I'm starting to get a little desperate

Thanks very much

 

[V_Malkoti]

Why use VBA for this when it can be done with Excel formulas.

enter in K2 of deltaFX
=VLOOKUP(E2, cambioTC!A24:D100000, 4, false)


enter in KL2 of deltaFX
=K2*G2

then fill these formulas until last row in those columns by dragging fill handle.

 

[blf16890]

Why use VBA for this when it can be done with Excel formulas.

enter in K2 of deltaFX
=VLOOKUP(E2, cambioTC!A24:D100000, 4, false)


enter in KL2 of deltaFX
=K2*G2

then fill these formulas until last row in those columns by dragging fill handle.

I have already written the formulas in the cells, but my boss doesn't want formulas, just the macro calculating the values and then pasting them in its correspondent place

It's faster and the file won't be too heavy

 

[blf16890]

Anyone?

 

[V_Malkoti]

I usually find formulas to be faster than vba. But here you go:

Sub LookupAndUpdate()
    Dim oCell As Range
    Dim oFX As Worksheet, oTC As Worksheet
    Dim strFindText As String
    Dim i As Long


    i = 2
    Set oFX = ThisWorkbook.Sheets("deltaFX")
    Set oTC = ThisWorkbook.Sheets("cambioTC")
    
    While oFX.Cells(i, 1).Value <> ""
        strFindText = oFX.Cells(i, 1).Value
        Set oCell = oTC.Range("A:A").Find(what:=strFindText, LookIn:=xlValues, lookat:=xlWhole)
        If Not oCell Is Nothing Then
            oFX.Range("K" & i).Value = oTC.Range("D" & oCell.Row).Value
            oFX.Range("L" & i).Value = oFX.Range("G" & i).Value * oFX.Range("K" & i).Value
        End If
        i = i + 1
    Wend
End Sub