Loop and Copy

[xenolith01]

I'm new to VBA and struggling with what I think should be a simple 'For' loop!

I have a set of data that when exported and opened in Excel gives a list of companies and dates of when an order has been placed.
The data contains the company name in column A and date of order in column B.
The orders are grouped by company name starting with the earliest order
The data only puts a company name for the first order date and blanks for any successive orders.

e.g.

Columns
A-----------------B
Company A 02/10/15
--------------08/10/15
--------------12/10/15
--------------18/10/15
Company B 02/10/15
--------------08/10/15
--------------12/10/15
--------------18/10/15
--------------24/10/15
--------------25/10/15
--------------30/10/15
Company C 02/10/15
--------------08/10/15
--------------12/10/15
--------------18/10/15

I need to loop through the data copying the correct company name to the blanks

My current thinking is to first find the row with the last date from column B
Use the row number to create a counter from 1 (or row number where the data starts) to the last row occupied by a date
Perform an IsEmpty check on each cell in column A.
If the cell is not empty copy the value to a variable
loop to the next cell and if empty paste the variable
Continue looping copying and pasting until the last row value

But how!!?

Any ideas to get me started would be appreciated.

Thanks

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG21Oct09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, -1)
    [COLOR="Navy"]If[/COLOR] Dn.Value <> "" [COLOR="Navy"]Then[/COLOR]
        Temp = Dn.Value
    [COLOR="Navy"]Else[/COLOR]
        Dn.Value = Temp
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[xenolith01]

Thanks Mick for the quick reply and yes your code does exactly what it was meant to.

My data is slightly different to that which I originally posted but I decided to cut it down to help with my learning process.
I've edited your code a bit as below which again works well but I was wondering if there is a way to condense the code or more specifically 'Do I need a FOR loop for each instance or could this be simplified?'

Thanks in advance as this has helped my slow learning process!!

Sub fillBlankDetails() 'Thanks MG21Oct09 for initial code


Dim RngE As Range 'finds last row containing a date
Dim RngA As Range, RngB As Range, RngC As Range, RngD As Range 'Dims the ranges ready for copying values
Dim TempA As String, TempB As String, TempD As String 'Dims Strings to hold data


Set RngE = Range(Range("E7"), Range("E" & Rows.Count).End(xlUp)) 'sets a range from E7 to the last row that contains a date in column E

'Column A
For Each RngA In RngE.Offset(, -4) 'For loops check for blanks and substitute with correct data
    If RngA.Value <> "" Then       'Applies to columns A,B,C,D based on column E
        TempA = RngA.Value
    Else
        RngA.Value = TempA
    End If
Next RngA

'Column B
For Each RngB In RngE.Offset(, -3)
    If RngB.Value <> "" Then
        TempB = RngB.Value
    Else
        RngB.Value = TempB
    End If
Next RngB

'Column C
For Each RngC In RngE.Offset(, -2)
    If RngC.Value <> "" Then
        TempC = RngC.Value
    Else
        RngC.Value = TempC
    End If
Next RngC

'Column D
For Each RngD In RngE.Offset(, -1)
    If RngD.Value <> "" Then
        TempD = RngD.Value
    Else
        RngD.Value = TempD
    End If
Next RngD


End Sub

Thanks

Gavin