Create Invoice From Flat Data Range

**johnreid7477** · 05-31-2013, 07:58 PM

Hi Excelfox,

My first thread sorry if make mistake or confusing.

My question 1 : If input 4 "item" (4 cells) in Description automatic "invoice & date" will be auto copy / create same 4 in cells.
My question 2 : How input next item, type, rate in next cells (because my first VBA only single cells), i don't understand to combine ...

================================================== ==
Example Userform VBA
================================================== ==

Invoice : 0001/SM/06/13
Date : 01 June 2013
Description :
Item	Type	Rate
ITEM1	HOT	$ 1
ITEM2	HOT	$ 1
ITEM3	HOT	$ 1
ITEM4	SPECIAL	$ 5

================================================== ==
================================================== ==

Example Result code if working

	B	C	L	K	M
1	Invoice	Date	Item	Type	Rate
2	0001/SM/06/13	01 June 2013	ITEM1	HOT	$ 1
3	0001/SM/06/13	01 June 2013	ITEM2	HOT	$ 1
4	0001/SM/06/13	01 June 2013	ITEM3	HOT	$ 1
5	0001/SM/06/13	01 June 2013	ITEM4	SPECIAL	$ 5

My single cell code. I'm not input (item2,item3,item4, type2,type3,typ4, rate2,rate,3,rate4) because confusing must use cells ..or Ws.... and ...

Code:

Private Sub buttonnew_Click()
Dim RowCount As Long
Dim benefits, total As Single
'copy the data to the database
RowCount = Worksheets("Database").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Database").Range("B1")
    .Offset(RowCount, 0) = Me.invoice.Value
    .Offset(RowCount, 1) = Me.date.Value
    .Offset(RowCount, 11) = Me.item1.Value
    .Offset(RowCount, 10) = Me.type.Value
    .Offset(RowCount, 12) = Me.rate.Value
End With
End Sub

Thanks - John

Note :
1. My Example and attachment (original file) it's different looking.
2. Other post but no answer mrexcel

**Excel Fox** · 05-31-2013, 09:48 PM

johnreid, can you clarify exactly what you want to achieve? The example you mentioned above is not there in the attachment. The output example you've posted above can be made, but you have to share exactly how does the input cells contain the value. I don't think the attachment you have here adds any value to what you are purposing to do.

**johnreid7477** · 06-01-2013, 04:43 PM

Originally Posted by Excel Fox

johnreid, can you clarify exactly what you want to achieve? The example you mentioned above is not there in the attachment. The output example you've posted above can be made, but you have to share exactly how does the input cells contain the value. I don't think the attachment you have here adds any value to what you are purposing to do.

Hi Excelfox,

Ignore my first post sorry if make confuse, i give step by step to tell my problem excel.

Code:

    Private Sub buttonnew_Click()
    Dim RowCount As Long
    Dim benefits, total As Single
    

    RowCount = Worksheets("Data").Range("B1").CurrentRegion.Rows.Count    ' i think it's code wrong only read row
    With Worksheets("Data").Range("B1")
        .Offset(RowCount, 0) = Me.noinvoice.Value    'input in row B column 2
        .Offset(RowCount, 1) = Me.Tanggal.Value      'input in row C column 2
        .Offset(RowCount, 2) = Me.Costumers.Value    'input in row D column 2
        .Offset(RowCount, 20) = Me.Note.Value        'input in row V column 2
        .Offset(RowCount, 22) = Me.ttd.Value         'input in row X column 2
        .Offset(RowCount, 21) = Me.initial.Value     'input in row W column 2
        
        .Offset(RowCount, 10) = Me.item1.Value    'input in row L column 2
        .Offset(RowCount, 10) = Me.item2.Value    'input in row L column 3
        .Offset(RowCount, 10) = Me.item3.Value    'input in row L column 4
        .Offset(RowCount, 10) = Me.item4.Value    'input in row L column 5
        .Offset(RowCount, 10) = Me.item5.Value    'input in row L column 6
        .Offset(RowCount, 10) = Me.item6.Value    'input in row L column 7
        .Offset(RowCount, 10) = Me.item7.Value    'input in row L column 8
        .Offset(RowCount, 10) = Me.item8.Value    'input in row L column 9
        .Offset(RowCount, 10) = Me.item9.Value    'input in row L column 10
        .Offset(RowCount, 10) = Me.item10.Value   'input in row L column 11
        .Offset(RowCount, 9) = Me.type1.Value    'input in row K column 2
        .Offset(RowCount, 9) = Me.type2.Value    'input in row K column 3
        .Offset(RowCount, 9) = Me.type3.Value    'input in row K column 4
        .Offset(RowCount, 9) = Me.type4.Value    'input in row K column 5
        .Offset(RowCount, 9) = Me.type5.Value    'input in row K column 6
        .Offset(RowCount, 9) = Me.type6.Value    'input in row K column 7
        .Offset(RowCount, 9) = Me.type7.Value    'input in row K column 8
        .Offset(RowCount, 9) = Me.type8.Value    'input in row K column 9
        .Offset(RowCount, 9) = Me.type9.Value    'input in row K column 10
        .Offset(RowCount, 9) = Me.type10.Value   'input in row K column 11
        .Offset(RowCount, 11) = Me.harga1.Value    'input in row M column 2
        .Offset(RowCount, 11) = Me.harga2.Value    'input in row M column 3
        .Offset(RowCount, 11) = Me.harga3.Value    'input in row M column 4
        .Offset(RowCount, 11) = Me.harga4.Value    'input in row M column 5
        .Offset(RowCount, 11) = Me.harga5.Value    'input in row M column 6
        .Offset(RowCount, 11) = Me.harga6.Value    'input in row M column 7
        .Offset(RowCount, 11) = Me.harga7.Value    'input in row M column 8
        .Offset(RowCount, 11) = Me.harga8.Value    'input in row M column 9
        .Offset(RowCount, 11) = Me.harga9.Value    'input in row M column 10
        .Offset(RowCount, 11) = Me.harga10.Value   'input in row M column 11
        .Offset(RowCount, 12) = Me.unit1.Value   'input in row N column 2
        .Offset(RowCount, 12) = Me.unit2.Value   'input in row N column 3
        .Offset(RowCount, 12) = Me.unit3.Value   'input in row N column 4
        .Offset(RowCount, 12) = Me.unit4.Value   'input in row N column 5
        .Offset(RowCount, 12) = Me.unit5.Value   'input in row N column 6
        .Offset(RowCount, 12) = Me.unit6.Value   'input in row N column 7
        .Offset(RowCount, 12) = Me.unit7.Value   'input in row N column 8
        .Offset(RowCount, 12) = Me.unit8.Value   'input in row N column 9
        .Offset(RowCount, 12) = Me.unit9.Value   'input in row N column 10
        .Offset(RowCount, 12) = Me.unit10.Value  'input in row N column 11
        .Offset(RowCount, 14) = Me.disc1.Value   'input in row P column 2
        .Offset(RowCount, 14) = Me.disc2.Value   'input in row P column 3
        .Offset(RowCount, 14) = Me.disc3.Value   'input in row P column 4
        .Offset(RowCount, 14) = Me.disc4.Value   'input in row P column 5
        .Offset(RowCount, 14) = Me.disc5.Value   'input in row P column 6
        .Offset(RowCount, 14) = Me.disc6.Value   'input in row P column 7
        .Offset(RowCount, 14) = Me.disc7.Value   'input in row P column 8
        .Offset(RowCount, 14) = Me.disc8.Value   'input in row P column 9
        .Offset(RowCount, 14) = Me.disc9.Value   'input in row P column 10
        .Offset(RowCount, 14) = Me.disc10.Value  'input in row P column 11
    End With

    End Sub

