Find cell containing £

[coop123]

Hi

I am using the following code to find cells formatted as currency £.

Cells.Find(What:="£", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

This is giving me the following error message, Run-time error '91': object variable or With block variable not set.

Can someone please give me some help to fix the problem.

Thanks

coop123

 

[anand3dinesh]

Hi

I am using the following code to find cells formatted as currency £.
View attachment 81401

This is giving me the following error message, Run-time error '91': object variable or With block variable not set.

Can someone please give me some help to fix the problem.

Thanks

coop123

try below code

Sub test()

Dim FindVal As Range

Set FindVal = Cells.Find(What:="£", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

FindVal.Activate

End Sub

 

[coop123]

Hi anand3dinesh

I copied you code but I still get same error

coop123

 

[anand3dinesh]

Hi anand3dinesh

I copied you code but I still get same error

coop123

"£" symbol, is it you type manually or number formatting?
if it is number formatting then yeah code gives error, because this symbol comes from formatting but it does not exist in your workbook.
I may be wrong. May be excel experts would able to help you

 

[coop123]

The "£" is in the cell by data formating

 

[anand3dinesh]

check below code. this highlights number format (£) cells with green

Sub Macro3()

Dim lrow As Double
lrow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To lrow
    If Range("B" & i).NumberFormat = """£ ""0" Then
    Range("B" & i).Interior.ColorIndex = 4
End If
Next

End Sub

 

[coop123]

I tried this but it didn't highlight anything!!

Is the problem the cell formating?

coop123

 

[anand3dinesh]

Sorry there was a space between your number format and value

if your values in column B then, try below. else change column reference

Sub Macro3()

Dim lrow As Double
lrow = Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To lrow
    If Range("B" & i).NumberFormat = "$0" Then
    Range("B" & i).Interior.ColorIndex = 4
End If
Next

End Sub

 

[coop123]

Sorry it still doesn't work.
I changed $ to £

coop123

 

[anand3dinesh]

Sorry it still doesn't work.
I changed $ to £

coop123

Change it back to $, it will work