Application or Object Defined Error

[jkharel]

Hello all,
I am trying to write a macro where I want the column T of my first file to be column J + column O of another file(fName) - column J of this file(this workbook). The way I am trying to do it use the sum function to do something like

'Range("T2").Formula = "=SUM('[fName]Per Agent Per Day Hr Breakdown'!$J$2, '[fName]Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

where fName is the name of the file that I have as parameter. However, I realised that this way when I autofill the column T cells are not updated to J3 O3 and so on.

Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Activate
Worksheets("Per Agent Per Day Hr Breakdown").Select
Range("T2").Formula = "=SUM('[fName]Per Agent Per Day Hr Breakdown'!$J$2, '[fName]Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T" & lRow), Type:=xlFillDefault

I tried to solve the problem by using VLOOKUP to create two columns T and U to get values in column J and O of other sheet and use col T + col U - col J to do what I want. For Vlook up, I tried
Range("T2").Formula = "=VLOOKUP(E2, [fName]Per Agent Per Day Hr Breakdown'!$C:$O, 13, FALSE)"
where I am trying to get the values in column O. But I get an error 'Run time error 1004 : Application Defined or Object Defined Error'
Can anyone please help me how to fix this error or a better way to solve the problem?
Thanks

 

[tico_ocit]

Range("T2").Formula = "=SUM('[fName]Per Agent Per Day Hr Breakdown'!$J2, '[fName]Per Agent Per Day Hr Breakdown'!$O2, J2*-1)"

Your are blocking the autofill by having $J$2 or $O$2, for that work you must have $J2 or $O2.
Hope this solve your problem.

 

[jkharel]

Range("T2").Formula = "=SUM('[fName]Per Agent Per Day Hr Breakdown'!$J2, '[fName]Per Agent Per Day Hr Breakdown'!$O2, J2*-1)"

Your are blocking the autofill by having $J$2 or $O$2, for that work you must have $J2 or $O2.
Hope this solve your problem.

Hello,

Thank you so much for your response. I removed the $ from 2's but now the error is method formula of object range is not defined.
Here is the revised code. The Vlookup is commented out but fails for same reason too. I would really appreciate your help and feedback.

Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Activate
Worksheets("Per Agent Per Day Hr Breakdown").Select
Range("T2").Formula = "=SUM([fName]Per Agent Per Day Hr Breakdown!$J2, [fName]Per Agent Per Day Hr Breakdown!$O2, J2 * -1)"
'Range("T2").Formula = "=VLOOKUP(E2, [fName]Per Agent Per Day Hr Breakdown!$C:$O, 13, FALSE)"

Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T" & lRow), Type:=xlFillDefault



Workbooks(fName).Close

 

[Joe4]

In VBA, it is important to distinguish literal text from variables. Everything that is enclosed with double quotes is treated as literal text. All variables must be outside of the double-quotes in order to be treated as literal text. You can sew the parts together using ampersand (&).

So if you have a variable named "fname", if you want to include it in your formula, it would look something like this:
Range("T2").Formula = "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

Note: To make sure you have the formula written exactly the way it needs to be, you can use a MsgBox to see exactly what you are building, i.e.
MsgBox "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

You can compare that to an example of a working formula you have on your sheet, to make sure the syntax matches exactly.

 

[tico_ocit]

In VBA, it is important to distinguish literal text from variables. Everything that is enclosed with double quotes is treated as literal text. All variables must be outside of the double-quotes in order to be treated as literal text. You can sew the parts together using ampersand (&).

So if you have a variable named "fname", if you want to include it in your formula, it would look something like this:
Range("T2").Formula = "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

Note: To make sure you have the formula written exactly the way it needs to be, you can use a MsgBox to see exactly what you are building, i.e.
MsgBox "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

You can compare that to an example of a working formula you have on your sheet, to make sure the syntax matches exactly.

Nice input @Joe4 ! I tested only with some random ranges! So thank you for helping!

 

[jkharel]

In VBA, it is important to distinguish literal text from variables. Everything that is enclosed with double quotes is treated as literal text. All variables must be outside of the double-quotes in order to be treated as literal text. You can sew the parts together using ampersand (&).

So if you have a variable named "fname", if you want to include it in your formula, it would look something like this:
Range("T2").Formula = "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

Note: To make sure you have the formula written exactly the way it needs to be, you can use a MsgBox to see exactly what you are building, i.e.
MsgBox "=SUM('" & fName & "Per Agent Per Day Hr Breakdown'!$J$2, '" & fName & "Per Agent Per Day Hr Breakdown'!$O$2, J2*-1)"

You can compare that to an example of a working formula you have on your sheet, to make sure the syntax matches exactly.

Thank you so much for your response. I am very new to this and really appreiciate it.
In my code just before I set the formula I have filtered my data to match the data that I reference by fName. The whole sheet has thousands of rows but after I filter I want the formula to be applied only to filtered rows. I am starting from 2 and going all way to lRow which is the last row of the sheet and was assuming the rows reference was not absolute. However, it turns out the rows reference are absolute and the formula changes whole dataset. How could I use formula to filtered data set not knowing what the absolute row number of first and last row is on the filtered data set. Also, the fName comes from file upload and I do not want fName to be a part of formula. May be there is a way to keep only values after using formula. Could you please suggest me on this?
Thanks

 

[Joe4]

n my code just before I set the formula I have filtered my data to match the data that I reference by fName. The whole sheet has thousands of rows but after I filter I want the formula to be applied only to filtered rows. I am starting from 2 and going all way to lRow which is the last row of the sheet and was assuming the rows reference was not absolute. However, it turns out the rows reference are absolute and the formula changes whole dataset. How could I use formula to filtered data set not knowing what the absolute row number of first and last row is on the filtered data set. Also, the fName comes from file upload and I do not want fName to be a part of formula. May be there is a way to keep only values after using formula. Could you please suggest me on this?

This is a whole different question than your original question, which was about the error in your VBA (there was no mention of filters in your original question).

Since you are new to the board, here is some advice to maximize your chances of getting answers.
1. If you have a new question, it is best to post it to its own thread (even if it is the same project for you). That way it appears as a new unanswered question and shows up in the "Unanswered Thread" that many people use to look for unanswered questions.
2. Only post additional questions to the original thread if it is a directly-related follow-up question (that is still about the original question).

So I would recommend posting this question to a new thread.

 

[jkharel]

This is a whole different question than your original question, which was about the error in your VBA (there was no mention of filters in your original question).

Since you are new to the board, here is some advice to maximize your chances of getting answers.
1. If you have a new question, it is best to post it to its own thread (even if it is the same project for you). That way it appears as a new unanswered question and shows up in the "Unanswered Thread" that many people use to look for unanswered questions.
2. Only post additional questions to the original thread if it is a directly-related follow-up question (that is still about the original question).

So I would recommend posting this question to a new thread.

Thank you so much for your feedback. Sorry, I will keep that in mind from next time.