excel 使用If/Then循环计算相邻单元格

7ajki6be  于 2023-03-24  发布在  其他
关注(0)|答案(1)|浏览(234)

我认为/希望我在构建这些循环时接近正确。本质上,我有两列与支付费用相关的不同ID。我需要屏蔽ID,但它们是文本而不是数字,所以我能想到的唯一方法是使用以下循环。
它还没有工作。代码运行,我认为它是接近,但我必须错过一些明显的,因为没有填充在相邻的单元格。任何想法,我如何可以调整这使之发生?

Sub Mask_Account()

Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets("Fee Letter Formatted")
Columns(3).EntireColumn.Insert
Columns(6).EntireColumn.Insert

Dim rcell As Range, rng As Range
Set rng = ThisWorkbook.Worksheets("Fee Letter Formatted").Range("B6:B200")
   For Each rcell In rng.Cells
   If Not IsError(rcell.Value) Then
      If rcell.Value <> "" Then
      rcell(0, 1).Formula = "=""****-*"" & right(rcell,3)"
      End If
    End If
               Next rcell

Dim rcell2 As Range, rng2 As Range
Set rng2 = ThisWorkbook.Worksheets("Fee Letter Formatted").Range("D6:D200")
   For Each rcell2 In rng2.Cells
      If Not IsError(rcell2.Value) Then
      If rcell2.Value <> "" Then
      rcell2(0, 1).Formula = "=""****-*"" & right(rcell2,3)"
      End If
      End If
   Next rcell2

'Sheets("Fee Letter Formatted").Range("B,D").EntireColumn.Delete

End Sub

下面的答案解决了大多数问题,我也遇到了一个问题,因为D可以是几个不同的值。它可以是“”,也可以是“与注册帐户相同”我试图用下面的代码修改它,但现在代码运行,但没有做任何事情。

For i = 1 To UBound(dArr)
    If Not IsError(dArr(i, 1)) Then
        If dArr(i, 1) <> "Same as Enrolled Account" Then
        dArr(i, 1) = dArr(i, 1)
        ElseIf dArr(i, 1) <> " " Then
        dArr(i, 1) = dArr(i, 1)
        ElseIf dArr(i, 1) <> "" Then
        dArr(i, 1) = "=""****-*""&right(" & Chr(34) & dArr(i, 1) & Chr(34) & ",3)"
        End If
    End If
Next i
qv7cva1a

qv7cva1a1#

尝试

Sub maskID()
Dim bArr, dArr
Dim i As Long

bArr = ThisWorkbook.Worksheets("Fee Letter Formatted").Range("B6:B200").Value
dArr = ThisWorkbook.Worksheets("Fee Letter Formatted").Range("D6:D200").Value

For i = 1 To UBound(bArr)
    If Not IsError(bArr(i, 1)) Then
        If bArr(i, 1) <> "" Or bArr(i, 1) <> "Same as Enrolled Account" Or bArr(i, 1) <> " " Then
            bArr(i, 1) = "=""****-*""&right(" & Chr(34) & bArr(i, 1) & Chr(34) & ",3)"
        End If
    End If
Next i

For i = 1 To UBound(dArr)
    If Not IsError(dArr(i, 1)) Then
        If dArr(i, 1) <> "" Or dArr(i, 1) <> "Same as Enrolled Account" Or dArr(i, 1) <> " " Then
            dArr(i, 1) = "=""****-*""&right(" & Chr(34) & dArr(i, 1) & Chr(34) & ",3)"
        End If
    End If
Next i

ThisWorkbook.Worksheets("Progress").Range("B6:B200").Value = bArr
ThisWorkbook.Worksheets("Progress").Range("D6:D200").Value = dArr
End Sub

相关问题