Newbie Repeated Copy-Paste Variable Selection Question

[MissRibena]

Hi everyone

I've been teaching myself some VBA with online resources and a book from J. Walkenbach. I've fiddled around with different Do While and For Each combinations but I'm getting nowhere.

The "Before" worksheet in attached workbook represents the file as it is exported from our accounts package. I need to copy the Customer name in C Column (e.g Plastic Components) beside each item on that customers account so that the customer's name appears in the B Column (see "After" worksheet) (for each customer). I just can't seem to get the code to recognise that each customer may have different numbers of transactions.

Maybe this is way to advanced for me as a beginner. And sorry in advance if I've posted in the wrong forum and thanks a million for having a look.

Rebecca

[acw]

Rebecca

Try this



Sub aaa()
For Each ce In Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
If Not IsEmpty(ce) And IsEmpty(ce.Offset(0, 1)) Then cpy = ce.Value
If ce.Offset(0, 1).Value = "Invoice" Then ce.Offset(0, -1).Value = cpy
Next ce
End Sub



HTH

Tony

[MissRibena]

Thanks a million Tony

That does the trick. If you had the time, it would be great if you could explain how this bit works:


Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)

My biggest problem is I keep thinking of the hardest way to go about solving something. I suppose if I keep practising the solutions will come more naturally.

Thanks again
Rebecca

[ALe]

That does the trick. If you had the time, it would be great if you could explain how this bit works:

Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)


it gets the last cell of column three - Cells(Rows.Count,3)
it moves up from that cell till it gets a cell with a value - End(xlUp)
it gets the number of that row - .Row (suppose 5)
the sentence returns a range that is between range"C1" and range"C5"

[MissRibena]

Thanks again.

Hopefully it will get a bit more easy for me in time!

Rebecca