PDA

View Full Version : In macro how do i go to last worked cell



getsatv
04-24-2008, 11:24 PM
Hi Iam very new to Excel Vba iam facing a problem i have recored a macro

which allows me to copy the formula from active cell to last worked cell in the same column but the problem is that its been hardcoded next time i run a macro it runs only from b1 to b5456 but data is actually more than that.

so code should be in such a way that it should check the last worked row in the particular column where i execute the macro.

Here is the code which i have recored

Selection.copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-7, -1).Range("A1:A8").Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-5098, 0).Range("A1:A5099").Select (here it gives me run time a error 1004 message)
ActiveCell.Activate
ActiveSheet.Paste
End Sub

Bob Phillips
04-25-2008, 12:14 AM
That code is very difficult to follow without seeing the data.

Can you tell us in words what it does?

getsatv
04-25-2008, 12:19 AM
Example

Iam having a formula in b2 column say a lookup formula or sum formula

i want to copy the formula from b3 to till last worked row in the same column

Hope its clear now

Bob Phillips
04-25-2008, 12:22 AM
What determines the last row, a value in A or somewhere else?

getsatv
04-25-2008, 12:37 AM
AS i have stated earlier A contians a formula same formula should be copied to last worked row in the same column

Example C2 has a formula =sum(A1+B1)

Last worked cell is c1000 i want macro which could find last worked row in the column and paste the same formula.

Next time worked cell may be c100 or c15000 macro should be able find the last worked row in the column and paste the formula till the last worked row in the column

hope it is clear now.

Bob Phillips
04-25-2008, 12:59 AM
AS i have stated earlier A contians a formula same formula should be copied to last worked row in the same column

No, you said B, B2 to be specific.


Example C2 has a formula =sum(A1+B1)

And now you are saying C, so which is it?


Last worked cell is c1000 i want macro which could find last worked row in the column and paste the same formula.

Next time worked cell may be c100 or c15000 macro should be able find the last worked row in the column and paste the formula till the last worked row in the column

I know that, but if I am going to filldown to some cell, I have to have a way of determining what the last cell is. For instance, you may have ids in column A, and a formula in B2. You could then work out the last cell to fill in B by working out where the last row in A is.

So how do I tell?

getsatv
04-25-2008, 01:32 AM
Yes Nihil i want work out the last cell to fill in B by working out where the last row in A is. Your right

I think i confuesed iam sry for that

Thanks in advance.

Bob Phillips
04-25-2008, 01:54 AM
Yes Nihil ...

That is a Latin quote, not my name.



Range("B2").AutoFill Range("B2").Resize(Range("A2").End(xlDown).Row - 1)