Comparing Cell Text Not the Formula Behind the Cell Text

[baldbrian]

Hi,

I have this macro to look at a cell and if it finds a certain value, copy that row to another sheet. My formula works if there is only Text in the cell and fails if a formula is creating the text in the cell.

Example Data:

	Col A	Col B
Row 1	Project Num <tbody> </tbody> <tbody> </tbody>	Project Type <tbody> </tbody>
Row 2	PM00049355 <tbody> </tbody>	Senior Living <tbody> </tbody>
Row 3	269591 <tbody> </tbody>	Residential <tbody> </tbody>
Row 4	269588 <tbody> </tbody>	Residential <tbody> </tbody>
Row 5	R000030236 <tbody> </tbody>	Retail <tbody> </tbody>
Row 6	274403 <tbody> </tbody>	Residential <tbody> </tbody>
Row 7	CU00000445 <tbody> </tbody>	Commercial <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

Now, with the data above in Column B... There is an underlying formula to figure this out (see below):

=IF(B2="","",IF(LEFT(B2,2)="PM","Senior Living",IF(LEFT(B2,2)="CP","Commercial",IF(LEFT(B2,1)="R","Retail",IF(LEFT(B2,2)="CU","Commercial","Residential")))))

So, even though you "see" the words Residential, Retail, Commercial, or Senior Living, the cell actually contains the above formula.

The Problem...

If I copy the contents of Column B and then Paste-Special-Values into Column C... my macro code works perfectly to find all Residential rows and copy and paste them into another sheet.

However, if I try to run my macro on Column B, my macro can not find the "Residential" lines...

Here is the code that I can't quite get to see the text in those cells...

If Range("B" & CStr(LSearchRow)).Text = "Residential" Then

I have also tried using .Value in the place of .Text. How do I get my macro to look for the visual text "Residential"?

Thank you for any help!

 

[baldbrian]

Correction to first code sample... should have been looking in Col A, sorry about that.

[COLOR=#333333]=IF(A2="","",IF(LEFT(A2,2)="PM","Senior Living",IF(LEFT(A2,2)="CP","Commercial",IF(LEFT(A2,1)="R","Retail",IF(LEFT(A2,2)="CU","Commercial","Residential")))))[/COLOR]