excel macro vba - what is the code for populating the data?

0
votes

Im new to vba excel macro. usually in excel i will double click on the cell and then the data will auto generate till the last row. but in vba how to make it ? Below is my code. currently im selecting BF2 to vlookup. after i get the value of vlookup. i want to populate the data till the last row. usually in excel way, i will just double click on the cell and then it will populate. so how do we do in vba macro ? advise pls. 

Set myValues = Application.InputBox("Please select on the CON2:", 
Default:=Range("BE2").Address(0, 0), Type:=8)
Set myResults = Application.InputBox("Please select previous CON2 to VLOOKUP:", Type:=8)

On Error Resume Next

Set myValues = myValues.Offset
FirstRow = myValues.Row
FinalRow = Cells(65536, myResults.Column).End(xlUp).Row

Range("BF2").Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
"  " & myResults.Address(External:=True) & " , 1, 0)"
2
vbaexcel
can you give an example of what you mean? your question is not really clear. – Goos van den Bekerom
i cant paste a picture here because im a new user. – Yang Chun
no problem we have put answers below ;) – Goos van den Bekerom
the answer seems cant match with my code. can you advise me ? :) Thanks – Yang Chun
that is because we answered before you put your code in the question. but sure i'll have a look – Goos van den Bekerom

2 Answers

1
votes

In VBA you would do something like this:

Sub AutoFill()
    Dim sRng As Range, fRng As Range

    Set sRng = Worksheets("Sheet1").Range("B2")      '<~~ Range to AutoFill from
    Set fRng = Worksheets("Sheet1").Range("B2:B10")  '<~~ Range to AutoFill

    sRng.AutoFill Destination:=fRng
End Sub

This code will AutoFill the range B2:B10 based on the content of cell B2.

0
votes

I think this is what you want.

Sub AutoFill()
    Dim sRng As Range, fRng As Range

    FirstRow = myValues.Row
    FinalRow = Cells(65536, myResults.Column).End(xlUp).Row

    Range("BF2").Formula = _
    "=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
    "  " & myResults.Address(External:=True) & " , 1, 0)"

    Set sRng = Worksheets("Sheet1").Range("BF2")
    Set fRng = Worksheets("Sheet1").Range("BF2:B" & FinalRow) 

    sRng.AutoFill Destination:=fRng
End Sub