How can I summarize what is in more cells in a single one

[balan_radu2003]

Hello,

So I have a table with some Lines and some Columns. I am filling this
Lines and Columns with the help of a form that is opening when I am
pressing a Button. Every time I press the Button "ADD" on the Form new
lines with the info from the form are inserted in the table. The
Header Columns of the Table is: Pizza, Type, extra topping, size,
Sauce. Price. What I want to do is a summarize on sheet 2. So that I
will have a copy of this table from sheet1 with more columns in just 2
columns on sheet 2. So "Pizza, Type, extra topping, size, Sauce"
should be copied into the first Column on sheet 2 and the Price should
be copied in the second column in the right of column 1 on sheet2. The
code should automatically check all the lines of the table and copy
all to sheet2 not only the first line of the table.

Example:

Column1 Column2 Column3 Column4 Column5 Column6
Pizza, ----- Type,-- extra topping, size, Sauce ---- price

quatro ----- stagioni --- cheese ---- big ---- no sauce ---- 20$


Should get on sheet 2:

--------------Column1 ----------------------------------- Column 2

"quatro big + stagioni + big + no sauce" ------ 20$



The current code is made like this for the form:

Private Sub cmdADD_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboPizza.Value
ActiveCell.Offset(0, 1) = txtType.Value
ActiveCell.Offset(0, 2) = cboExtratopping.Value

etc etc....

I would like to have something like this with a referential cell like
A3 on Sheet2. like I have the A3 cell for the first sheet.

Thank you very much

 

[Joel]

Private Sub cmdADD_Click()
BoxNames = Array("cboPizza", "txtType", "cboExtratopping")

With ActiveWorkbook.Sheets("Sheet1")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
Colcount = 1
For i = LBound(BoxNames) To UBound(BoxNames)

Do While .Cells(NewRow, Colcount) <> ""
Colcount = Colcount + 1
Loop
.Cells(NewRow, Colcount) = Controls(BoxNames(i)).Value
Next i
End With
End Sub

 

[balan_radu2003]

Private Sub cmdADD_Click()
    BoxNames = Array("cboPizza", "txtType", "cboExtratopping")

    With ActiveWorkbook.Sheets("Sheet1")
       Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
       NewRow = Lastrow + 1
       Colcount = 1
       For i = LBound(BoxNames) To UBound(BoxNames)

          Do While .Cells(NewRow, Colcount) <> ""
             Colcount = Colcount + 1
          Loop
                .Cells(NewRow, Colcount) = Controls(BoxNames(i)).Value
       Next i
     End With
End Sub

That is not exactly what I wanted.

I want to have what is in A3, B3, C3, D3, E3, F3, G3, H3, I3 from
Sheet 1 into Sheet 2 all in A3 and what is in J3 from Sheet1 in B3 on
Sheet2. And it should apply for every row that follows under A3... so
for A4 to J4, for A5 to J5 and so on... but only if these cells are
filled in with information...

 

[balan_radu2003]

So I found some way to do it as a formula

I am inserting the formula on sheet2 in some Cell I want:

=TRIM(CONCATENATE(Sheet1!A4," ",Sheet1B4," ",Sheet1C4," ",Sheet1D4,"
",Sheet1E4))


PROBLEM:

On sheet1 I have a delete Button for entire rows so that I can delete
a Pizza if I entered something wrong into the list. And when I am
deleting the row, on sheet 2 It appears: #REF! #REF! #REF! #REF! -
because I deleted the row. What I want, is that this does not happen
on sheet2. It should notice that I deleted a row and just take the
next row.

Is there a way to do that in a macro? or do I need another formula?

 

[Joel]

Private Sub cmdADD_Click()
BoxNames = Array("cboPizza", "txtType", "cboExtratopping")

With ActiveWorkbook.Sheets("Sheet1")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
Colcount = 1
Do While .Cells(NewRow, Colcount) <> ""
Colcount = Colcount + 1
Loop
PizzaOrder = ""
For i = LBound(BoxNames) To UBound(BoxNames)

if PizzaOrder = "" then
PizzaOrder = Controls(BoxNames(i)).Value
else
PizzaOrder = PizzaOrder & " " & Controls(BoxNames(i)).Value
Next i
.cells(NewRow, ColCount) = PizzaOrder
End With
End Sub

 

[balan_radu2003]

 Private Sub cmdADD_Click()
     BoxNames = Array("cboPizza", "txtType", "cboExtratopping")

     With ActiveWorkbook.Sheets("Sheet1")
        Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        NewRow = Lastrow + 1
        Colcount = 1
        Do While .Cells(NewRow, Colcount) <> ""
           Colcount = Colcount + 1
        Loop
        PizzaOrder = ""        
        For i = LBound(BoxNames) To UBound(BoxNames)

           if PizzaOrder = "" then
              PizzaOrder = Controls(BoxNames(i)).Value
           else
              PizzaOrder = PizzaOrder & " " & Controls(BoxNames(i)).Value
        Next i
        .cells(NewRow, ColCount) = PizzaOrder
      End With
 End Sub

This code seems not to work.

Message: "Next without For"

 

[Joel]

Th eonly way you would get the error message is if you didn't copy the code
exactly as posted or you modified the code. Post your lasted code and I will
look at it.