Translate Excel Function to VBA

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
How about
Code:
   Range("I" & nr).FormulaR1C1 = "=IF(rc7=""Delivery"",(10000+IF(rc4=""Ain Jarfa"",1000,3000)),10000)"
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,216
Office Version
365
Platform
Windows
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).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
My pleasure & thanks for the feedback
 

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,216
Office Version
365
Platform
Windows
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.
Code:
Cells(nr, "I")
instead of
Code:
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:
Code:
Cells(nr, 9)
So let's say that you wanted to loop through columns I-M, you could so something like:
Code:
For c = 9 to 13
    Cells(nr, c)...
Just a little extra info for you...
 
Last edited:

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
Thank you so much Joe4, this really helps me a lot, appreciate that
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,216
Office Version
365
Platform
Windows
You are welcome.
Glad we were able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,453
Messages
5,414,633
Members
403,538
Latest member
indiemusicboy

This Week's Hot Topics

Top