How to add Loop

[HSAR]

Created a VBA code to putt the relevant details into invoice but do not know how to add a loop to run this till used range.

Sub Sheetdev()

With Sheet3
.Range("C1").Value = "Account:"
.Range("C3").Value = "Head:"
.Range("C5").Value = "Duration:"
.Range("C7").Value = "Party Name:"
.Range("C9").Value = "Type:"
.Range("C11").Value = "Location"
.Range("C14").Value = "Tax Rate:"
.Range("C15").Value = "Qty:"
.Range("C16").Value = "Tax:"
.Range("C22").Value = "Total Amount:"
.Range("C23").Value = "Remarks:"
.Range("H3").Value = "Voucher #:"
.Range("H5").Value = "Date:"
.Range("H7").Value = "Invoice #:"
.Range("H9").Value = "Billing Date:"

'Loop

.Range("E1").Value = Sheet1.Range("C2").Value
.Range("E3").Value = Sheet1.Range("D2").Value
.Range("E5").Value = Sheet1.Range("J2").Value
.Range("E7").Value = Sheet1.Range("F2").Value
.Range("E9").Value = Sheet1.Range("H2").Value
.Range("E11").Value = Sheet1.Range("G2").Value
.Range("E14").Value = Sheet1.Range("K2").Value
.Range("E15").Value = Sheet1.Range("L2").Value
.Range("E16").Value = Sheet1.Range("N2").Value
.Range("J22").Value = Sheet1.Range("O2").Value
.Range("E23").Value = Sheet1.Range("P2").Value
.Range("J3").Value = Sheet1.Range("B2").Value
.Range("J5").Value = Sheet1.Range("E2").Value
.Range("J7").Value = Sheet1.Range("I2").Value
.Range("J9").Value = Sheet1.Range("Q2").Value

End With

End Sub

 

[DanteAmor]

How about:

Sub Sheetdev()
  Dim ar1 As Variant, ar2 As Variant
  Dim i As Long, j As Long
  
  ar1 = Array("E1", "E3", "E5", "E7", "E9", "E11", "E14", "E15", "E16", "J22", "E23", "J3", "J5", "J7", "J9")
  ar2 = Array("C", "D", "J", "F", "H", "G", "K", "L", "N", "O", "P", "B", "E", "I", "Q")
  
  With Sheet3
    .Range("C1").Value = "Account:"
    .Range("C3").Value = "Head:"
    .Range("C5").Value = "Duration:"
    .Range("C7").Value = "Party Name:"
    .Range("C9").Value = "Type:"
    .Range("C11").Value = "Location"
    .Range("C14").Value = "Tax Rate:"
    .Range("C15").Value = "Qty:"
    .Range("C16").Value = "Tax:"
    .Range("C22").Value = "Total Amount:"
    .Range("C23").Value = "Remarks:"
    .Range("H3").Value = "Voucher #:"
    .Range("H5").Value = "Date:"
    .Range("H7").Value = "Invoice #:"
    .Range("H9").Value = "Billing Date:"
    
    'Loop
    For i = 2 To Sheet1.Range("C" & Rows.Count).End(3).Row
      For j = 0 To UBound(ar1)
        .Range(ar1(j)).Value = Sheet1.Range(ar2(j) & i).Value
      Next
      '
      'Here what you want to do with the invoice, print, save as pdf or excel, etc.
      '
    Next
  End With
End Sub

 

[Dermot]

Here is a very flexible solution, which allows anyone to make changes in future (as I'm sure will be needed) to anything, without changing any code.

1. Add a row at the top, above the table of invoice details. Create a counter cell in that row. Put 1 in it for now.
2. Set up Sheet 3 with all the headings you need (there's no need to have the code do it, when you are going to keep using the same sheet for all your invoices).
3. Insert invoice details on Sheet 3 using formulae, with the counter cell as an offset.
4. Get the code to simply put numbers in sequence in the counter cell, 1,2,3 and this will load invoices in Sheet 3

The example below shows how to set up the formulae

Test it by putting numbers in the counter cell and checking that the correct invoice details appear on Sheet 3

Now the code to run through the whole list

Sub ProduceInvoices()
  Dim i
  i = 1
  'loop as Long as there are more invoices to produce
  Do While Sheets("Sheet1").Cells(4, 1).Offset(i, 0) <> ""
    Cells(2, 3) = i
    Calculate
 
    'insert code to produce your invoice here
 
    i = i + 1
  Loop
End Sub

Now users can change things on the sheets, and the code will still work. (If they are likely to move headings or the location of the counter, you should give them range names and use those names in the code).

 

[HSAR]

Sir thank you so much that you have created this code.

But i was looking for single invoice code i mean, Currently using this via spin button and vlookup if i press the lower spin button then invoice # 0234 will be appear i wanted to learn this process via VBA.

If i press upper spin button then it will show 0232 invoice.

I understand that it is my mistake that i cannot convey proper message before apology.

 

[HSAR]

I also wants to add 2 button with name Next and Back for invoices appearing.