PDA

View Full Version : vba - help removing selection



malleshg24
11-05-2017, 07:55 PM
Hi Team,:help
The below codes formula work for me, however in each sheet column B is getting selected
I want range ("a1") to get selected by default.

I tried application.cutcopymode = false but no luck. plz assist. Thanks.


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

Simon Lloyd
11-05-2017, 09:16 PM
The code doesn't "Select" anything! it simply puts a formula in column B for the range you specify, if you put that formula in column a you will have a circular reference.

YasserKhalil
11-05-2017, 09:17 PM
Hello
Please put the codes between tags to appear properly
As for your code, it is working well and there is no relationship between selecting B2 and the code
But if you need to do that and select A1 you can use the code like that


Sub Test()
Dim ws As Worksheet
Dim lr As Long


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