Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 83
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
generate different number Paul Excel Worksheet Functions 2 October 6th 08 08:39 AM
Serial number generate Tony Excel Worksheet Functions 4 October 20th 07 04:59 AM
generate a random number and use if function to generate new data Dogdoc1142 Excel Worksheet Functions 4 April 26th 06 03:44 AM
how can text be set up to generate a number? jwmaes New Users to Excel 3 January 25th 05 02:32 PM
generate serial number john douglass Excel Worksheet Functions 0 November 5th 04 11:49 PM


All times are GMT +1. The time now is 12:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"