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'!BB),MATCH('D2','X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!AA),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]
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'!BB),MATCH('D2','X:\CIS\Fire Iinstallations\[RED.xlsx]RED'!AA),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]