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
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