I am a newbie to VBA.

I am dealing with the following simple VBA problem.

For example, I would like to replace the corresponding value in column D with 0 or empty, if B matches exactly 'USD', 'CNY', 'HKD'.

And I would like to apply the same logic to another sheet that also consists of the currency column and column D.

CodePudding user response：

Please, put the below simple code on a new module ( if you do not know ,google for how to insert a new module excel vba ? ) This code will run only on the active sheet.

Option Explicit
'Option Compare Text
Sub Replace_the_corresponding()
    With Application
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
       .EnableEvents = False
    End With
 
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim B_Column As Range: Set B_Column = ws.Range("B2", ws.Cells(Rows.Count, "B").End(xlUp))
 
Dim cell As Object
  For Each cell In B_Column
   If cell.value = "USD" Or cell.value = "CNY" Or cell.value = "HKD" Then
     cell.Offset(, 2).value = ""
      End If
       Next cell
 
   With Application
      .Calculation = xlCalculationAutomatic
      .ScreenUpdating = True
      .EnableEvents = True
    End With
End Sub