Add formula into VBA code

chrysti

Board Regular
Joined
Dec 20, 2006
Messages
218
I want to add the following formula into my VBA...

I need it to paste into cell N2 and copy down to the bottom of the data

My formula is:

=IF(M2="","",INDEX(DropDownLists!$G$1:$H$5010,MATCH(LineGuide!$M2,DropDownLists!$G$1:$G$5010,0),MATCH(LineGuide!N$1,DropDownLists!$G$1:$H$1,0)))

My other problem is the "" that don't work in VBA...anyone know a way around this???

Thanks in advance for the help
C
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello,

You will need to use double quotes like """"

Maybe like this...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Maybe()<br><br>    <SPAN style="color:#00007F">With</SPAN> Range("N2")<br>        .FormulaR1C1 = "=IF(M2="""","""",INDEX(DropDownLists!$G$1:$H$5010,MATCH(LineGuide!$M2,DropDownLists!$G$1:$G$5010,0),MATCH(LineGuide!N$1,DropDownLists!$G $1:$H$1,0)))"<br>        .AutoFill Destination:=Range("N2:N25"), Type:=xlFillDefault<br>        Range("N2:N25").Select<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Does that fill to row 25 or so?

Also where is your data to fill to, or in other words, how would you like to determine how far down to fill this formula?
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
Since the formula references column M, I'm assuming we can use that column to determine how far down to extend the formula and you can put them all in at once with

<font face=Courier New>Range("N2:N" & Range("M" & Rows.Count).End(xlUp).Row).Formula = _<br>    "=IF(M2="""","""",INDEX(DropDownLists!$G$1:$H$5010," _<br>        & "MATCH(LineGuide!$M2,DropDownLists!$G$1:$G$5010,0)," _<br>        & "MATCH(LineGuide!N$1,DropDownLists!$G$1:$H$1,0)))"</FONT>
 
Last edited:
Upvote 0

chrysti

Board Regular
Joined
Dec 20, 2006
Messages
218
the "" in the formula don't work in the above...and I want to go down to row 1000, I really need the "" though :(

Thanks
C
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
the "" in the formula don't work in the above...and I want to go down to row 1000, I really need the "" though :(

Thanks
C
Not sure which 'above' you are talking about. When entering a formula with vba you will need to double up the quotes as repairman has stated.
However, I don't believe repairman's code as stated will work, not because of the quotes, but because ..
a) the formula (copied from your post) has a space in it that needs removing, and
b) the formula is not in R1C1 notation

Did you try my code?
 
Upvote 0

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I wondered if it would work.
I also was not sure about the line "Range("N2:N25").Select"

Any rate, I was digging your one liner Peter. :)
 
Upvote 0

Forum statistics

Threads
1,191,356
Messages
5,986,177
Members
440,008
Latest member
Cmbuck

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
Top