| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled
cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
Hi,
Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
Hi Mike. I really need a macro.
I am using some macros and i need to use this macro along with the other ones. But i really don't know how to make this work. Can you help me with a macro? Thanks! "Mike H" wrote: Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
A macro it is then
Sub Sonic() Dim LastRow As Long Set SrcSht = Sheets("Sheet2") Set DstSht = Sheets("Sheet1") LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi Mike. I really need a macro. I am using some macros and i need to use this macro along with the other ones. But i really don't know how to make this work. Can you help me with a macro? Thanks! "Mike H" wrote: Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
On Tue, 9 Mar 2010 13:26:01 -0800, Mike H
wrote: A macro it is then Sub Sonic() Dim LastRow As Long Set SrcSht = Sheets("Sheet2") Set DstSht = Sheets("Sheet1") LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) End Sub +1 should be added at the end of the last statement like this: DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) + 1 Lars-Åke |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
Hi Mike, your first formula gives me an error, and the macro retrieves the
last number. I need to display last number +1. Am i doing something wrong? Thanks! "Mike H" wrote: A macro it is then Sub Sonic() Dim LastRow As Long Set SrcSht = Sheets("Sheet2") Set DstSht = Sheets("Sheet1") LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi Mike. I really need a macro. I am using some macros and i need to use this macro along with the other ones. But i really don't know how to make this work. Can you help me with a macro? Thanks! "Mike H" wrote: Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to generate next number
It's working.
Thanks! "puiuluipui" wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro to increase the value of one cell and place the new valueinto different cell
On Wednesday, September 22, 2010 at 1:27:17 AM UTC+6, Gord Dibben wrote:
Sub addone() With ActiveSheet.Range("A1") .Value = .Value + 1 .Offset(0, 1).Value = .Value End With End Sub Alternative.................use a Spinner from the Forms Toolbar. Gord Dibben MS Excel MVP On Mon, 20 Sep 2010 13:54:48 GMT, Bon Mitchell wrote: can you tell me how to increase value in cell A1 in the same sheet and increase it by 1 then place the new value into A2? thanks On Tuesday, March 09, 2010 3:58 PM puiuluipui wrote: Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! On Tuesday, March 09, 2010 4:08 PM Mike H wrote: Hi, Yuo can have a macro if you want but you do not need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: On Tuesday, March 09, 2010 4:17 PM puiuluipui wrote: Hi Mike. I really need a macro. I am using some macros and i need to use this macro along with the other ones. But i really do not know how to make this work. Can you help me with a macro? Thanks! "Mike H" wrote: On Tuesday, March 09, 2010 4:26 PM Mike H wrote: A macro it is then Sub Sonic() Dim LastRow As Long Set SrcSht = Sheets("Sheet2") Set DstSht = Sheets("Sheet1") LastRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "puiuluipui" wrote: On Tuesday, March 09, 2010 4:41 PM Lars-?ke Aspelin wrote: +1 should be added at the end of the last statement like this: DstSht.Range("A1").Value = SrcSht.Range("A" & LastRow) + 1 Lars-?ke On Tuesday, March 09, 2010 4:50 PM puiuluipui wrote: Hi Mike, your first formula gives me an error, and the macro retrieves the last number. I need to display last number +1. Am i doing something wrong? Thanks! "Mike H" wrote: On Tuesday, March 09, 2010 5:30 PM puiuluipui wrote: it is working. Thanks! "puiuluipui" wrote: On Monday, September 20, 2010 9:52 AM Bon Mitchell wrote: can you tell me how to do that if you want to increase the value inthe same sheet for cells. for example if A1 =1, then the macro will look into A1 and increase it to 2 and place the value into A2. Thanks, Submitted via EggHeadCafe - Software Developer Portal of Choice MongoDb vs SQL Server Basic Speed Tests http://www.eggheadcafe.com/tutorials...eed-tests.aspx Here is a video link. I hope this will help you to increase the number easily : https://youtu.be/Hz7Ye-Rm6Po |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| generate different number | Excel Worksheet Functions | |||
| Serial number generate | Excel Worksheet Functions | |||
| generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
| how can text be set up to generate a number? | New Users to Excel | |||
| generate serial number | Excel Worksheet Functions | |||
Linear Mode