Explain :
'My Goal : If i'm create one invoice, my value (item, type, unit, harga until 10 column) do it complete in my sheet "Data".
'My Problem : My VBA code in upper just copy paste from basic VBA only for row single column, need fix and need opinion (in case i don't understand about offset value)
'Reward : I'm just have $10 paypal please help us.

'My Tutorial in below :

1. Example i do my job in userform like this.

2. Result Sheet "Data" become like this.

3. If i do next invoice example 0002/SM/06/13 and same description (item, type, unit, harga until 10 column) will do it in Column 2 and my first invoice 0001/SM/06/13 auto down.

Thanks - John

**Excel Fox** · 08-18-2013, 05:09 PM

Missed this one john reid, can you confirm if you are still looking for a solution.

**johnreid7477** · 08-19-2013, 11:41 AM

Originally Posted by Excel Fox

Missed this one john reid, can you confirm if you are still looking for a solution.

Thanks all solved

Private Sub buttonclose_Click()
Unload Me
End Sub

Private Sub buttonnew_Click()
Dim Cancel As Boolean
Dim keyCol As Range
Dim i As Long
Set keyCol = ThisWorkbook.Sheets("Data").Columns(2)

If Me.noinvoice.Value = "" Then
MsgBox "Please enter a name", vbExclamation, "Invoice"
Cancel = True
End If
If Me.Tanggal.Value = "" Then
MsgBox "Please enter a item", vbExclamation, "Invoice"
Cancel = True
End If
If Me.Costumers1.Value = "" Then
MsgBox "Please enter a costumers name", vbExclamation, "Invoice"
Cancel = True
End If
If Me.ttd.Value = "" Then
MsgBox "Please enter a ttd", vbExclamation, "Invoice"
Cancel = True
End If
If Me.initial.Value = "" Then
MsgBox "Please enter a initial", vbExclamation, "Invoice"
Cancel = True
End If

If Cancel = True Then
'Do nothing
Else

For i = 1 To 10
If Me.Controls("item" & i).Text <> vbNullString Then
With keyCol.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Columns("M").Value = Me.Controls("item" & i).Text
.Columns("O").Value = Me.Controls("unit" & i).Value
.Columns("L").Value = Me.Controls("type" & i).Text
.Columns("N").Value = Me.Controls("harga" & i).Value
.Columns("Q").Value = disc1.Value / 100
.Columns("Z").Value = Me.PajakCode.Value
.Columns("T") = "10%"
.Columns("B") = noinvoice
.Columns("C").Value = Me.Tanggal.Value
.Columns("D").Value = Costumers1.Value
.Columns("W").Value = Note
.Columns("Y").Value = ttd
.Columns("X").Value = initial
End With
End If
Next i

Range("AA1").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Unload Me
End If

End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Costumers")

With noinvoice
.Value = Format(Val(Cells(Rows.Count, "B").End(xlUp)) + 1, "0000") & "/" & Format(Month(Date), "00") & "/BMO" & "/" & Right(Year(Date), 2)
.Enabled = False
End With

Me.Tanggal.Value = Format(Date, "short Date")

Me.initial.Value = Range("LoggedInAs").Value

For Each cCostumers1 In ws.Range("AlamatCostumers")
With Me.Costumers1
.AddItem cCostumers1.Value
.List(.ListCount - 1, 1) = cCostumers1.Offset(0, 4).Value
End With
Next cCostumers1

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the close button!!"
End If
End Sub