Editing a macro

D

Dtmos01

Looking for information on how to edit a macro. Using a macro to enter data
into a spreadsheet. The number of entries is never the same. How do you
tell Excel to stop entering data after the last entry? I currently have to
enter a value such as Range("K4:K20000"), but I never have 20000 entries, it
will fill the remaining cells with #VALUE, I need the macro to stop after the
last entry. Any ideas?
 
S

smw226 via OfficeKB.com

Hi,

Without seeing your code it is quite hard to give a precise solution.

Please can you post your code and I will try to help

Thanks,

Simon

Looking for information on how to edit a macro. Using a macro to enter data
into a spreadsheet. The number of entries is never the same. How do you
tell Excel to stop entering data after the last entry? I currently have to
enter a value such as Range("K4:K20000"), but I never have 20000 entries, it
will fill the remaining cells with #VALUE, I need the macro to stop after the
last entry. Any ideas?
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
 
M

Mike

You need to determine the last used row and you can do this with this

lastrow = Range("A65536").End(xlUp).Row
Set myrange = Range("K4", lastrow)

This checks Column A so edit to suit.

Mike

Mike
 
D

Dtmos01

Sub Macro15()
'
' Macro15 Macro
' Macro recorded 10/18/2006
'

'
Range("K1:K2").Select
Selection.Copy
Windows("AGJ603f.csv").Activate
Range("K3").Select
ActiveSheet.Paste
Range("K4").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=LOOKUP(INT(LEFT(RC[-7],6)),'[errors.xls]Model
List'!R1C1:R81C1,'[errors.xls]Model List'!R1C2:R81C2)"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K20000"), Type:=xlFillDefault
Range("K4:K20000").Select
Windows("errors.xls").Activate
End Sub


smw226 via OfficeKB.com said:
Hi,

Without seeing your code it is quite hard to give a precise solution.

Please can you post your code and I will try to help

Thanks,

Simon

Looking for information on how to edit a macro. Using a macro to enter data
into a spreadsheet. The number of entries is never the same. How do you
tell Excel to stop entering data after the last entry? I currently have to
enter a value such as Range("K4:K20000"), but I never have 20000 entries, it
will fill the remaining cells with #VALUE, I need the macro to stop after the
last entry. Any ideas?
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
 
B

Bob Phillips

Dim LastCell As Range
Range("K1:K2").Copy Range("K3")
Workbooks("AGJ603f.csv").Range ("K4")
.FormulaR1C1 = "yourformula"
Set LastCell = .End(xlDown)
.AutoFill Destination:=.Resize(LastCell.Row - 3), _
Type:=xlFillDefault
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Dtmos01 said:
Sub Macro15()
'
' Macro15 Macro
' Macro recorded 10/18/2006
'

'
Range("K1:K2").Select
Selection.Copy
Windows("AGJ603f.csv").Activate
Range("K3").Select
ActiveSheet.Paste
Range("K4").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=LOOKUP(INT(LEFT(RC[-7],6)),'[errors.xls]Model
List'!R1C1:R81C1,'[errors.xls]Model List'!R1C2:R81C2)"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K20000"), Type:=xlFillDefault
Range("K4:K20000").Select
Windows("errors.xls").Activate
End Sub


smw226 via OfficeKB.com said:
Hi,

Without seeing your code it is quite hard to give a precise solution.

Please can you post your code and I will try to help

Thanks,

Simon

Looking for information on how to edit a macro. Using a macro to enter
data
into a spreadsheet. The number of entries is never the same. How do
you
tell Excel to stop entering data after the last entry? I currently have
to
enter a value such as Range("K4:K20000"), but I never have 20000
entries, it
will fill the remaining cells with #VALUE, I need the macro to stop
after the
last entry. Any ideas?
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
Click to expand...
 
D

Dtmos01

How do I apply that section to my current macro?

Bob Phillips said:
Dim LastCell As Range
Range("K1:K2").Copy Range("K3")
Workbooks("AGJ603f.csv").Range ("K4")
.FormulaR1C1 = "yourformula"
Set LastCell = .End(xlDown)
.AutoFill Destination:=.Resize(LastCell.Row - 3), _
Type:=xlFillDefault
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Dtmos01 said:
Sub Macro15()
'
' Macro15 Macro
' Macro recorded 10/18/2006
'

'
Range("K1:K2").Select
Selection.Copy
Windows("AGJ603f.csv").Activate
Range("K3").Select
ActiveSheet.Paste
Range("K4").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=LOOKUP(INT(LEFT(RC[-7],6)),'[errors.xls]Model
List'!R1C1:R81C1,'[errors.xls]Model List'!R1C2:R81C2)"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K20000"), Type:=xlFillDefault
Range("K4:K20000").Select
Windows("errors.xls").Activate
End Sub


smw226 via OfficeKB.com said:
Hi,

Without seeing your code it is quite hard to give a precise solution.

Please can you post your code and I will try to help

Thanks,

Simon


Dtmos01 wrote:
Looking for information on how to edit a macro. Using a macro to enter
data
into a spreadsheet. The number of entries is never the same. How do
you
tell Excel to stop entering data after the last entry? I currently have
to
enter a value such as Range("K4:K20000"), but I never have 20000
entries, it
will fill the remaining cells with #VALUE, I need the macro to stop
after the
last entry. Any ideas?

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Sub Macro15()
Dim LastCell As Range
Range("K1:K2").Copy Range("K3")
Workbooks("AGJ603f.csv").Range ("K4")
.FormulaR1C1 = "yourformula"
Set LastCell = .End(xlDown)
.AutoFill Destination:=.Resize(LastCell.Row - 3), _
Type:=xlFillDefault
End With
End SUb

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top