CODEWITHSUNDEEP
arraysvbaloopsrangedeclare
Ryan S
Ryan S

Reputation: 55

VBA Array of Sheets with a range, loop through cells and change value of separate column

The goal is to look through Column A in all Sheets in the array. If "CASH" is found in a cell in Column A, replace Column D of that same row With "USD".

I am hitting an error with the array.range. I realize I can't declare it as is but not sure what to do from here. I will likely his a snag with the loop as well, open to suggestion there too.

Thanks

    Sub test()

Dim Sht As Worksheet
Dim SCAFI, ILB, IILB, MMA, IMBD, SCAEQ, IMEQ, IREA, IMSC, ExclSecFI, ExclSecEQ As Worksheet
Dim FIFindCASH, FIFindCASHRng As Variant

Set FIFindCASHRng = Sheets(Array("SCA FI", "ILB", "IILB", "MMA", "IMBD", "Excluded Securities FI")).Range("A:A")


Set SCAFI = Sheets("SCA FI")
Set ILB = Sheets("ILB")
Set IILB = Sheets("IILB")
Set MMA = Sheets("MMA")
Set IMBD = Sheets("IMBD")
Set SCAEQ = Sheets("SCA EQ")
Set IMEQ = Sheets("IMEQ")
Set IREA = Sheets("IREA")
Set IMSC = Sheets("IMSC")
Set ExclSecFI = Sheets("Excluded Securities FI")
Set ExclSecEQ = Sheets("Excluded Securities EQ")

For Each Sht In FIFindCASHRng
    If Cell.Value = "CASH" Then
        Cell.Value.Offset(0, 3) = "USD"
    End If
Next

End Sub

Upvotes: 0

Views: 319

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Test the next code, please:

Sub testIterateChangeVal()
 Dim wb As Workbook, Sht As Worksheet, i As Long, lastRow As Long
 Set wb = ActiveWorkbook 'use here the needed workbook

 For Each Sht In wb.Worksheets
    Select Case Sht.Name
        Case "SCA FI", "ILB", "IILB", "MMA", "IMBD", "Excluded Securities FI"
            lastRow = Sht.Range("A" & Rows.count).End(xlUp).Row
            For i = 1 To lastRow
                If Sht.Range("A" & i).value = "CASH" Then
                    Sht.Range("A" & i).Offset(0, 3) = "USD"
                    'or
                    'Sht.Range("D" & i).value = "USD"
                End If
            Next i
    End Select
 Next
End Sub

Upvotes: 3

Related Questions