CODEWITHSUNDEEP
excelvba
Josh Green
Josh Green

Reputation: 11

Multiple condition find and Replace

I am very new to this forum and to Excel VBA. I have learnt a little by playing with VBA for sometime. But now I am stuck in this little problem which I am sure the Excel Guru’s here would be in a position to help me. Any help on this would be really appreciated . Below is my Problem

Summary:

I have a spreadsheet with values in Columns A:F I want to find and replace values in Column C with Hex depending on the Value in Column C and Column A.

Example:

If Column A = 1 and Column C = General then replace General with 0x0001

But if Column A = 2 and Column C = General then replace General with 0x0002

My code:

Columns("C").Replace What:=" General", Replacement:=" 0x0001", LookAt:=xlWhole, _SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

Problem:

The code I currently have only looks at the values in Column C so changes every General to 0x0001.

Upvotes: 1

Views: 3078

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6206

I would use a combination of If and Case, the V3 test only has one possible trigger, no need to case that.

Sub Changes()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To N
        v1 = Cells(i, 1).Value
        v3 = Cells(i, 3).Value
        If v3 = "General" Then
            Select Case v1
                Case 1
                    Cells(i, 3).Value = "0x0001"
                Case 2
                    Cells(i, 3).Value = "0x0002"
                Case Else
            End Select
        End If
    Next i
End Sub

Upvotes: 1

user3819867
user3819867

Reputation: 1120

In addition to @garys-student 's answer, I'd like to say that the "Case" method makes it easier for larger decision trees, e.g:

Sub Changes()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To N
        v1 = Cells(i, 1).Value
        v3 = Cells(i, 3).Value
        Select Case v1
            Case 1
                Select Case v3
                    Case "General"
                        Cells(i, 3).Value = "0x0001"
                    Case Else
                End Select
            Case 2
                Select Case v3
                    Case "General"
                        Cells(i, 3).Value = "0x0002"
                    Case Else
                End Select
            Case Else
        End Select
    Next i
End Sub

The "Select Case v1" goes in lieu of "If v1=" and then the "Case 1" completes it to "If v1=1".

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

If you start with:

enter image description here

Running this macro:

Sub Changes()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To N
        v1 = Cells(i, 1).Value
        v3 = Cells(i, 3).Value
        If v1 = 1 And v3 = "General" Then Cells(i, 3).Value = "0x0001"
        If v1 = 2 And v3 = "General" Then Cells(i, 3).Value = "0x0002"
    Next i
End Sub

Will produce:

enter image description here

Upvotes: 2

Related Questions