none
Simple Excel Macro Not Working RRS feed

  • Question

  • This is my first attempt at an Excel macro.   Here is what I have so far.

    Option Explicit
    
    Sub EmailList()
        Dim LastRow As Integer
        Dim Addy As String
        Dim I As Integer
        
        Windows("Membership.xlsm").Activate
        Sheets("Members").Activate
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "c").End(xlUp).Row
        End With
        
        For I = 4 To LastRow
            Addy = "r" & CStr(I) & "c7"
            Debug.Print (Range(Addy).Value)
        Next I
    End Sub

    If I print LastRow it gives the correct answer.  But the Debug statement gives the error message: Method"Range" of object '_Global' failed.

    What is the error?

    Wednesday, June 6, 2018 1:38 PM

Answers

  • Hello SezMe,

    "R4C7" is not a correct address format while using Range. You could try

    Debug.Print ActiveSheet.Range("G" & I).Value

    Or

    Debug.Print ActiveSheet.Cells(I, 7).Value

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SezMe Thursday, June 7, 2018 5:52 AM
    Thursday, June 7, 2018 1:59 AM