Multiple products under one ref number (VBA)

[mckaya100]

Hi,

I have set up a userform that allows users to select products and record them in a data sheet. There are multiple product cats and you can select multiple products at a time.

e.g.

<TABLE style="WIDTH: 207pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=275 border=0><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c5be97" width=84 height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc" width=77>7</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=114>apple</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c5be97" height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc">8</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">banana</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #c5be97" height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc">9</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">pear</TD></TR></TBODY></TABLE>

First row conatins cust number
second row contains amount
third row conatins product

1. how can I get it to leave cust number out of the 2nd and 3rd rows when it is the same order.

2. how can I get it to realise that there is an entry on the row and move to the next line?

Please find code that I am using now below....

Thanks for any help,

Adam

Private Sub CommandButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("entries")


'find first empty row in database

irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a cust number
If Me.TextBox1.Value = "" Then
MsgBox "Please enter a Customer number"
End If



ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 3).Value = Me.ComboBox1.Value
ws.Cells(irow, 5).Value = Me.ComboBox2.Value
ws.Cells(irow, 7).Value = Me.ComboBox3.Value
ws.Cells(irow, 9).Value = Me.ComboBox4.Value
ws.Cells(irow, 11).Value = Me.ComboBox5.Value
ws.Cells(irow, 16).Value = Me.ComboBox21.Value
ws.Cells(irow, 18).Value = Me.ComboBox23.Value
ws.Cells(irow, 20).Value = Me.ComboBox24.Value



'find first empty row in database

irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 3).Value = Me.ComboBox16.Value
ws.Cells(irow, 5).Value = Me.ComboBox17.Value
ws.Cells(irow, 7).Value = Me.ComboBox18.Value
ws.Cells(irow, 9).Value = Me.ComboBox19.Value
ws.Cells(irow, 11).Value = Me.ComboBox20.Value
ws.Cells(irow, 16).Value = Me.ComboBox26.Value
ws.Cells(irow, 18).Value = Me.ComboBox28.Value
ws.Cells(irow, 20).Value = Me.ComboBox29.Value

'find first empty row in database

irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 16).Value = Me.ComboBox30.Value
ws.Cells(irow, 18).Value = Me.ComboBox31.Value
ws.Cells(irow, 20).Value = Me.ComboBox32.Value
ws.Cells(irow, 18).Value = Me.ComboBox34.Value
ws.Cells(irow, 20).Value = Me.ComboBox35.Value
ws.Cells(irow, 22).Value = Me.ComboBox36.Value



Me.TextBox1.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox21.Value = ""
Me.ComboBox23.Value = ""
Me.ComboBox24.Value = ""
Me.ComboBox16.Value = ""
Me.ComboBox17.Value = ""
Me.ComboBox18.Value = ""
Me.ComboBox19.Value = ""
Me.ComboBox20.Value = ""

Unload Me

End Sub