vba help on lr

[Mallesh23]

Hi Team,

I have 5 sheets, and in column B of every worksheet I want to apply vlookup formula, after applying a formula, I want to autofill that formula till last row.

Shall I need to create different LR?... Cells.(rows.count,1).end(xlup).row

for different sheets. Please suggest.

Thanks in advance !!!
Regards,
Mallesh Gangadhar

 

[igold]

Hi,

A couple of questions...

What is going to trigger the macro?
Is the formula always going to be the same?
Is it the same formula for every sheet?
Are there any other sheets in the workbook other than the "5 sheets"?
Is the formula going to start in the same row in Column B on every sheet?

 

[Jonmo1]

With no specifics to go on, here's an example assuming:
It's doing every sheet in the book
Formula should start in B2 on each sheet
Formula is referring to A2 on each sheet
Lookup range is E2:F100
Formula goes down as far as data exists in column A.

Sub Test()
Dim ws As Worksheet, lr As Long
For Each ws In Worksheets
    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,$E$2:$F$100,2,FALSE)"
Next ws
End Sub

 

[Mallesh23]

Hello sir,

Thanks for your help , looking for One more help plz.

I want this formula only in sheet1,sheet2, and sheet4. I don't want to run looping in sheet3 and sheet 5.
My formula start at B2 in each sheets. look up value and look up data is same for all sheets. I have given Name to my range as (data)

also Can you suggest if my data is in different workbook , how to select that files data and use in current workbook.

I am new to vba, just started leaning the concept. Please assist. Thank you so much .


Regards,
Mallesh

 

[Jonmo1]

This will restrict it to a list of specified sheet names.
And will use data as the named range for the lookup range

Sub Test()
Dim ws As Worksheet, lr As Long
For Each ws In Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet2", "Sheet5"
            lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
            ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,data,2,FALSE)"
    End Select
Next ws
End Sub