Change currency throughout workbook based on dropdown value

[ctcarroll]

I have tried to find my answer via past posts/answers but must be missing something or continually mis-typing.
I have a 53 worksheet workbook that has 10 sets of 5-worksheet accounts which include tabular data, and charts, including speedometer charts. It is working fine but I want users to be able to set currency symbols. The idea is that they will keep the basic workbook and copy/save it for each new project that sometimes will require non-U.S. currency.

I am currently getting "subscript out of range" error messages on the following macro:

Sub SetCurrency2()

Dim i As Integer
If Sheets("Spending Tracker").Range("N2") = "US" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$$-409] * #,##0.00"
End With
Next i
ElseIf Sheets("Spending Tracker").Range("N2") = "UK" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "£#,##0.00"
End With
Next i
ElseIf Sheets("Spending Tracker").Range("N2") = "EUR" Then
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$€-2] * #,##0.00"
End With
Next i
Else
For i = 1 To 53
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "¥#,##0.00"
End With
Next i
End If

End Sub

I have referenced the "Spending Tracker" worksheet where the dropdown exists (and which also has currency data) but also tried referring to it as "Sheet2" in case that was the problem. It apparently isn't.

I will appreciate some guidance on addressing this problem.

Thanks,
Charlie Carroll

 

[Gerald Higgins]

Which line of code is highlighted when you get the "subscript out of range" message ?

 

[ctcarroll]

Gerald:
Thank you for responding to my question. The line of code highlighted is:

If Sheets("Spending Tracker").Range("N2") = "US" Then

That spelling is accurate. I have also tried referring to it as "Sheet2" which it chronologically is. I verified that there are 53 worksheets in the workbook.

Again, thank you for your time and kind effort,
Charlie Carroll

 

[tusharm]

Check the spelling of the worksheet name and in the code. You may have a different number of spaces or you may have a special character that looks like a space.

Also, you can avoid changing the worksheets one at a time. Use the worksheets.FillAcrossSheets method. The help page for the method is quite easy to understand.

Gerald:
Thank you for responding to my question. The line of code highlighted is:

If Sheets("Spending Tracker").Range("N2") = "US" Then

That spelling is accurate. I have also tried referring to it as "Sheet2" which it chronologically is. I verified that there are 53 worksheets in the workbook.

Again, thank you for your time and kind effort,
Charlie Carroll

 

[ctcarroll]

To Tusharm:
Thank you for adding guidance to my thread. I actually can't use the "fillacrosssheets" method because each set of five sheets within a given subaccount differ from each other. there is a data entry sheet, a speedometer gauge sheet which does have a tabular area, a calculations sheet which is all tabular, a detailed analysis sheet which has two charts and tabular data and an alternate analysis sheet which has one chart with tabular data. This leaves the home worksheet, "Spending Tracker," which I did check the spelling on, an "all gauges" worksheet which has a central combined speedometer surrounded by smaller gauges from each sub-account and a calculations sheet which performs the calculations for the combined speedometer.
In the process of reviewing my code I did note that I was using i = 1 to 53 to substitute for the various "sheets" when the first sheet should be "sheet0" and the last sheet should be "sheet52" so i changed that to i = 0 to 52. This resulted in a new line marked for having its "subscript out of range." it is now the italicized, enboldened line after the final "Else":

Sub SetCurrency2()

Dim i As Integer
If Sheets("Sheet1").Range("N2") = "US" Then
For i = 0 To 52
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$$-409] * #,##0.00"
End With
Next i
ElseIf Sheets("Sheet1").Range("N2") = "UK" Then
For i = 0 To 52
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "£#,##0.00"
End With
Next i
ElseIf Sheets("Sheet1").Range("N2") = "EUR" Then
For i = 0 To 52
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "[$€-2] * #,##0.00"
End With
Next i
Else
For i = 0 To 52
With Sheets("Sheet" & i).Range("A1:Z1000")
.Style = "Currency"
.NumberFormat = "¥#,##0.00"
End With
Next i
End If

End Sub

 

[dmt32]

rather than use sheet names try using the worksheets(index) to iterate through your workbook.

Not tested but something like this perhaps:

Dave

Sub SetCurrency2()
    Dim wsST As Worksheet
    Dim NumFormat As String
    Dim i As Integer

    Set wsST = Worksheets("Spending Tracker")
    Select Case wsST.Range("N2").Value
    Case "US"
        NumFormat = "[$$-409] * #,##0.00"
    Case "UK"
        NumFormat = "£#,##0.00"
    Case "EUR"
        NumFormat = "[$€-2] * #,##0.00"
    Case Else
        NumFormat = "¥#,##0.00"
    End Select
    For i = 1 To 53
        With Sheets(i).Range("A1:Z1000")
            .Style = "Currency"
            .NumberFormat = NumFormat
        End With
    Next i
