VBA Macro to check for multiple blank cells, then copy and paste accordingly

[envisioning]

I have a report that is over 600 pages long.

I am able to remove the junk and afterwards, import it into excel via Text to Column.

The problem is that when the ancient database (an Old DOS one) dumps the data into text, whatever the search field is (example: CABLES), if there is more than one listing for each Cable, the search field is not listed again.

Here is a picture:

As you can see, Cable is listed once, and the Raleways and other fields are continued down until they are done, and then there is a blank row followed by the next CABLE. I need the CABLE ID in ever row it exists, so sorting by CABLE CODE will work correctly (otherwise, sorting throws the empties to the bottom of the list)

My thinking is, is there any way to do a VBA macro to do following??:
1) find a non-blank field (in column A)
2) copy that field (this would be A2)
3) check to see if next row is blank (A3)
4) if true, see if next row is blank (now we are at A4)
5) if true, paste field into A3, if false, restart process by finding next NON-BLANK row

6) select A4
7) check to see if blank
8) if true, check to see if next row is blank (A5)
9) if true, paste A2 value in A4
10) if false, restart process

This would be repeated over and over for the whole large document.

Basically, if there are two blank rows in a row, the Cable ID above the blank rows needs to be pasted in the first blank row.

Whenever there is a situation where there is one blank row only (data exists in the second row), this process needs to start over with the new CABLE ID.

Is this possible or am I completely out of my element? I have been messing with some code but can not seem to figure how to write it so it can do it automatically.

Thanks in advance.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG05May05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] 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
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1) <> "" [COLOR="Navy"]Then[/COLOR]
            Temp = Dn.Offset(, -1)
        [COLOR="Navy"]ElseIf[/COLOR] Dn <> "" And Dn.Offset(, -1) = "" [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, -1) = Temp
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[VoG]

Try

Sub Blanks()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]="""","""",R[-1]C)"
    .Value = .Value
End With
End Sub

 

[envisioning]

Thank you VoG and Mick, they both work great! Sorry for the delay, I got swamped at work and this was put on the back burner for a few days.

Thank you for the quick response!

I have been using the F2 (Object Browser) Menu to learn alot of the commands in VBA, however, I am trying to learn all the shortcuts (if that's what you would call them).

When use a "," here:
========================
If Dn.Offset(, -1) <> "" Then
Temp = Dn.Offset(, -1)
ElseIf Dn <> "" And Dn.Offset(, -1) = "" Then
Dn.Offset(, -1) = Temp
End If
========================

I am trying to understand what they do. I am sure there is a bunch of similar commands ( I know that <> means does "equal to"), but is there a list of these commands somewhere?

Thanks again!

 

[envisioning]

Wow, sorry guys, I was under the impression the "," was doing something, but it was just leaving the first argument blank.



Sorry!

Thanks again for all the help!

If there is any really good cheat sheets out there, I would definitely appreciate the link. I'm trying to learn this stuff with as few questions as possible! (not starting out so well! )

Thanks!