Cell Text Search & Insert Value in Next Column

S

Shaun

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
 
D

Dave Peterson

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

But with 5 cells, I would think just looping through the cells would be easier.
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
 
S

Shaun

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
 
D

Dave Peterson

Glad you got it working.


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top