Code allows text entry, but not formula

immyjimmy

Active Member
Joined
May 27, 2002
Messages
254
Greetings,

I wrote a macro to add a formula to a cell, then copy the cell down
In cell A1, I have the customer's name
In cell A3, I have the sales order number

Our purchasing department keeps the equipment lists on the network drive in a specific folder. All the files are under the same format, so by concatenating the first part of the path with the file name with the ending portion of the path, I can create an INDEX/MATCH formula that works beautifully.

Here's the following code:

Sub To_Do_List_1()
Dim Sales_Number As String
Dim Sales_Name As String
Dim File_Name As String
Sales_Number = Sheets("Sheet1").Range("A3")
Sales_Name = Sheets("Sheet1").Range("A1")
File_Name = Sales_Number & " " & Sales_Name
Range("Q4").Select
ActiveCell.FormulaR1C1 = "'=INDEX('T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$D$9:$D$1000,MATCH($B2,'T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$E$9:$E$1000,0))"
Range("M4:Q4").Select
Selection.Copy
Range("M5:Q5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Call Remove_Button_3
End Sub

The code places the formula, copies and pastes it down as desired except for one thing: If I don't put the " ' " before the = sign, I get a runtime error.

Once the macro is run and the text placed, I can go into the cell and remove the " ' ". Then the formula functions as desired.

What am I missing?

Thanks,
Jim
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Jim

I don't use R1C1 notation but I've found that using Value works fine for adding a formula, still need the "" of course.
Code:
ActiveCell.Value = "=INDEX('T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$D$9:$D$1000,MATCH($B2,'T:\SALES\Equipment List\Current Equipment Lists\[" & File_Name & ".XLS]Equipment List'!$E$9:$E$1000,0))"
HTH

Regards
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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