Code:
--------------------
Private Sub CommandButton2_Click()
CommandButton2_Click:

'Dim Variables
Dim i As Integer
Dim Product As Integer
Dim NextRow As Integer

'''''''''''''''''''''''''''''''
'If B1 = blank then...
If Sheets(1).Range("B1").Value = "" Then
'Bring up a MsgBox and request cell entry
MsgBox "Please enter a Member ID into highlighted cell B1", vbOKOnly, "Enter Member ID"
'End the Sub
Exit Sub
'''''''''''''''''''''''''''''''

Else

'If A14 : A13 = blank then...
If Sheets(1).Range("A4").Value = "" And Sheets(1).Range("A5").Value = "" And Sheets(1).Range("A6").Value = "" And Sheets(1).Range("A7").Value = "" And Sheets(1).Range("A8").Value = "" And Sheets(1).Range("A9").Value = "" And Sheets(1).Range("A10").Value = "" And Sheets(1).Range("A11").Value = "" And Sheets(1).Range("A12").Value = "" And Sheets(1).Range("A13").Value = "" Then
'Bring up a MsgBox requesting a product to be selected
MsgBox "Please select a product to purchase in Sheet1", vbOKOnly, "Select a Product"
'End the Sub if true
Exit Sub

Else
'''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''
' *** INSERT INTO SHEET 3 *** '
'''''''''''''''''''''''''''''''
'Set Variables
Product = 4
'Set up Loop
Do While Product <= 13
'Set Variables
NextRow = LastCell(Sheet3).Row
i = NextRow
MsgBox i

'ACTUAL CODING TO INSERT PRODUCT
If Sheets(1).Range("A" & Product).Value < "" Then
Sheets(3).Range("A" & i).Value = i - 1
Sheets(3).Range("B" & i).Value = Sheets(1).Range("B1").Value
End If
'END OF INSERTION

'Set Variables before looping
Product = Product + 1
'Loop
Loop


End If
End If
End Sub

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function



--------------------


I currently have this code, but it isn't working as I desire.

I have 4 sheets, but this part of my VBA is only concerning two of
them: Sheet1 and Sheet3.

In Sheet1 I have a table where the user can enter up to 10 products to
purchase goods. The user enters a "MemberID" into cell "B1". The user
then enters up to 10 product names from cell "A4" up to cell "A13". I
want the user to press a command button and the data will be sent to
Sheet3....

Sheet3 is essentially a logging table, where a log of all the products
are kept. If a product is in cell "A4" in sheet1 then it will be put
into the next available cell in Sheet3 in column "C". If there isn't
one then it will check cell "A4" and put it into Sheet3 and so on...
However, I am not concerned about that yet, if this first problem is
fixed then that can be fixed easily too. In column "A" in Sheet3 I
have a PurchaseID, essentially a primary key. I want this command
button to find the next blank cell in column "A" Sheet3, and then
insert into it the next integer in the PurchaseID sequence. I have had
a stab at it, but I cannot get it to work - what is going on? I can
only seem to get it to put the PurchaseID for the first product (into
row 2 Sheet3)and that is it.

If you still don't understand then post and say so.


--
Mario_Party
------------------------------------------------------------------------
Mario_Party's Profile: http://www.excelforum.com/member.php...o&userid=30925
View this thread: http://www.excelforum.com/showthread...hreadid=508371