How To Replace Cells With Specific Value In Excel?

user_11606675 0
Viewed 120

I have the following:

Private Sub Worksheet_Activate()
    If [E2] <> [G1] Then Range("H9:H44").Value = "Pendente"
End Sub

I need to include in the initial check whether the cells to be changed have a specific value.

I tried the following:

Private Sub Worksheet_Activate()
    If [E2] <> [G1] And Range("H9:H44").Value = "Paga" Then Range("H9:H44").Value = "Pendente"
End Sub

That is, I need it to change only the cells that have the value "Paga" in that column.

What is the correct way to do this?

vba excel
4 Answers

I need it to change only the cells that have the value "Paga" in that column.

Use .Replace so that it can replace all text in one go.

Option Explicit

Private Sub Worksheet_Activate()
    If [E2] <> [G1] Then
        Range("H9:H44").Replace What:="Paga", Replacement:="Pendente", LookAt:=xlWhole
    End If
End Sub
user_11405790

This should do it.

    With Sheet1 'Change as needed
        If .Cells(2, 5).Value <> .Cells(1, 7).Value Then
            Dim i As Long
            For i = 9 To 44
                If .Cells(i, 8).Value = "Paga" Then
                    .Cells(i, 8).Value = "Pendente"
                End If
            Next i
        End If
    End With
user_116435280

Try this, obviously change SheetName to your sheetname

For i = 9 to 44
x= Worksheets("SheetName").Cells(2,5)
y= Worksheets("SheetName").Cells(1,7)
z= Worksheets("SheetName").Cells(i,8)
If x<>y and z="Paga" then Worksheets("SheetName").Cells(i,8)="Pendente"
Next i
user_145396180

You can put this into a button (button below is called Replace) or use a macro

Private Sub Replace_Click()
Dim c As Range

For Each c In Range("H9:H44")

    If c.Value = "Paga" Then

     c.Value = "Pendente"

    End If

  Next c
End Sub

hope this helped

user_146333790
Related