ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   macro to generate next number (https://www.excelbanter.com/excel-discussion-misc-queries/258457-macro-generate-next-number.html)

puiuluipui March 9th 10 08:58 PM

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!

Mike H March 9th 10 09:08 PM

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!


puiuluipui March 9th 10 09:17 PM

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!


Mike H March 9th 10 09:26 PM

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!


Lars-Åke Aspelin[_4_] March 9th 10 09:41 PM

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

puiuluipui March 9th 10 09:50 PM

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!


puiuluipui March 9th 10 10:30 PM

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!


ituTech March 17th 21 05:15 AM

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


All times are GMT +1. The time now is 01:32 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com