Searching for a value for and then replacing when found.
 

Can someone tell me how I would do the following:

- I have numbers in cells E5:E10; A1 and B1
- I would like to search for A1 in cells E5:E10
- On the first instance I find of A1, I would like to replace the
value of that cell with B1

What would be a simple way to write this?

Thanks,
Fish

Searching for a value for and then replacing when found.
 

Sub getNfix()
Dim c As Range
Dim ws As Worksheet
Set ws = Worksheets(1) 'Change to actual
For Each c In ws.Range("E5:E10")
If c = Range("a1").Value Then
c.Value = Range("B1").Value
End If
Next

" wrote:

Can someone tell me how I would do the following:

- I have numbers in cells E5:E10; A1 and B1
- I would like to search for A1 in cells E5:E10
- On the first instance I find of A1, I would like to replace the
value of that cell with B1

What would be a simple way to write this?

Thanks,
Fish

Searching for a value for and then replacing when found.
 

missed the End Sub on copying.

Sub getNfix()
Dim c As Range
Dim ws As Worksheet
Set ws = Worksheets(1) 'Change to actual
For Each c In ws.Range("E5:E10")
If c = Range("a1").Value Then
c.Value = Range("B1").Value
End If
Next
End Sub

"JLGWhiz" wrote:

Sub getNfix()
Dim c As Range
Dim ws As Worksheet
Set ws = Worksheets(1) 'Change to actual
For Each c In ws.Range("E5:E10")
If c = Range("a1").Value Then
c.Value = Range("B1").Value
End If
Next

" wrote:

Can someone tell me how I would do the following:

- I have numbers in cells E5:E10; A1 and B1
- I would like to search for A1 in cells E5:E10
- On the first instance I find of A1, I would like to replace the
value of that cell with B1

What would be a simple way to write this?

Thanks,
Fish

Searching for a value for and then replacing when found.
 

Here is a method using an enumerated search...

Sub ChangeNumber()
Dim X As Long
For X = 5 To 10
If Range("A1").Value = Cells(X, "E").Value Then
Cells(X, "E").Value = Range("B1").Value
Exit For
End If
Next
End Sub

Rick


wrote in message
...
Can someone tell me how I would do the following:

- I have numbers in cells E5:E10; A1 and B1
- I would like to search for A1 in cells E5:E10
- On the first instance I find of A1, I would like to replace the
value of that cell with B1

What would be a simple way to write this?

Thanks,
Fish

Searching for a value for and then replacing when found.
 

Thanks JLGWhiz and Rick. I went with the enumerated search and it
worked like a charm.