excel nested if or vba code

[32131232424q2]

hi,

I've been asked to automate an excel facility to do the following:

px-last=fx-rate (multiply or divide by currency rate) and if currency = gbp then divide gbp rate by 100 first

Whether to multiply or divide depends on the currency

Currency PX_LAST FX Rate Fx Table
JPY 4,810.0000 104.0000 EUR EUR Curncy 1.3000 M
EUR 16.0000 1.5000 GBP GBP Curncy 1.9957
CHF CHF Curncy 1.0023
SEK SEK Curncy 5.8490
NOK NOK Curncy 4.9527
CAD CAD Curncy 1.0059
JPY JPY Curncy 104.7600
HKD HKD Curncy 7.7934
SGD SGD Curncy 1.3501

Whether to multiply or divide by depends on the currency. I can write a vba case statement as I think this would be much clearer than nested ifs, but I don’t know how to get column b within excel to to refer to colum c.

Using a formula I’d simply use:

$b2=$c2….
$b3=$c3..
And so on

Not sure how to do this within vba

Any help much appreciated.

thx

 

[Bong]

cells(2,2)=cells(2,3)

or

[b2]=[c2]

_________________
Bob Rashkin

 

[SkipVought]

It would be much more instructive if you were to post the code segment in question. It is significant if you are using a loop or referencing cells explicitly.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[xlbo]

From what I can see there is no need for VBA to do this - could easily be written in an IF statement on the worksheet...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[32131232424q2]

xlbo, you can only nest a maximum of 7 if statements, plus it would be harder to read

bong,

if I wanted to do this for multiple rows, say b2-10, c2-10
would I just list each value explicity or is there a way to dynamically account for new rows being added?

thx

 

[Bong]

[b2:b10]=[c2:c10]

The form [<whatever>] is shorthand for range("<whatever>")

_________________
Bob Rashkin

 

[xlbo]

32131232424q2 - really - you don't say

why do you need nested ifs?

=if(currencycell = "gbp" , "do something","do something else")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[32131232424q2]

xlbo, because i have 9 currencies and maybe more in future...

 

[xlbo]

apologies - thought there were just 2 currencies

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[32131232424q2]

Ok, so i now have a function that I can use, but I have an issue where I've hard coded the cell references, sorry to seem obtuse, but how would I change these references to relative values?

Thanks

Function update_cells() As Double

If Cells(c2) = "EUR" Then 'C2
e2 = f2 * o2
ElseIf Cells(c2) = "GBP" Then
e2 = (f2 / 100) * o3
ElseIf Cells(c2) = "CHF" Then
e2 = f2 * o4
ElseIf Cells(c2) = "SEK" Then
e2 = f2 / o5
ElseIf Cells(c2) = "NOK" Then
e2 = f2 / o6
ElseIf Cells(c2) = "CAD" Then
e2 = f2 / o7
ElseIf Cells(c2) = "JPY" Then
e2 = f2 / o8
ElseIf Cells(c2) = "HKD" Then
e2 = f2 / o9
ElseIf Cells(c2) = "SGD" Then
e2 = f2 / o10
ElseIf Cells(c2) = "AUD" Then
e2 = f2 / o11
ElseIf Cells(c2) = "INR" Then
e2 = f2 / o12
ElseIf Cells(c2) = "KRW" Then
e2 = f2 / o13
ElseIf Cells(c2) = "USD" Then
e2 = f2 / o14
End If

'cells(2,3) takes row and cols vals so this refers to c2
'TOOLS MACRO INSERT MODULE FOR FUNCTION

End Function

 

[misterstick]

this is a case for VLOOKUP.

1. tidy up your table of currencies, rates and whether to divide or multiply. (i'd advise you to get this consistent and always do one or the other: it's not hard)
something like this:

[tt]
CCY RATE D/M
GBP 1.0000 M
USD 2.1985 D
KWD 3.6666 M
[/tt]

2. include in your destination sheet three extra columns

Col1: =vlookup(<ccyref>, <tableref>, 2, false)
Col2. =vlookup(<ccyref>, <tableref>, 3, false)
Col3. =iif(<Col1>="M",<valueref>*col2,<valueref>/col2)

simpler.

if you need to extend this to have multiple rates for each currency ( ie GBP-EUR <> EUR-GBP) then create your leftmost colunm in the table with both currencies instead of one.

hope this helps.


mr s. <

 

[xlbo]

Pass the range through as an argument to the function - you can thgen use OFFSET to determine which cells to use

Function update_cells(rngTest as range) As Double


If rngTest = "EUR" Then
rngTest.offset(0,2) = rnfTest.offset(0,3) * rngTest.offset(0,12)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[32131232424q2]

thx for your input people, i cracked it with:



Sub calc_cells()



Dim End_Row As Long, n As Long



End_Row = Range("C" & Rows.Count).End(xlUp).Row



For n = 2 To End_Row



Select Case Cells(n, 3).Value



Case "EUR": Cells(n, 5).Formula = "=F" & n & "*O" & 2

Case "GBP": Cells(n, 5).Formula = "=(F" & n & "/100)*O" & 3

Case "CHF": Cells(n, 5).Formula = "=F" & n & "*O" & 4

Case "SEK": Cells(n, 5).Formula = "=F" & n & "*O" & 5

Case "NOK": Cells(n, 5).Formula = "=F" & n & "/O" & 6



Case "CAD": Cells(n, 5).Formula = "=F" & n & "/O" & 7

Case "JPY": Cells(n, 5).Formula = "=F" & n & "/O" & 8



Case "HKD": Cells(n, 5).Formula = "=F" & n & "/O" & 9

Case "SGD": Cells(n, 5).Formula = "=F" & n & "/O" & 10



Case "AUD": Cells(n, 5).Formula = "=F" & n & "/O" & 11

Case "INR": Cells(n, 5).Formula = "=F" & n & "/O" & 12



Case "KRW": Cells(n, 5).Formula = "=F" & n & "/O" & 13

Case "USD": Cells(n, 5).Formula = "=F" & n & "/O" & 14





End Select



Next n

End Sub