VBA Vlookup

[uti1301]

I have 5 columns B F G I J. A simplified version of what I want to do is illustrated by the following data:
B F G I J

[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt" span="9"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]674[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]106.00%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]701.5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent, align: right"]79.50%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]736.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent, align: right"]102.70%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]778.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent, align: right"]111.00%
[/TD]
[/TR]
</tbody>[/TABLE]
I want to use VLOOKUP to enter formulae into the G column using VLOOKUP, and multiply the result by the values in column Fbut can not get it to work in VBA.
The code I have tried is:
Sub lookuptest1()
Dim i As Long
Dim lookup_range As Range
Set lookup_range = Range("I3:J6")
Range("G3").Select
For i = 0 To 3
ActiveCell.Offset(i, 0).FormulaR1C1 = "=RC[-1]*vlookup(RC[-5],lookup_range,2)"
Next

Could somebody help me please?

 

[BrianJN1]

When I enter the formula "=F3*VLOOKUP(B3,I$3:J$6,2,FALSE)" manually into G3 and copy down I get these values:
7144, 55729.5, 75072 and 86358. I didn't use your data exactly, but is that much as you would expect? Looking at your formula which pretty much emulates what I have manually created they are the same except that you have not declared your range to be absolute, ie no "$" symbols.
I suggest that is your likely issue.

 

[BrianJN1]

I've just gone back to your macro and I find that is not necessarily the issue.

 

[BrianJN1]

I have a macro identical to the red other than the range:

sub LookMe()
Dim lookup_range As Range
Set lookup_range = Range("I$3:J$6")
[I][COLOR=#ff0000]PL = 6

Range("D3").Select
    ActiveCell.Formula = [U][B]"=VLOOKUP(B3, I$3:J$6,2,FALSE)"[/B][/U]
    
    Range("D3").Select
    Selection.AutoFill Destination:=Range("D3:D" & PL), Type:=xlFillDefault
    Range("D3:D" & PL).Select[/COLOR][/I]
End sub

This macro works in that it copies J values into D. By replacing "I$3:J$6" with "lookup_range" #NAME errors result.
Even with that formula above I regret that I cannot apply a multiplication within it.

I regret I can't currently offer a solution.

 

[uti1301]

Thanks BrianJN1.
This will work when I can find out how to multiply the results by the values in the preceeding column.

 

[BrianJN1]

Hi again.

I had a bit of a play around and came up with a macro which I believe exactly fits the sheet that you submitted:

Sub LookMe()
Dim PL As Long
With ActiveSheet
PL = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

Range("G3").Select
    ActiveCell.Formula = "=F3" & " * " & "VLOOKUP(B3, I$3:J$" & PL & ",2,FALSE)"
    
    Range("G3").Select
    Selection.AutoFill Destination:=Range("G3:G" & PL), Type:=xlFillDefault
    Range("G3:G" & PL).Select
End Sub

This will automatically adjust to last value in column F as well as giving your calculation in Column G.
The big issue is where to place those quotes! They can be a worry.

 

[uti1301]

Thank you very much, it works perfectly.
I am very grateful

 

[BrianJN1]

Glad to have been of service.

 

[Peter_SSs]

If interested, here's another way that I think does the same job.

Sub InsertFormulas()
  Dim PL As Long
  
  PL = Cells(Rows.Count, "F").End(xlUp).Row
  Range("G3:G" & PL).Formula = "=F3*VLOOKUP(B3,I$3:J$" & PL & ",2,0)"
End Sub

 

[BrianJN1]

@Peter.
That's neat, much tidier than mine.
I'm going to strip that formula back to the VLOOKUP element use your statements in the application from which I derived my version.
Thanks for the thought.