1004 - method of range class error

[rickblunt]

Hello, I have another head scratcher for you (well for me anyway - lol) I am using the following code to transfer some data and it works perfectly...

Sub EmergencyShowerReport()

    Dim rng As Range
    
    With Sheets("Device List")
        Set rng = .Range("A1:H" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With rng
        .AutoFilter Field:=1, Criteria1:="Emergency Shower"
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Emergency Shower Report").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Emergency Shower Report").Cells.EntireColumn.AutoFit
    End With
    
    Call ShowAll
           
       Sheets("Emergency Shower Report").Range("M1").Select
        
End Sub

But when I use this code to call it I get an 1004 error at the "Sheets("Emergency Shower Report").Range("M1").Select" syntax

Private Sub EsReportButton_Click()

    Dim Answer As Integer

    Answer = MsgBox("Would you like to create the Emergency Shower Report now?", vbYesNo + vbQuestion, "Report Manager")
               
        If Answer = vbYes Then Call EmergencyShowerReport
    
End Sub

I do not understand why calling the sub is creating an error I really appreciate any insights on this - thanks

 

[johnnyL]

Hello, I have another head scratcher for you (well for me anyway - lol) I am using the following code to transfer some data and it works perfectly...
...
But when I use this code to call it I get an 1004 error at the "Sheets("Emergency Shower Report").Range("M1").Select" syntax

Private Sub EsReportButton_Click()

    Dim Answer As Integer

    Answer = MsgBox("Would you like to create the Emergency Shower Report now?", vbYesNo + vbQuestion, "Report Manager")
              
        If Answer = vbYes Then Call EmergencyShowerReport
   
End Sub

I do not understand why calling the sub is creating an error I really appreciate any insights on this - thanks

The problem is with that last bit of code.

'Answer' is going to return an integer result. 7 will be returned if 'No' is selected and 6 will be returned if 'Yes' is selected. So when you check for 'vbYes', that is not going to happen.

    Dim Answer As Integer
'
    Answer = MsgBox("Would you like to create the Emergency Shower Report now?", vbYesNo + vbQuestion, "Report Manager")
'
    If Answer = 7 Then
        MsgBox "No was selected"
    ElseIf Answer = 6 Then
        MsgBox "Yes was selected"
''        Call EmergencyShowerReport
    End If

So you may want to change:

        If Answer = vbYes Then Call EmergencyShowerReport

to:

        If Answer = 6 Then Call EmergencyShowerReport

 

[rickblunt]

oh boy - that went over my head, I think I do understand what you are saying about having to answer both sides of the equation, but I do not understand the 6 and the 7. Is that one of those coding syntaxes that I see a lot? I am sorry for my ignorance, I have learned so much from all of you but I still have so much more to learn....

 

[JoeMo]

This line:
Sheets("Emergency Shower Report").Range("M1").Select
cannot be executed unless the sheet with tab name "Emergency Shower Report" is the activesheet when the line is executed. Does the sub ShowAll leave that sheet active when it completes?

 

[rickblunt]

Bingo! that's it - (sound of my forehead hitting my desk) the ShowAll is still focused on that sheet. I should have caught that - thank you once again

 

[JoeMo]

Bingo! that's it - (sound of my forehead hitting my desk) the ShowAll is still focused on that sheet. I should have caught that - thank you once again

You are welcome - thanks for the reply.

 

[johnnyL]

oh boy - that went over my head, I think I do understand what you are saying about having to answer both sides of the equation, but I do not understand the 6 and the 7. Is that one of those coding syntaxes that I see a lot? I am sorry for my ignorance, I have learned so much from all of you but I still have so much more to learn....

Sorry for any confusion I have caused, it appears that the integer and text values are interchangeable.