Lookup/Identify next number,Paste into cell

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

2 responses

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