CODEWITHSUNDEEP
excelvba
J.Joe
J.Joe

Reputation: 11

how can I Find a cell for multiple number and change its value in Excel

I want to find a cell and change its value in another cell, but how can I do it for mutiple cell ? 

Sub replace_sales()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    For i = 1 To 10000
        If ws.Cells(i, 1) = "1932597" Then
            ws.Cells(i, 9) = "0"
        End If
    Next i
Next ws

End Sub

Upvotes: 1

Views: 50

Answers (1)

QHarr
QHarr

Reputation: 84465

Sounds like you might want Select Case. In the case part you can specify which numbers to test for and what to do in each case. You can have multiple values on the same line. If using numbers you don't need "". Those are for string literals.

Option Explicit
Public Sub replace_sales()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For i = 1 To 10000
            Select Case ws.Cells(i, 1)
            Case 1932597, 1234, 123
                ws.Cells(i, 9) = 0
            Case 12345
               ws.Cells(i, 9) = 1
            End Select
        Next
    Next ws
End Sub

Upvotes: 1

Related Questions