# Find cell containing £



## coop123 (Dec 22, 2022)

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 (Dec 22, 2022)

coop123 said:


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


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 (Dec 22, 2022)

Hi anand3dinesh

I copied you code but I still get same error 

coop123


----------



## anand3dinesh (Dec 22, 2022)

coop123 said:


> 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 (Dec 22, 2022)

The "£" is in the cell by data formating


----------



## anand3dinesh (Dec 22, 2022)

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 (Dec 22, 2022)

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

Is the problem the cell formating?




coop123


----------



## anand3dinesh (Dec 22, 2022)

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 (Dec 22, 2022)

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

coop123


----------



## anand3dinesh (Dec 22, 2022)

coop123 said:


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


Change it back to $, it will work


----------



## coop123 (Dec 22, 2022)

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


----------



## coop123 (Dec 22, 2022)

I changed it back as you suggested but still no success.

I have found another peice of code that does what I need.


> Dim rng As Range, r As Range, rFound As Range
> 
> Set rFound = Nothing
> For Each r In ActiveSheet.UsedRange
> ...


Thank you for your time trying to fix my problem.

coop123


----------

