Trying to change cells to GBP currency format using VBA

[itm]

I am trying to write some VBA (in Excel 2016) to look for USD currency values in a range of cells and change the cell format to GBP. The code below spots the USD cells (i.e. the If statement is reached, but the cells remain formatted as "$n.nn". Could anyone explain what I'm doing wrong?

Sub ChangeCurrency()
Dim cl As Range


For Each cl In Intersect(ActiveSheet.Range("C24:I26"), ActiveSheet.UsedRange)
        If InStr(1, cl.Text, "$") > 0 Then
            cl.Style = "Currency"
            cl.NumberFormat = "[$£-809]#,##0.00"
        End If


Next cl
 
End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Sub ChangeCurrency()
    Dim cl As Range
    
    For Each cl In ActiveSheet.Range("C24:I26")
            If InStr(1, cl.Text, "$") > 0 Then
                cl.Value = CCur(cl.Value)
                cl.Style = "Currency"
                cl.NumberFormat = "[$£-809]#,##0.00"
            End If
    Next cl
 
End Sub

Dave

 

[rlv01]

FWIW, I tested your code as-is and it works perfectly.

 

[itm]

Hi,
see if this update to your code does what you want

Sub ChangeCurrency()
    Dim cl As Range
   
    For Each cl In ActiveSheet.Range("C24:I26")
            If InStr(1, cl.Text, "$") > 0 Then
                cl.Value = CCur(cl.Value)
                cl.Style = "Currency"
                cl.NumberFormat = "[$£-809]#,##0.00"
            End If
    Next cl
 
End Sub

Dave

I get a Type Mismatch on this line:
cl.Value = CCur(cl.Value)

The cell in question contains a value of $1.39 and has a format of Currency
???

 

[johnnyL]

    With Range("C4:I26")
        .NumberFormat = "£#,##0.00"
    End With

 

[itm]

    With Range("C4:I26")
        .NumberFormat = "£#,##0.00"
    End With

Not all of the cells in that range contain currency values - hence the original loop structure and If statement.

If I try this I get the same result (i.e. none of the cells are altered):

With Range("C4:I26")
        If InStr(1, .Text, "$") > 0 Then
            .NumberFormat = "£#,##0.00"
        End If
    End With

 

[dmt32]

Error suggests some of your cells contain strings

Try this update

Sub ChangeCurrency()
    Dim cl As Range
    
    For Each cl In ActiveSheet.Range("C24:I26")
        If InStr(1, cl.Text, "$") > 0 Then
            With cl
                If IsNumeric(.Value) Then .Value = CCur(cl.Value) Else .Value = CCur(Mid(.Value, 2))
                .Style = "Currency"
                .NumberFormat = "[$£-809]#,##0.00"
            End With
        End If
    Next cl
 
End Sub

Dave

 

[johnnyL]

Not all of the cells in that range contain currency values - hence the original loop structure and If statement.

If I try this I get the same result (i.e. none of the cells are altered):

With Range("C4:I26")
        If InStr(1, .Text, "$") > 0 Then
            .NumberFormat = "£#,##0.00"
        End If
    End With

You can't use a With/End With like that. That would never work.

Try the following:

    Dim cl As Range
    
    For Each cl In ActiveSheet.Range("C24:I26")
        If InStr(1, cl.Text, "$") > 0 Then
            cl.NumberFormat = "£#,##0.00"
        End If
    Next cl

 

[itm]

Error suggests some of your cells contain strings

Try this update

Sub ChangeCurrency()
    Dim cl As Range
   
    For Each cl In ActiveSheet.Range("C24:I26")
        If InStr(1, cl.Text, "$") > 0 Then
            With cl
                If IsNumeric(.Value) Then .Value = CCur(cl.Value) Else .Value = CCur(Mid(.Value, 2))
                .Style = "Currency"
                .NumberFormat = "[$£-809]#,##0.00"
            End With
        End If
    Next cl
 
End Sub

Dave

That's it - perfect!
Many thanks

 

[dmt32]

That's it - perfect!
Many thanks

welcome - glad we were able to help

Appreciate the feedback

Dave