Scatchy head time again. VBA formulas

Nygie

Board Regular
Joined
Apr 15, 2015
Messages
50
Afternoon all. Done some work on my project today and it's been going pretty well. Getting towards then end of moving data around and started inserting some formulas.
The first formula went fine, it just adds/combines 2 Cells together down the columns into a new column.
My next formula references another spreadsheet to pull some data across and that is where things get a little out of my league.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim lLastRow As Long[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] lLastRow = Range("A" & Rows.Count).End(xlUp).Row

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Columns("C").Insert
Range("C1") = "Full Name"
Range("C2:C" & lLastRow).FormulaR1C1 = "=RC[-2] & "" "" & RC[-1]"
'Inserts column C and combines columns A & B into it

Columns("A").Insert
Range("A1") = "A/C"
Range("A2:A" & lLastRow).FormulaR1C1 = "=INDEX('X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!B:B,MATCH(D2,'X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!A:A,0))"

Supposed to insert formula in column A but it's adding extra parenthesis around one of the A range ref's and one of the B range refs also putting ' ' around my cell reference (D2). Also not advancing the cell reference as you go down the column like the above formula (top section).
Below is how the formula ends up in the worksheet. If I remove the extra () & ' ' the formula works.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX('X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!B:(B),MATCH('D2','X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!A:(A),0))
Am I missing something? Or is it a little more complicated than I thought it would be??
Thanks for any help.
[/FONT]<strike></strike>
[/FONT]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try .Formula instead of .FormulaR1C1
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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