Multiple For Loops into One

[alexb523]

I have 3 'for loops' where i loop through each tab and do some basic clean up by referencing columns by their header. I think it would be more elegant combine these three loops in one loop, but cannot quite figure out what i am doing wrong to do so.

    'find "ClaimName" and replace values to match template format
    Dim sh2 As Worksheet, R As Range
    For Each sh2 In b1.Sheets
        Set R = sh2.Cells.Find("ClaimName", , xlValues, xlWhole)
        If R Is Nothing Then
            GoTo Nx
        Else
            sh2.Range(R, sh2.Cells(sh2.Rows.Count, R.Column).End(xlUp)).Replace "Score", "", xlPart
            sh2.Range(R, sh2.Cells(sh2.Rows.Count, R.Column).End(xlUp)).Replace "Claim", "Claim ", xlPart
        End If
Nx:
    Next sh2
   
    'find "SBName" and replace values to match template format
    Dim sh3 As Worksheet, S As Range
    For Each sh3 In b1.Sheets
        Set S = sh3.Cells.Find("SBName", , xlValues, xlWhole)
        If S Is Nothing Then
            GoTo Nx2
        Else
            sh3.Range(S, sh3.Cells(sh3.Rows.Count, S.Column).End(xlUp)).Replace "ELA", "ELA/Literacy", xlPart
            sh3.Range(S, sh3.Cells(sh3.Rows.Count, S.Column).End(xlUp)).Replace "Math", "Mathematics", xlPart
        End If
Nx2:
    Next sh3
   
    'find "VarName" and replace values to match template format
    Dim sh4 As Worksheet, V As Range
    For Each sh4 In b1.Sheets
        Set V = sh4.Cells.Find("VarName", , xlValues, xlWhole)
        If V Is Nothing Then
            GoTo Nx3
        Else
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "Overall", "Total", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "AmericanIndianOrAlaskaNative", "American Indian or Alaska Native", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "BlackOrAfricanAmerican", "Black/African American", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "NativeHawaiianOrOtherPacificIslander", "Native Hawaiian or Pacific Islander", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "HispanicOrLatino", "Hispanic/Latino Ethnicity", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "DemographicRaceTwoOrMoreRaces", "Two or More Races", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "UnknownRace", "Unidentified Race", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "LEPStatus", "LEP Status", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "IDEAIndicator", "IDEA Indicator", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "Section504Status", "Section 504 Status", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "EconomicDisadvantageStatus", "Economic Disadvantage Status", xlPart
            sh4.Range(V, sh4.Cells(sh4.Rows.Count, V.Column).End(xlUp)).Replace "2_2", "", xlPart
        End If
Nx3:
    Next sh4

thanks! alex

 

[rlv01]

Hard to know w/o testing, but here's my version

    Dim sh2 As Worksheet
    Dim R As Range, S As Range, V As Range
    
    For Each sh2 In b1.Sheets
        'find "ClaimName" and replace values to match template format
        Set R = sh2.Cells.Find("ClaimName", , xlValues, xlWhole)
        If Not R Is Nothing Then
            With sh2.Range(R, sh2.Cells(sh2.Rows.Count, R.Column).End(xlUp))
                .Replace "Score", "", xlPart
                .Replace "Claim", "Claim ", xlPart
            End With
        End If

        'find "SBName" and replace values to match template format
        Set S = sh2.Cells.Find("SBName", , xlValues, xlWhole)
        If Not S Is Nothing Then
            With sh2.Range(S, sh2.Cells(sh2.Rows.Count, S.Column).End(xlUp))
                .Replace "ELA", "ELA/Literacy", xlPart
                .Replace "Math", "Mathematics", xlPart
            End With
        End If

        'find "VarName" and replace values to match template format
        Set V = sh2.Cells.Find("VarName", , xlValues, xlWhole)
        If Not V Is Nothing Then
            With sh2.Range(V, sh2.Cells(sh2.Rows.Count, V.Column).End(xlUp))
                .Replace "Overall", "Total", xlPart
                .Replace "AmericanIndianOrAlaskaNative", "American Indian or Alaska Native", xlPart
                .Replace "BlackOrAfricanAmerican", "Black/African American", xlPart
                .Replace "NativeHawaiianOrOtherPacificIslander", "Native Hawaiian or Pacific Islander", xlPart
                .Replace "HispanicOrLatino", "Hispanic/Latino Ethnicity", xlPart
                .Replace "DemographicRaceTwoOrMoreRaces", "Two or More Races", xlPart
                .Replace "UnknownRace", "Unidentified Race", xlPart
                .Replace "LEPStatus", "LEP Status", xlPart
                .Replace "IDEAIndicator", "IDEA Indicator", xlPart
                .Replace "Section504Status", "Section 504 Status", xlPart
                .Replace "EconomicDisadvantageStatus", "Economic Disadvantage Status", xlPart
                .Replace "2_2", "", xlPart
            End With
        End If
    Next sh2

 

[alexb523]

Thanks! this works and has cleaned up my code.

 

[Fluff]

If interested the last part of the code could be done like

Dim repl As String
Dim Ary As Variant
Dim i As Long


repl = "Overall|Total|AmericanIndianOrAlaskaNative|American Indian or Alaska Native|BlackOrAfricanAmerican|Black/African American|NativeHawaiianOrOtherPacificIslander|Native Hawaiian or Pacific Islander|HispanicOrLatino|Hispanic/Latino Ethnicity|DemographicRaceTwoOrMoreRaces|Two or More Races|UnknownRace|Unidentified Race|LEPStatus|LEP Status|IDEAIndicator|IDEA Indicator|Section504Status|Section 504 Status|EconomicDisadvantageStatus|Economic Disadvantage Status|2_2|"

Ary = Split(repl, "|")
For i = LBound(Ary) To UBound(Ary) Step 2
   With sh2.Range(v, sh2.Cells(sh2.Rows.count, v.Column).End(xlUp))
      .Replace Ary(i), Ary(i + 1), xlPart
   End With
Next i