PDA

View Full Version : Autofill



KURLL
11-02-2011, 10:58 AM
Hi,

I need some help to finish a macro to run an autofill!

I'm trying to indicate the final range through the contents of the A1 cell:

Dim lastrow As String

lastrow = Cells(1, 1)
Selection.AutoFill Destination:=Range("A5", lastrow), Type:=xlFillDefault


Someone knows the reason for do not assume the variable?

Many Thanks

Kenneth Hobs
11-02-2011, 11:19 AM
Welcome to the forum!

Always Dim row numbers as Long.

darweesh8
02-13-2012, 10:27 PM
Kenneth Hobs,

can you modify his code so it find the last cell in the column and fill up to it?

regards,

Darweesh

frank_m
02-13-2012, 11:35 PM
If there is a value already in Column A that you are using to determine the last row, then this should fill the value that is in "A5", down to one cell above the last row.
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A5").AutoFill .Range("A5:A" & LastRow - 1), Type:=xlFillDefault
End With

darweesh8
02-14-2012, 12:27 AM
Thanks Frank but it worked only if i put it in sub procedure and call it in the main, if i put it directly to main a run time error occured " class failed"

frank_m
02-14-2012, 12:47 AM
Can you through together a sample workbook that fails so I see what you mean? - Or at least more of your main sub code. And a perhaps a little more detail of the data and sheet structure.

darweesh8
02-14-2012, 01:03 AM
here it is Frank, this sheet will open a raw data and copy from it to this workbook.
i will upload the raw data next post

darweesh8
02-14-2012, 01:03 AM
here is the raw data

Kenneth Hobs
02-14-2012, 07:18 AM
I don't see a syntax error. What is the problem?

When I entered 6 for the elevation, column 3 started with 6 but the friction numbers started at the 0 elevation from the slave file.

darweesh8
02-14-2012, 09:59 PM
Mr. Hobs,

Try to move the code from the macro to the main code, it will not work!!
That is the problem, I know it's working but I like to know why it happened.

Kenneth Hobs
02-15-2012, 06:35 AM
Do you mean that there is a problem with running Macro7? By running the Main "macro", I think that you mean running CommandButton1_Click.

A problem, run-time error 1004, when running Macro7 after the CommandButton1_Click is that you did not unprotect the sheet as you did in the latter routine. Rather than doing all that unprotect and protect for each sheet, I recommend doing this sort of thing for each sheet:

Sheets("start").Protect UserInterfaceOnly:=True
This allows your code to change sheet data but not manual changes by the user. I normally do this in the Open event of the ThisWorkbook object but it should be done for each newly added sheet if your code will then modify the data.

darweesh8
02-16-2012, 05:26 AM
a million thanks Mr. Hobs, I got it.