Create Invoice From Flat Data Range

[johnreid7477]

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 ...

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 (http://www.mrexcel.com/forum/excel-questions/705723-userform-auto-copy-invoice-date-if-item-fills-rows.html)

[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.

[johnreid7477]

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.


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.
http://i44.tinypic.com/9abpg6.jpg

2. Result Sheet "Data" become like this.
http://i44.tinypic.com/351cr2b.jpg

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.
http://i42.tinypic.com/eagrih.jpg


Thanks - John

[Excel Fox]

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

[johnreid7477]

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