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.

 

[jasonb75]

Are you getting some correct results for each currency type?

There could be some inconsistancy in the cell format being used for some currencies.

 

[piggylumps]

I'm not getting anything for the dollars, the Euro is spot on and the GBP is working on some rows but not on others, sorry to be a pain

 

[jasonb75]

I just found something that I didn't think would work, try this code as alternative.

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

You will need to recalculate excel after changing the code. (press F9, in excel, not the code editor).

Hopefully this one will work

 

[piggylumps]

SOOOOOOOOO close now. The GBP AND Euro are working correctly, however the USD is displaying every instance that a $ is shown in the row instead of summing them, example: $1888$289$30 instead of $2207

 

[jasonb75]

Have you made any changes to the code, or are you using SUMCURR as part of a longer formula?

I can't make it do the same thing so this could take a while to find the cause.

 

[piggylumps]

code is exactly as you wrote it (thank you very much for that, I would never have thought of writing a module) the only thing I changed is the =SUMCURR formula so that it shows the complete row I'm working on, so it now reads =SUMCURR(G4:R4,"USD"), quite bizaare as it's working fine for the other two.

 

[jasonb75]

Have you tried saving and closing the file then restarting excel?

Do you get the same error if you try it in another workbook, or if possible, the same workbook on another PC?

What do you get as the result of =CELL("Format",G4) (change G4 to a single cell that holds a USD value in G4:R4)?

There is probably something simple causing the problem, the hard part is finding it.

 

[piggylumps]

closed it down and restarted it to the same effect. the returned value to =CELL("FORMAT",G4) is C0-

I'm ready for slinging this sodding thing out the window now lol

 

[jasonb75]

I notice that I didn't properly declare some of the valiables, although I don't thin kit would cause the problem you're having, it's worth eliminating it as a cause.

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

Remember to rerfesh excel with F9 after changing the code.

 

[piggylumps]

hmmmm that hasn't made any difference, but now if I open it up on anothers machine I get #NAME? in the total cells. Talk about your head scratcher