VBA find a code and replace the value

[jevi]

Hi All,

I have registered this macro but is not working well. I would like that check the data in column A (might be everyday different rows of data) and if you find the code 100000 then in the column it should write the code 90...so in this case it did find the code 100000 in the row A306 and A307 and it changed the data in row Q306 and Q307 but the problem is that this code 100000 is not everyday in the same rows, sometimes it might be in two or 3 rows. Any help how to achieve it?

Columns("A:A").Select
    Selection.Find(What:="100000", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Range("Q306:Q307").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "990"
    Range("Q307").Select
    ActiveCell.FormulaR1C1 = "990"
    Range("Q307").Select

Thank you

 

[Fluff]

Is that code on it's own in the cell & is it a number or text?

 

[jevi]

Is that code on it's own in the cell & is it a number or text?

Hi Pluff,

The code it is in its own and it looks like a number but if formatted as General.

thnx

 

[Fluff]

If you use

=isnumber(A2)

in another cell what does it say. Change the A2 to a cell that contains 100000

 

[jevi]

If you use

=isnumber(A2)

in another cell what does it say. Change the A2 to a cell that contains 100000

it says true...

 

[Fluff]

Ok, how about

Sub jevi()
   With Range("A2", Range("A" & rows.count).End(xlUp))
      .Offset(, 16).Value = Evaluate(Replace("if(" & .Address & "=100000,990,if(@="""","""",@))", "@", .Offset(, 16).Address))
   End With
End Sub

 

[jevi]

Ok, how about

Sub jevi()
   With Range("A2", Range("A" & rows.count).End(xlUp))
      .Offset(, 16).Value = Evaluate(Replace("if(" & .Address & "=100000,990,if(@="""","""",@))", "@", .Offset(, 16).Address))
   End With
End Sub

It worked so great . Thank youuuuuu so much as it is a daily task.

 

[Fluff]

You're welcome & thanks for the feedback.