VBA Code

Resolved · Medium Priority · Version 2010

Wed 31 Jan 2018, 13:46 Delegate Emma said...

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

Thu 1 Feb 2018, 09:37 Trainer Marius said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Hide separate columns in Excel 2010

If you want to hide columns not adjacent to each other for example, Columns A, C and E then:-

1) Click on the fist column to be hidden i.e. A

2) Press and hold down the CTRL key

3) While holding the CTRL key, left click on the rest of the columns you want to hide i.e. C and E

4) Right click and choose Hide

View all Excel hints and tips