Application or Object Defined Error

jkharel

New Member
Joined
Aug 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
VBA Code:
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.
 
Upvote 0
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top