Range not selecting vba

Question

My code is giving an error" 1004: Select method of Range class failed.

Sub AdvencedFilterNew()
'
' AdvencedFilterNew Macro
'

    Dim WsOutput As Worksheet
    Dim WsMain As Worksheet
    Dim wsScenarios As Worksheet
    Dim ScenarioIDrow As Long
    Dim ScenarioIDColumn As Long
    Dim rgn As Range
    Dim p As String
    Dim q As String

    Range("E15").Select
    Range("E17:Q350").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("E14:Q15"), Unique:=False

    Set WsOutput = Worksheets("Output")
    Set WsMain = Worksheets("Main Menu")
    Set wsScenarios = Worksheets("Scenarios.New")

    ScenarioIDrow = WsOutput.Cells.Find("Scenario ID").Row
    ScenarioIDColumn = WsOutput.Cells(ScenarioIDrow, Columns.Count).End(xlToLeft).Column
    p = wsScenarios.Cells(ScenarioIDrow, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    q = wsScenarios.Cells(ScenarioIDrow, ScenarioIDColumn).Address(RowAbsolute:=False, ColumnAbsolute:=False)

    If WsMain.Range("E15") > 0 Then

    WsOutput.Range(p, q).Select
    WsMain.Cells.Find("E15").Select
    Else
    End If

End Sub

So, I am selecting a range based of the address of p an q. Which are A22 and ATX22. I dont know why I can select it. it works in some other codes I use. Another issue, if the number is 4 it finds also all other numbers that contains 4, for example 1014, 1024 and so on. Can someone help me here? Thanks!

The Range.Find method finds cells based on their contents not address. msdn.microsoft.com/en-us/library/office/ff839746.aspx So I believe you want WsMain.Range("E15") where you use WsMain.Cells.Find("E15") — JamesFaix

user3598756 user3598756 · Accepted Answer · 2016-12-04T16:40:49

it's because you have to Activate a worksheet before selecting any range of it

WsOutput.Range(p, q).Select
WsMain.Activate
WsMain.Cells.Find("E15").Select

Range not selecting vba

1 Answers