Translate Excel Function to VBA

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about
Code:
   Range("I" & nr).FormulaR1C1 = "=IF(rc7=""Delivery"",(10000+IF(rc4=""Ain Jarfa"",1000,3000)),10000)"
 
Upvote 0
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).
 
Upvote 0
How about
Code:
   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
 
Upvote 0
My pleasure & thanks for the feedback
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Thank you so much Joe4, this really helps me a lot, appreciate that
 
Upvote 0
You are welcome.
Glad we were able to help.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top