Lookup/Identify next number,Paste into cell

Solved/Closed
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012 - 16 Mar 2010 à 06:50
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012 - 18 Mar 2010 à 03:19
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

2 responses

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Mar 2010 à 10:10
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)
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012
18 Mar 2010 à 03:19
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