Macro/Excel Find Function

**ashleys.nl** · 09-14-2011, 12:35 PM

Hey Everyone

I've created a Macro uses the find function to find rows & columns and than collect the data from the row-Column intersection. It all works great when I use simple words like "cat" and "dog". However the actual report I'm retrieving data from has the cells formated in such a way that the find function doesn't work, not even when I use the excel find function. Are there other find fucntions I can use in my macro besides

Pressure04 = Worksheets("Sheet1").Cells.Find(What:="Cat", LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)

I've also attached a sample of the report columns. I can't seem to get the find function to pick up these columns.

Thanks in advance! You are all Amazing!

**StephenR** · 09-14-2011, 12:40 PM

What are you searching for?

**ashleys.nl** · 09-14-2011, 12:47 PM

Hey Stephen

I'm searching for the actual column titles for example in the attached workbook I would search for "DH Gauge Press" or "WHT @ SST". But I can't seem to find these cells when I'm using the find function. I believe it has something to do with how they are spaced. I've tried several combonations of typing these into the find functions but with no luck.

Also the reason I am trying to find these columns is sometimes the reports I recieve have new columns or rows inserted so I want something more reliable than just copying the value of an assigned cell. Say for example E30 in old reports has "Guage" information but in newer reports E30 has "Sub-Sea Choke" data.

Thanks for your interest

**StephenR** · 09-14-2011, 12:55 PM

Yes it's those invisible carriage return characters I think. Here is one approach (get rid of them):

Sub x()

Dim s As String

s = "DH Gauge Press"

Rows(5).Replace Chr(10), "", xlPart

MsgBox Worksheets("Sheet1").Cells.Find(What:=s, LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)


End Sub

**ashleys.nl** · 09-14-2011, 01:03 PM

Thanks,

But just so I'm clear.

This code goes to row 5 and replaces invisible charecters right? if so it a great approach but in these reports I'm working with rows are subject to change as well.

For example a year ago the Column titles would have been in row 4, today they are in row 5. and tommorow who knows where they could be. I'm trying to create a way of finding data regardless of its reference location.

Thank you much though

Anymore thoughts?

**StephenR** · 09-14-2011, 01:04 PM

This will do the whole sheet:

Cells.Replace Chr(10), "", xlPart

**ashleys.nl** · 09-14-2011, 01:05 PM

Ahhh ha! excellent I shall give this a try and get back to you! Thank you soo much!

**ashleys.nl** · 09-14-2011, 02:31 PM

Works Great... here's the final sample code I've constructed

Sub CharReplace()



Dim s As String

s = "DH Gauge Press"

Worksheets("Sheet1").Cells.Replace Chr(10), "", xlPart

MsgBox Worksheets("Sheet1").Cells.Find(What:=s, LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)


End Sub