CODEWITHSUNDEEP
excelvba
Stephanie
Stephanie

Reputation: 23

Replace contents across non-contiguous cells across multiple worksheets - Error 1004

I am an Excel VBA novice and am hoping for help on a bit of code. We have a workbook with four identical worksheets, one for each quarter of the year. What I would like to do is run code on a command button click that replaces identifying name information with generic, sequentially numbered information (ex: Joe Smith becomes Male1). There are multiple participant types, represented across the worksheet columns and also in multiple rows within the spreadsheet. So, data for males are in cells C11, C28, C45..C215, J11, J28, J45, X11, X28, X45...AE215 across 4 tabs.

I have a Worksheets Array that identifies the four quarterly tabs, and code for each participant type that defines the range of cells where their names would be listed.

The below code is giving me Error 1004 Method 'Range' of object '_Worksheet' failed at the For Each MaleCare line... Any insight into what I am doing wrong? 

Sub DelConfSAVE()
    Dim sh As Worksheet

    For Each sh In Worksheets(Array("Oct-Dec Attendance", "Jan-Mar Attendance", _
    "Apr-Jun Attendance", "Jul-Sep Attendance"))

        'Replace Male Caregiver (X)
        Dim MaleCare As Range
        Dim X As Integer

        X = 0

        For Each MaleCare In sh.Range("C11, C28, C45, C62, C79, C96, C113, C130, C147, C164, C181, C198, C215, J11, J28, J45, J62, J79, J96, J113, J130,J147, J164, J181, J198, J215, Q11, Q28, Q45, Q62, Q79, Q96, Q113, Q130,  Q147, Q164, Q181, Q198, Q215, X11, X28, X45, X62, X79, X96, X113, X130, X147, X164, X181, X198, X215, AE11, AE28, AE45, AE62, AE79, AE96, AE113, AE130, AE147, AE164, AE181, AE198, AE215")
            If MaleCare.Value <> "" Then
               X = X + 1
               MaleCare.Value = "MaleCare" & X
            End If
        Next MaleCare

        Exit For

        'Replace Female Caregiver (Y)
        Dim FemCare As Range
        Dim Y As Integer

        Y = 0

        For Each FemCare In sh.Range("D11, D28, D45, D62, D79, D96, D113, D130, D147, D164, D181, D198, D215, K11, K28, K45, K62, K79, K96, K113, K130, K147, K164, K181, K198, K215, R11, R28, R45, R62, R79, R96, R113, R130, R147, R164, R181, R198, R215, Y11, Y28, Y45, Y62, Y79, Y96, Y113, Y130, Y147, Y164, Y181, Y198, Y215,AF11, AF28, AF45, AF62, AF79, AF96, AF113, AF130, AF147, AF164, AF181, AF198, AF215")
            If FemCare.Value <> "" Then
                Y = Y + 1
                FemCare.Value = "FemCare" & Y
            End If
        Next FemCare

        Exit For

        'And additional code for Youth1 Youth2 Youth3 OtherAdult cut for posting brevity

    Next sh
End Sub

Upvotes: 2

Views: 420

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Stephanie, Is this what you are trying?

Sub DelConfSAVE()
    Dim sh As Worksheet
    Dim X As Long, Y As Long, i As Long

    For Each sh In Worksheets(Array("Oct-Dec Attendance", "Jan-Mar Attendance", _
    "Apr-Jun Attendance", "Jul-Sep Attendance"))

        X = 0

        For i = 11 To 215 Step 17
            With sh
                '~~> Male
                If Not Len(Trim(.Range("C" & i).Value)) = 0 _
                Then .Range("C" & i).Value = "MaleCare" & X

                If Not Len(Trim(.Range("J" & i).Value)) = 0 _
                Then .Range("J" & i).Value = "MaleCare" & X

                If Not Len(Trim(.Range("Q" & i).Value)) = 0 _
                Then .Range("Q" & i).Value = "MaleCare" & X

                If Not Len(Trim(.Range("X" & i).Value)) = 0 _
                Then .Range("X" & i).Value = "MaleCare" & X

                If Not Len(Trim(.Range("AE" & i).Value)) = 0 _
                Then .Range("AE" & i).Value = "MaleCare" & X

                '~~> Female
                If Not Len(Trim(.Range("D" & i).Value)) = 0 _
                Then .Range("D" & i).Value = "FemCare" & Y

                If Not Len(Trim(.Range("K" & i).Value)) = 0 _
                Then .Range("K" & i).Value = "FemCare" & Y

                If Not Len(Trim(.Range("R" & i).Value)) = 0 _
                Then .Range("R" & i).Value = "FemCare" & Y

                If Not Len(Trim(.Range("Y" & i).Value)) = 0 _
                Then .Range("Y" & i).Value = "FemCare" & Y

                If Not Len(Trim(.Range("AF" & i).Value)) = 0 _
                Then .Range("AF" & i).Value = "FemCare" & Y

                X = X + 1: Y = Y + 1
            End With
        Next i

        'And additional code for Youth1 Youth2 Youth3 OtherAdult cut for posting brevity
    Next sh
End Sub

Upvotes: 1

Related Questions