Hello I have a small problem searching for a value then getting the
value in the next column. If "spontaneous" is in the cell then I'd
prefer the next column's (B) cell to be 1 instead of the word in column
A being replaced with 1 (e.g. c.Value="1").

Would it be something like c.[offset].Value="1"?

Thanks for your help!

Shaun

--Code--
With Worksheets(1).Range("a1:a5")
Set c = .Find("spontaneous", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = "1"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

c.offset(0,1).Value = "1"

But with 5 cells, I would think just looping through the cells would be easier.

Shaun wrote:

Hello I have a small problem searching for a value then getting the
value in the next column. If "spontaneous" is in the cell then I'd
prefer the next column's (B) cell to be 1 instead of the word in column
A being replaced with 1 (e.g. c.Value="1").

Would it be something like c.[offset].Value="1"?

Thanks for your help!

Shaun

--Code--
With Worksheets(1).Range("a1:a5")
Set c = .Find("spontaneous", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = "1"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--

Dave Peterson

Thanks for your help David, works perfectly!

Usually I work with cells(iRow, iColumn).value to target other cells so
this offset and range stuff was confusing :).

I was limiting it to only 5 cells because I was getting error 91 about
wrong object / while values. I thought it has something to do with the
value being replaced so this offset seems to have fixed that problem
also.

Shaun

Glad you got it working.



Shaun wrote:

Thanks for your help David, works perfectly!

Usually I work with cells(iRow, iColumn).value to target other cells so
this offset and range stuff was confusing :).

I was limiting it to only 5 cells because I was getting error 91 about
wrong object / while values. I thought it has something to do with the
value being replaced so this offset seems to have fixed that problem
also.

Shaun

--

Dave Peterson