CODEWITHSUNDEEP
vbaexcel
Yang Chun
Yang Chun

Reputation: 25

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

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)"

Upvotes: 0

Views: 376

Answers (2)

Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

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

Upvotes: 0

Netloh
Netloh

Reputation: 4378

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.

Upvotes: 1

Related Questions