带有范围的VBA表格阵列,在单元格中循环并改变单独列的值

我们的目标是查看数组中所有Sheet的A列。如果在Column A的单元格中发现 “CASH”,则用 “USD “替换同一行的Column D。

我在array.range上遇到了一个错误。我意识到我不能像现在这样声明它,但不知道接下来该怎么办。我很有可能在循环中遇到了问题,也请大家给我一些建议。

谢谢你的建议。

    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

解决方案:

请测试下一段代码。

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

给TA打赏
共{{data.count}}人
人已打赏
解决方案

无法使用SSL连接到IBMMQ 7.5。

2022-5-11 14:00:08

解决方案

在xcode-select --install后得到 "由于网络问题,无法下载软件 "的提示。

2022-5-11 14:00:12

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索