Calculating Dollars not GBP in a row

[piggylumps]

Is there anyway I can calculate the sum of a certain currency in a row while ignoring others?

Say for example I have a range of a1:d1, a1 will be $25, b1 will be £36, c1 will be $75 and d1 will be £5.

So what I'm looking for will be a formula in e1 which tells it to sum the range only if the numbers are entered in $'s.

Hope that makes sense.

 

[piggylumps]

I'm thinking the same to be honest with you as I've now changed all the currencies to the correct ones and all I'm now showing is #NAME? in every single totals cell. Time to play with some macro security, wish me luck as macros aren't my strong suit, as you've propbaly guessed by now

 

[piggylumps]

Changed and rechanged the security settings but I'm still getting the annoying #NAME? return, completely at a loss now

 

[jasonb75]

Are you using named ranges in your formula?

 

[piggylumps]

My formula is =IF(A2="","",SUMCURR(G2:R2,"GBP")) and obviously "USD" and "EURO" for those currencies

 

[jasonb75]

This should tell us if the code is being permitted to run.

Open the code editor, then on the left of the window, find and double click "ThisWorkbook"

Copy this code into the editor

Private Sub Workbook_Open()
MsgBox "Macros are enabled"
End Sub

Close the editor, then save and close your workbook.

When you re-open the workbook you should see either a message "macros are enabled" or a prompt to enable them.

Does that cure the #NAME? error?

 

[piggylumps]

My man you're an absolute star. All cured. Thank you so much for all your help and patience, it's greatly appreciated<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[jasonb75]

You're welcome,

I wasn't going to let it beat me

 

[piggylumps]

I know that feeling all to well

 

[piggylumps]

Trying to add an extra column to calculate YEN but it's not seeing the values I've inputted. I've changed the code to show:

Public Function SUMCURR(rng As Range, curr As String) As Currency
Select Case curr
Case "USD"
curr = "$"
Case "EURO"
curr = "€"
Case "GBP"
curr = "£"
Case "YEN"
curr = "¥"
End Select
For Each cell In rng
If InStr(cell.Text, curr) Then
SUMCURR = SUMCURR + cell.Value
End If
Next
End Function

but it's returning a zero value.

I'll be so glad when I can put this spreadsheet to bed once and for all

 

[piggylumps]

I've just done a =CELL("FORMAT", ) on a cell that contains a Yen value and I'm getting a ,2 returned instead of ,0- which is what I'm looking for, anyone know how I can correct this?