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
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