For Each x in Worksheets

[jojones]

I want to go through every cell on the current worksheet, or every cell on all worksheets to find where a given string exists.

Where a cell contains this string I want to analyse the entire string to determine what is in that cell and return a new value to that cell. I was thinking of using the For Each x in Worksheets, but am not exactly sure how to use it or if you can return values to cells using this method.

I have the code I need for the analysis of the string, but I need to know how to use the For Each... to find each instance of my string, get the value from that cell and then return the new value to that cell...

Anyone?

 

[LSTAN]

Hi jojones

Dim x As Range
assume you have a range named myRng
For Each x in Range("myRng&quot
'
'
'Your string anaylsis code here
'
'
Next x


rgrds
LSTAN

 

[RobBroekhuis]

If you have multiple worksheets, you probably still want to loop through them, using something like

for each Sh in Worksheets
with Sh
for each cell in Sh.usedrange
...
next cell
end with
next Sh

Is there a particular reason you're not using the Find capability in Excel? That will do the same job much faster than running through your cells programmatically.
Rob

 

[jojones]

Thanks guys - these are very helpful. Was not declaring my x as a range which was the problem.

Rob - I am not using the Find/Replace because I need to look at substrings of the cell value before I know what the replace will be. ie.

Looking for string 'NM' in the worksheet
Found in cell where value = 0564NM464L
Use substrings of cell value to allocate new code according to certain business rules.
Return value = "ABCD2002P"

It is really not a straightforward search and replace.

Jo

 

[RobBroekhuis]

You should still use Find, just not Replace. That will get you to the cells you're interested in more quickly, and then you can do whatever you need to do with the cell.
Rob

 

[jojones]

I will give it a go. Hope you are around next week incase I don't get it to work. Have a nice weekend.

 

[RobBroekhuis]

I will not be in next week. Maybe this snippet of code from one of my applications will be helpful in guiding your programming:

dim c as range
...
With Worksheets(sheetnr).UsedRange
   lastrow = 0
   Set c = .Find(findterm, LookIn:=xlValues, lookat:=xlPart)
   If Not c Is Nothing Then
      firstaddress = c.Address
      Do
         ...process cell c here...
         Set c = .FindNext(c)
      Loop While Not c Is Nothing _
                 And c.Address <> firstaddress
   End If
End With

Good luck!
Rob