How to enter vlookup in all sheets whose name ends with _AS in VBA

[A9kurs]

HI

Guys my file has few sheets whose name ends with _AS. i want to insert a vlookup formula using vba on all the sheet where sheet name is Like *_AS.

Thanks

[BadlySpelledBuoy]

What is the VLOOKUP formula you want to enter and which cell is it to go in on each sheet?

BSB

[A9kurs]

cell should be A2
VLOOKUP(X2,List!$B$2:$C$18,2,0)

[BadlySpelledBuoy]

Maybe this:

Please Login or Register  to view this content.

BSB

[BadlySpelledBuoy]

Just in case any sheet names have '_AS' anywhere else in the name than the last 3 characters you should use this line instead of the one in the previous post.

Please Login or Register  to view this content.

BSB

[A9kurs]

Its not going to sheets

below one is going to sheets but it not ending and dont know how to add formulae

Sub test()
Dim ws As Worksheet, flg As Boolean
Dim Formulae As String
For Each ws In Sheets
If ws.name Like "*WT" Then
ws.Select Not flg
ws.Cells(10, 41) = "Y"
End If

Next
End Sub

[BadlySpelledBuoy]

Did you run the sub?

Could you attach a desensitized version of the workbook?

BSB

[A9kurs]

Code below is working but it not ending

Sub BSB()
Dim ws As Worksheet

For Each ws In Sheets
With ws
'If InStr(1, .name, "_WT") Then .range("AO10").FormulaR1C1 = "=VLOOKUP(RC[23],List!R2C2:R18C3,2,0)"
If Right(.name, 3) = "_WT" Then .range("Ao10").FormulaR1C1 = "Y"
End With
Next ws
End Sub

[BadlySpelledBuoy]

What do you mean by "not ending"? It will loop through every sheet in the workbook and stop when it gets to the last one.

BSB

[A9kurs]

It’s goes to end and then going back to first sheet

[BadlySpelledBuoy]

If you open the VB Editor, put the cursor somewhere in that macro and just press F5 once then let it go, does it run to the end and stop or start again?

If the latter then attach the workbook and we can investigate better.

BSB

[A9kurs]

Hi

Can you help me how to drag the formulae to end and paste the value instead of formulae


Sub Test()
Dim ws As Worksheet
For Each ws In Sheets
With ws
If Right(.Name, 3) = "_WT" Then .Range("AO10").Formula = "=VLOOKUP((A10&G10),'Master BOM1'!$A:$V,14,0)"

End With
Next
End Sub

[BadlySpelledBuoy]

To comply with forum rules you need to use code tags around your VBA. So you need to go back and edit your forum posts to do that before I will reply further.

Whilst you're editing things, explain more what you mean by "drag the formula to the end"? End of what? End of time? End of my wits...?

Example workbook will explain things far better.

BSB

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)