Cell Text Search & Insert Value in Next Column
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 |
Cell Text Search & Insert Value in Next Column
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 |
Cell Text Search & Insert Value in Next Column
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 |
Cell Text Search & Insert Value in Next Column
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 |
All times are GMT +1. The time now is 12:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com