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

Question

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

FaneDuru · Accepted Answer · 2020-04-23 13:17:29Z

3

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

edited Apr 23, 2020 at 13:17

answered Apr 23, 2020 at 13:13

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related