Add formula into VBA code

[chrysti]

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

 

[repairman615]

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?

 

[Peter_SSs]

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>

 

[chrysti]

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

 

[Peter_SSs]

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?

 

[repairman615]

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.

 

[chrysti]

Peter - thanks works perfectly!!!!