Loop through a named range

Question

I have worksheet containing a load of cells formatted as currency made into a range using vba. I would like to loop through all the cells in the range and change the currency format the code I am trying is belowcode below:

Dim c As Range
Dim rnge As Range
Dim c as Range

Set rnge = Range("CampusCurrRng")
For Each c In rnge
    If Left(cell, 1) = "£" And cell.Value <> "" Then
        Left(cell, 1) = "R" And cell.Value * 50
    End If
Next

I keep getting the error 1004 Method 'Range' of object'_worksheet' failed. Has anyone any ideas why? and how to solve it.

Thank you

Answer 1

You need to check the NumberFormat of the Cells, Left will not work here. See code below

Dim rnge As Range
Dim cell As Range

Set rnge = Range("CampusCurrRng")
For Each cell In rnge
    ' in case you use pound in whatever format
    If InStr(cell.NumberFormat, "£") > 0 And cell.Value <> "" Then
        ' used the Rand default of Excel, can modify this line according to your need
        cell.NumberFormat = "[$R-430]#,##0.00"
        cell.Value = cell.Value * 50
    End If
Next

Answer 2

If your values are currency formatted then using left will not work - it pulls back the first digit, not the currency prefix.

This code may help you get started. It converts £ to R (South African Rand).

Sub CurrencyConverter()
    Dim cl As Range, rng As Range

    Set rng = Range("CampusCurrRng")

    For Each cl In rng
        If cl.NumberFormatLocal = "£#,##0;[Red]-£#,##0" And cl.Value <> "" Then
            cl = cl * 50
            cl.NumberFormat = "[$R-1C09] #,##0;[Red][$R-1C09] -#,##0"
        End If
    Next
End Sub

Notes:

To check for £ you need to specify the currency format. This is tricky. For example, I am in the UK and if I have £100 in cell A1:

Range("A1").NumberFormat ~~~> $#,##0_);[Red]($#,##0)
Range("A1").NumberFormatLocal ~~~> £#,##0;[Red]-£#,##0

You need to use NumberFormatLocal to pick up £. Put another way, this code will produce a negative result in the loop:

If cl.NumberFormat = "£#,##0;[Red]-£#,##0"...

The next bit is easier. You need to specify the output currency format. For South African Rand I have:

cl.NumberFormat = "[$R-1C09] #,##0;[Red][$R-1C09] -#,##0"

To get the one you need I would do the following:

1. Type a random value in a random cell e.g. 100 in cell A1
2. Select the cell and press F1 (brings up the Format Cells dialog)
3. Select Currency in the left hand navigation
4. Select your desired currency format in the Symbol drop down menu
5. Click OK
6. Now press F1 again
7. In the left hand navigation select Custom
8. The right hand navigation will now show the correct format

This all seems a little like hard work. I am interested to see if any simpler approaches surface!

Answer 3

shorter version that will keep your number format

For Each c In [CampusCurrRng] 
    If c.Text Like "*£*" Then c.Value = c * 50: _
        c.NumberFormat = Replace(c.NumberFormat, "£", "R")
Next

Answer 4

I have not tested this but try Dim c as variant. Alternatively, you can omit it completely if option has not been set to explicit since any variable that has not been declared is automatically dim_ed as variant type.

Also, unless your named range is of workbook scope, I would like to suggest that you add its worksheet specification to ensure everything runs predictably.

e.g.

Set rnge = Worksheets("example").Range("CampusCurrRng")

Lastly, like what M Lind said, it should be c instead of cell (missed that out on the first pass).