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]

Do you have any data in row "A" of sheet FData?

Also, if you want it to be one less than the last row, something like:

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

should work.

 

[Jameo]

Hi mate,

I do indeed have data in row A of the fdata sheet, as for the second part, thanks that works perfectly, just couldn't remember the coding.

As for the first part, I am completely lost as it should work fine.

 

[Jameo]

Grrr, working now for some reason, lol. Cheers for the help

--Edit:

It has stopped working again. It worked once and now is failing. I am at a complete loss

 

[Jameo]

The error I am getting is:

Autofill method of range class failed.

 

[sous2817]

What's the value of TR when the code runs (not the value you expect it to be, but what it's actually assigned)?

Also, you don't need to autofill, you could do something like:

Sheets("fdata").Range("C1:C" & TR).Formula = "=((B1+B2)/2)*(A2-A1)"

 

[CharlesChuckieCharles]

The error I am getting is:

Autofill method of range class failed.

Are you intending to get the row counts from two different sheets?

 

[sous2817]

ah...good catch CharlesChuckieCharles!

Either use LR through out your code, or find the value of TR AFTER you paste over the values from "Imported Fuel Usage Data".

 

[Jameo]

I am not quite sure I understand your question.

I have dim TR as Long

and TR = Sheets("FData").Range("A" & Rows.Count).End(xlUp).Row

I do not set it as TR = 0 etc.

The last row of data in my sheet is at row 2881.

I will try the above method, although I guess I would have to change the forumla to:

Sheets("fdata").Range("C1:C" & TR).FormulaR1C1 = "=((RC[-1]+R[1]C[-1])/2)*(R[1]C[-2]-RC[-1]))"

My complete code with the above changes is:

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").Range("C1:C" & TR).FormulaR1C1 = "=((RC[-1]+R[1]C[-1])/2)*(R[1]C[-2]-RC[-1]))"
 
End Sub

Still getting an error message :/

 

[CharlesChuckieCharles]

Move

TR = Sheets("FData").Range("A" & Rows.Count).End(xlUp).Row

to after you've pasted in your data