Solved: Filling a formula

[Klartigue]

Sub Vlookup()
'
' Vlookup Broker
Windows("Volume worksheet.xlsx").Activate
Application.Run "BLPLinkReset"
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Broker Codes.xlsx]Sheet1'!R2C1:R68C2,2,FALSE)"
Range("F3").Select
Application.Run "BLPLinkReset"

End Sub

I would like to have this macro do a vlookup function for every cell in column F present in my spreadsheet, not just cell F2.

How do I do this?

Thanks for the help!

[Bob Phillips]

Code tags are VBA, not VBAX.



Sub Vlookup()
'
' Vlookup Broker
Windows("Volume worksheet.xlsx").Activate
Application.Run "BLPLinkReset"

Range(Range("F2"), Range("F2").End(xlDown)).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Broker Codes.xlsx]Sheet1'!R2C1:R68C2,2,FALSE)"

Range("F3").Select
Application.Run "BLPLinkReset"

End Sub

[Klartigue]

Thanks, i was just looking for how to do correct codes.

On the above, would you mind helping me figure out how to do a vlookup function for every cell in column F present in my spreadsheet, not just cell F2.

Thanks, the help is greatly appreciated!

[Klartigue]

Sorry i was unclear, sometimes column F may be from F2:F60, it could be from F2:F100.

Would a macro have something with:

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1

[Bob Phillips]

You were clear. Did you try it?

[Klartigue]

it works but it fills in the entire F column so there are many N/A because I dont need it to go down that far, only need it to fill a certain amount of rows.

[Bob Phillips]

Try this then



Range("F2").Resize(Range("A2").End(xlDown).Row - 1).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Broker Codes.xlsx]Sheet1'!R2C1:R68C2,2,FALSE)"

[Klartigue]

great it works! thank you!

[Klartigue]

See attached..

Can you help write a macro that inserts three blank cells between each different broker. So in this case, between F2 and F3, F3 and F4, F4 and F5, F6 and F7, etcc...

And the cells may differ so I would need to write a general formula.

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow - 1 To 2 Step -1

If .Cells(i, "F").Value <> .Cells(i + 1, "F").Value Then

.Rows(i + 1).Insert
End If
Next i
End With
End Sub