End Sub

 

[ctcarroll]

Dave:

Thank you for your suggestion. It actually moved me closer to the solution. The code resulted in an "application-defined or user-defined error," highlighting the line:

.style = currency

It actually changed the first three worksheets but changed ALL numbers to the selected code (in this case, the Euro); including dates and account numbers that I guess I should format as strings.

I am wondering if I can use an additional "with" indentation. I obviously need to isolate those numbers that are already being formatted as currency. Am trying to run that down.

Again,
Thanks,
Charlie Carroll

 

[ctcarroll]

I am continuing in my effort to identify cells formatted as currency to change them to a selected currency. I am posting this in order to get more eyes on it and, hopefully :>)), suggestions.
The Cell function provides for information type and reference (the cell address). So, I get the following results for
=CELL("format",XX): $2.43 yields C2- 7/4/2012 yields D4 18881 yields G $1,532.19 yields C2- ($789.04) yields C2- 13.5% yields P1 and 25-Sep-12 yields D1

Then applying =LEFT(CELL("format",XX),1) yields C, D, G, C, C, P and D

This is as far as I am going tonight but it is a step closer to being able to evaluate cells and reformat only those containing currency into a desired currency format.

Charlie Carroll

 

[Jeff The Masher]

Quick Question, does it matter which currency you are formatting too when it fails?? (have you tried it with all of the required currencies?) I notice you are using two different methods of setting the cell format. look at the syntax of the US dollars versus the english pound.

 

[ctcarroll]

Next steps on my trail of tears.

In answer to Jeff's question, it doesn't matter. I realize Iwas sloppy on using varying conventions and will clean that up.

Found a way to change existing values formatted as currency while leaving others alone as follows:

Sub setcurrency()
Dim rCell As Range, j As String
ActiveSheet.Unprotect

For Each rCell In ActiveSheet.Range("A1:I100")

If Left(r.Text, 1) = "$" Or Left(r.Text, 1) = "£" _
Or Left(r.Text, 1) = "€" Or Left(r.Text, 1) = "¥" _
Or Mid(r.Text, 2, 1) = "$" Or Mid(r.Text, 2, 1) = "€" _
Or Mid(r.Text, 2, 1) = "¥" Or Mid(r.Text, 2, 1) = "£" Then

With rCell
.Style = "Currency"
.NumberFormat = "€#,###.00;[Red]-€#,###.00"
End With
End If
Next rCell
ActiveSheet.Protect

End Sub

This works because the text value starts with the currency symbol in the first position OR with a minus sign or left parenthesis in the first positon and the currency symbol in the second position. I used it on a 5 worksheet test of my 53 worksheet workbook and it did exactly what I expected.

Unfortunately it didn't solve the problem because it leaves cells formatted but currently empty (which would include data entry cells that also need to have the correct currency) with their original format. In order to solve this problem, need to evaluate the formatting of each cell and change formatting for those formatted as a currency; whether empty or filled. This is the kind of thing that the CELL function seems to be good for but VBA doesn't support that function so I tried to use the "evaluate" method. Used the square brackets ([ and ]) to reduce the need to figure out all of the quotation marks. Had this success in a test workbook and simplified macro:

Sub testeval()
Dim j As String

j = [LEFT(CELL("FORMAT",A16),1)]

If (j = ",") Then
With ActiveSheet.Range("A16")
.Style = "currency"
.NumberFormat = "€#,###.00;[Red]-€#,###.00"
End With
End If

End Sub

Used a literal cell address, A16, just to keep it simple. Looked for j = "," because all of the currencies seemed to evaluate as ",2-" and I knew I didn't have any other decimal numbers to work with. I use integers for things like numbers of days expired in the fiscal year.

Tried plugging that idea back into another simple test macro and keep getting the usual litany of "mismatched" and similar sins. I don't understand this part. Here is the last thing I tried:

Sub setcurrency()
Dim rCell As Range, j As String
ActiveSheet.Unprotect

For Each rCell In ActiveSheet.Range("A1:I100")

j = [LEFT(CELL("FORMAT",rCell),1)]

If j = "," Then
With rCell
.Style = "Currency"
.NumberFormat = "€#,###.00;[Red]-€#,###.00"
End With
End If
Next rCell

ActiveSheet.Protect
End Sub

I tried just doing the "for each rcell in range ...." and used a "MsgBox rCell.Value" statement; it moved right through all of the cells in the range, showing either the correct value of the cell or a blank (which was the correct value of the cell content). It seems like I should be able to substitute rCell without quotation marks into the evaluation formula since I do not need quotation marks when using a specified cell address.

Again, will appreciate input as I feel i am circling the solution (perhaps "chasing the bull's eye" to use the marksman's term)

Thank you,
Charlie Carroll