Lookup/Identify next number,Paste into cell [Solved/Closed]

Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
- - Latest reply: Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
- Mar 18, 2010 at 03:19 AM
Hello,I need help with a identifying the next number!

When pressing the Save button on sheet1-Form(Code below),lookup the last number that was used in sheet2-Database Column B(say 8) and put the next number (9) in Sheet1-Form Cell B3.

Option Explicit
Dim wsDatabase As Worksheet
Dim wsForm As Worksheet
Dim rNextCl As Range
Sub Save_Data()

'Set variables
Set wsDatabase = Sheet2
Set wsForm = Sheet1
'find the next empty row for data input
Set rNextCl = wsDatabase.Cells(65536, 1).End(xlUp).Offset(1, 0)

'enter the data using Offset to find next Column
'note use With....End With to let Excel that the following lines are working with rNextCl

With rNextCl
.Value = wsForm.Cells(2, 2).Value
.Offset(0, 1).Value = wsForm.Cells(3, 2).Value
.Offset(0, 2).Value = wsForm.Cells(4, 2).Value
.Offset(0, 3).Value = wsForm.Cells(5, 2).Value
.Offset(0, 4).Value = wsForm.Cells(6, 2).Value
.Offset(0, 5).Value = wsForm.Cells(7, 2).Value
.Offset(0, 6).Value = wsForm.Cells(8, 2).Value
.Offset(0, 7).Value = wsForm.Cells(9, 2).Value
End With

'confirm data transferred
MsgBox "Data transferred successfully to Database" & vbCrLf & "", vbInformation, "Data transfer"

'clear input cells, note use of Named Range
wsForm.Range("DataInput").ClearContents

'clear variables
Set wsDatabase = Nothing
Set wsForm = Nothing
Set rNextCl = Nothing

End Sub
TX>>>>G
See more 

2 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753
0
Thank you
Where in column B will that value B ? Are you saying that you want to look for the value in the last used cell in B column and then increment it by 1 ? If that is the case

lastValue= sheets("Sheet1).cells(rows.count, "B").end(xluP).row
lastvalue = sheets("Sheet1).cells(lastvalue, "B").value


Just a suggestion

Set rNextCl = wsDatabase.Cells(65536, 1).End(xlUp).Offset(1, 0)

this is good for 2003, but you would be better off with

Set rNextCl = wsDatabase.Cells(rows.count, 1).End(xlUp).Offset(1, 0)
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
0
Thank you
TX rivisa1

I've used =MAX(OFFSET($B$2,0,0,MAX(IF(ISNUMBER(B:B),ROW(B:B)-3,0)),1)))+3 CTRL+SHIFT+ENTER but will also try out yours.

TX G