Simple VBA issue

[Jameo]

Hi all,

I have the following code:

Public Sub copydata()
Dim LR As Long
Dim TR As Long
LR = Sheets("Imported Fuel Usage Data").Range("A" & Rows.Count).End(xlUp).Row
TR = Sheets("FData").Range("A" & Rows.Count).End(xlUp).Row
 
Sheets("Imported Fuel Usage Data").Range("A8:B" & LR).Copy Destination:=Sheets("fdata").Range("A" & 1)
Sheets("fdata").Select
Sheets("fdata").Range("C1").Formula = "=((B1+B2)/2)*(A2-A1)"
Sheets("fdata").Range("C1").AutoFill Destination:=Range("C1:C" & TR), Type:=xlFillDefault
 
End Sub

However, it is failing on autofilling the formula down to the last cell. If I use LR as the last row identifier than it works, but as there are 7 rows more data on the ("Imported Fuel Usage Data") it autofills the forumla down to far.

Also, ideally I would like to fill the formula down to the last row but one, but can't figure out how to set the variable for such a function.

Any help would be great.

Thanks in advance

 

[sous2817]

the problem is, you're finding the value of TR before you paste information over to that sheet. So it's getting a value of 0.

Also, I don't see why you'd have to convert the formula to R1C1...as I have it posted, it should work (albeit not tested).

Either way, try this:

Public Sub copydata()
Dim LR As Long
Dim TR As Long
LR = Sheets("Imported Fuel Usage Data").Range("A" & Rows.Count).End(xlUp).Row
 
Sheets("Imported Fuel Usage Data").Range("A8:B" & LR).Copy Destination:=Sheets("fdata").Range("A" & 1)

TR = Sheets("fdata").Range("A" & Rows.Count).End(xlUp).Row -1

Sheets("fdata").Select
Sheets("fdata").Range("C1").Formula = "=((B1+B2)/2)*(A2-A1)"
Sheets("fdata").Range("C1").AutoFill Destination:=Range("C1:C" & TR), Type:=xlFillDefault
 
End Sub

That should do it....

 

[Jameo]

LOL, ofcourse that is the problem. The variable is defined before there is any data copied into the sheet.

How stupid of me. Thanks for that mate

Edit: Thanks to you as well sous. I have to convert it to R1C1 else the same formula would be pasted into every cell, where it needs to change based on the cell reference, if you see what I mean. If I just put it into the range, and dont use autofill i mean

Cheers again

 

[Jameo]

Working

Thanks

 

[sous2817]

Main thing is, you've got it working. Best of luck with the rest of your project!