Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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.a...excel/200704/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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 "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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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" wrote: 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.a...excel/200704/1 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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" wrote in message ... 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" wrote: 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.a...excel/200704/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
How do I apply that section to my current macro?
"Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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.a...excel/200704/1 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
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) "Dtmos01" wrote in message ... How do I apply that section to my current macro? "Bob Phillips" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a Macro | Excel Discussion (Misc queries) | |||
Formula Editing Macro? | Excel Discussion (Misc queries) | |||
Editing Macro | Excel Discussion (Misc queries) | |||
Editing a simple macro | Excel Worksheet Functions | |||
in cell editing macro | Excel Discussion (Misc queries) |