Emma has attended:
Excel VBA Introduction course
VBA Code
I have a piece of code that does an Index Match to a different worksheet. I then have another bit of code that copies this formula down to the end of the range which works nicely. However I want to make it more dynamic by removing the code to copy and paste, but when I do it doesn't seem to like it and I can't see what I'm doing wrong.
Currently the code I have that works is:
Range("AA2").FormulaR1C1 = _
"=IF(ISNA(INDEX('Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C67,MATCH(RC[-24],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C4,0),MATCH(RC[-25],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R8C67,0))),0,(INDEX('Y:\Carole\Travel\[Travel Claim spread" & _
"w 01.12.2017.xlsx]Travel Hours'!R8C4:R250C67,MATCH(RC[-24],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C4,0),MATCH(RC[-25],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R8C67,0))))"
And then
.Range("AA2").Copy
.Range("AA3:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).PasteSpecial Paste:=xlPasteFormulas
I have tried the following:
.Range("AA2:A" &.Cells(.Rows.Count, "B").End(x1Up).Row).FormulaR1C1 = _
"=IF(ISNA(INDEX('Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C67,MATCH(RC[-24],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C4,0),MATCH(RC[-25],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R8C67,0))),0,(INDEX('Y:\Carole\Travel\[Travel Claim spread" & _
"w 01.12.2017.xlsx]Travel Hours'!R8C4:R250C67,MATCH(RC[-24],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R250C4,0),MATCH(RC[-25],'Y:\Carole\Travel\[Travel Claim spreadsheet New 01.12.2017.xlsx]Travel Hours'!R8C4:R8C67,0))))"
I get an Application-defined or object-defined error.
Any help would be appreciated
RE: VBA Code
Hello Emma,
If you have a full set of data in Column Z (your code shows that you are in Column AA), you can use the data in column Z to drive the following code which will count the number of cells with data in Z and then fill your formula down to the last row in AA.
Dim lastrow As Long
Range("Z2").Select
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("AA2:AA" & lastrow).FormulaR1C1 = "your formula"
Please let us know if this works. If not, we can try something different.
Kind regards
Marius Barnard
STL
