Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba code

ResolvedVersion 2010

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

Excel tip:

Change the Value of a Constant

When using a named constant in a worksheet, you may wish to change the value of that constant.

From the 'Insert' menu, select 'Name', then select 'Define'.

In the 'Define Name' dialog box, select the constant that you want to change.
Change the value in the 'Refers To' box.
Click OK.

Wherever that named constant has been used it will now use its new value.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.