Help needed on loop

[johncassell]

Hello, I am struggling to find a solution to the following problem an
would really appreciate any help.

On Sheet1 I have a list of jobs numbers in column A, column B is th
customer, column C is the date, column D is the price, column E is th
invoice number.

I have a loop which looks though column B and basically says i
customer = "United" then put an invoice number in column E.

This works fine but at the same time this loop is running I would lik
it to populate the Invoice Sheet.

So, for example, it has verified that job number 1 is United so has pu
invoice number 100 in column D. It would then need to go into th
Invoice Sheet and put Job Number 1 in cell A1, the date for job 1 i
B1, the price for job 1 in C1.

The next time the loop goes round it would need to put the invoic
number in column D again but when it goes to the invoice sheet it wil
realise that there is already something in A1 so it will put it in A2.

The code for my loop is
--------------------------------
Sub InvoiceUnitedJobs()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("B:B")
If cell.Value = "United" Then
cell.Offset(0, 3).Value = 1
Else
End If
Next
End Sub
-------------------------------

I know this explanation has been very long-winded but would appreciat
any guidance.
Thanks

John

 

[aidan.heritage]

Various solutions - though all with offset! You can EITHER keep a
counter variable going


dim countervar as long
countervar=0
If cell.Value = "United" Then
cell.Offset(0, 3).Value = 1
sheets("Invoice
Sheet").range("d1").offset(countervar,0).value=whatever
countervar=countervar+1
etc etc etc

which is fine if you always start at D1. Alternatively, you can use
the COUNTA function to get the number you need to offset by (counta
would return the number of cells with data - as long as you don't have
blanks you can then determine from this the next blank cell).

You could also use specialcells to get the next blank, but that method
doesn't always work in my experience!

 

[Guest]

Assuming all the United Jobs will have the same invoice number:

Sub InvoiceUnitedJobs()
Dim ivNum as Long
Dim cell As Range
Dim rng as Range
Dim rng2 as Range
With Worksheets("sheet1")
set rng2 = .Range(.Cells(1,2),.Cells(rows.count,2).End(xlup))
End with
ivNum = 100
For Each cell In rng2
If cell.Value = "United" Then
cell.Offset(0, 3).Value = ivNum
set rng = Worksheets("Invoice").Cells(rows.count,1).end(xlup)
if not isempty(rng) then set rng = rng(2)
rng.Value = cell.Offset(0,-1).Value
rng.offset(0,1).Value = cell.offset(0,1).Value
rng.offset(0,2).value = cell.offset(0,2).Value
End If
Next
End Sub