SOS VBA Code Emergency: need to copy tell to empty cell direct below where values in adjacent cells

[Steven Rosenberg]

I have an eight-column Excel (for Mac, but don't think that matters)
sheet (i.e., columns "A" -"H") and am trying to write a macro that
will look down the cells in Column "H" (about 10,000 rows of data)
and, upon finding an empty cell ("target cell"), copy the contents
(text) from the cell directly above that cell IF:

--the "target" cell is empty; AND
--the value of the same-row cells over in column "D" equal each other.

In other words, an example:

IF. in Column "H", Row 26 has a value of "Judge12", and Row 27 is
empty, the macro should copy "Judge 12" from Row 26 to Row 27 in
Column "H"
AS LONG AS Rows 26 and 27 Column "D" both have the same value, e.g.,
"Smith."

 

[Tom Ogilvy]

Sub AAFillrow()
Dim rng As Range
Dim cell As Range
Set rng = Columns(8).SpecialCells(xlBlanks)
For Each cell In rng
If cell.Offset(0, -4).Value = cell.Offset(-1, -4).Value Then
cell.Value = cell.Offset(-1, 0).Value
End If
Next

End Sub

Should do what you want.

 

[Ed Ferrero]

Hi Steven,

This should get you started

Sub conditional_copy()
Dim cell As Range
' assumes that you have selected all cells in col H
' where you want to copy contents

For Each cell In Selection
If cell = "" Then
If cell.Offset(0, -4) = cell.Offset(-1, -4) Then
cell.Offset(-1, 0).Copy Destination:=cell
End If
End If
Next cell

 

[Steven Rosenberg]

Well, thanks for the two suggested answers, but each
one results in "Compile Error" and "Syntax error", with
the "If cell" line, and the following line displayed in red
on my Mac in the edit box.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Those are two separate lines of code - so both shouldn't be highlighted in
red. Sounds like you have some problem with the entry of the code in the
module. I can't imagine the MAC wouldn't support the code in either -
particularly those lines of code.

Regards,
Tom Ogilvy