Translate Excel Function to VBA

[NDMDRB]

Hi,

Can someone please help me translate this function to VBA?

=IF(G5="Delivery",(10000+IF(D5="Ain Jarfa",1000,3000)),10000)

I need it in Sub cmdNew_Click as:
ws.Cells(nr, "I").formula = (The above function)

Note:

• Instead of "G5" put (ws.Cells(nr, "G"))
• Instead of "D5" put (ws.Cells(nr, "D"))

Many thanks in advanced

 

[Fluff]

How about

   Range("I" & nr).FormulaR1C1 = "=IF(rc7=""Delivery"",(10000+IF(rc4=""Ain Jarfa"",1000,3000)),10000)"

 

[Joe4]

Note: One trick to get most of the code you need it so simply turn on your Macro Recorder, and record yourself entering the formula into a cell.
By default, Excel will use the relative referencing (R1C1) in the formula (and not hard-coding).

 

[NDMDRB]

How about

   Range("I" & nr).FormulaR1C1 = "=IF(rc7=""Delivery"",(10000+IF(rc4=""Ain Jarfa"",1000,3000)),10000)"

Thank you so much Fluff, this is what I need

 

[Fluff]

My pleasure & thanks for the feedback

 

[NDMDRB]

Note: One trick to get most of the code you need it so simply turn on your Macro Recorder, and record yourself entering the formula into a cell.
By default, Excel will use the relative referencing (R1C1) in the formula (and not hard-coding).

Thank you Joe4 for your note, actually I recorded a macro, but my problem was with the raw number

 

[Joe4]

Thank you Joe4 for your note, actually I recorded a macro, but my problem was with the raw number

Yep, it is literal with where the formula is placed (the left side of the formula), but using relative referencing for the right side (the details of the formula).

Note that you can use the "Cells" functionality too, instead of the "Range" option, i.e.

Cells(nr, "I")

instead of

Range("I" & nr)

Where "Cells" is sometimes advaantageous is that you can use the column letter or number in it, which can come in handy if you want to loop through a bunch of columns.
So, since column "I" is the 9th column, it could also be written:

Cells(nr, 9)

So let's say that you wanted to loop through columns I-M, you could so something like:

For c = 9 to 13
    Cells(nr, c)...

Just a little extra info for you...

 

[NDMDRB]

Thank you so much Joe4, this really helps me a lot, appreciate that

 

[Joe4]

You are welcome.
Glad we were able to help.