figment
07-03-2008, 10:03 AM
i can not find the error in this code
there is a formula in column A, that will retrun "" when it runs out of values, this macro should check to see if the last instance of this formula is returning a value, and if it is it will copy it down till it returns ""
the problem is that it is part of a list of information and which might be added to at a later date, so i want to make sure that all information is filled down with column A.
Private Sub Worksheet_Activate()
Dim a As Long, b As Long
With Worksheets("Metrics")
a = .Range("A7").End(xlDown).Row
While .Range("A" & a) <> ""
b = .Range("A" & a).End(xlToRight).Column
.Range("A" & a).Resize(0, b).AutoFill .Range("A" & a).Resize(1, b), xlFillDefault
a = .Range("A7").End(xlDown).Row
Wend
End With
End Sub
right now i am getting and error at the line
.Range("A" & a).Resize(0, b).AutoFill .Range("A" & a).Resize(1, b),xlFillDefault
the error is:
Run-time error '1004':
application-defined or object-defined error.
ever thing i can find shows that i am using the correct objects with the corect inputs, so if some one could help me out i would be great full.
there is a formula in column A, that will retrun "" when it runs out of values, this macro should check to see if the last instance of this formula is returning a value, and if it is it will copy it down till it returns ""
the problem is that it is part of a list of information and which might be added to at a later date, so i want to make sure that all information is filled down with column A.
Private Sub Worksheet_Activate()
Dim a As Long, b As Long
With Worksheets("Metrics")
a = .Range("A7").End(xlDown).Row
While .Range("A" & a) <> ""
b = .Range("A" & a).End(xlToRight).Column
.Range("A" & a).Resize(0, b).AutoFill .Range("A" & a).Resize(1, b), xlFillDefault
a = .Range("A7").End(xlDown).Row
Wend
End With
End Sub
right now i am getting and error at the line
.Range("A" & a).Resize(0, b).AutoFill .Range("A" & a).Resize(1, b),xlFillDefault
the error is:
Run-time error '1004':
application-defined or object-defined error.
ever thing i can find shows that i am using the correct objects with the corect inputs, so if some one could help me out i would be great full.