I receive an Excel workbook where the information I use is coupled into
two rows per item. The first row is the old information, and the second
row is the new information. If the new information is the same as the
old info, then nothing appears on the second row--the new information
cell is blank. This causes havoc with the macro I use to convert the
information so it becomes more easily usable and having the sender edit
the format is not an option.

Is there a function that I can insert into a macro so if the cell is
empty, then it will copy the cell from above it? For example, if S3 is
empty then it will copy the information from cell S2.

I know my terminology is off, so I apologize. My thanks in advance.


--
Cacoe
------------------------------------------------------------------------
Cacoe's Profile: http://www.excelforum.com/member.php...o&userid=28894
View this thread: http://www.excelforum.com/showthread...hreadid=486416

Hi Cacoe,

Try:
'==============
Public Sub Tester()
Dim Rng As Range

Set Rng = Selection '<<===== CHANGE

On Error Resume Next
Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
On Error GoTo 0

End Sub
'<<==============

---
Regards,
Norman


"Cacoe" wrote in
message ...

I receive an Excel workbook where the information I use is coupled into
two rows per item. The first row is the old information, and the second
row is the new information. If the new information is the same as the
old info, then nothing appears on the second row--the new information
cell is blank. This causes havoc with the macro I use to convert the
information so it becomes more easily usable and having the sender edit
the format is not an option.

Is there a function that I can insert into a macro so if the cell is
empty, then it will copy the cell from above it? For example, if S3 is
empty then it will copy the information from cell S2.

I know my terminology is off, so I apologize. My thanks in advance.


--
Cacoe
------------------------------------------------------------------------
Cacoe's Profile:
http://www.excelforum.com/member.php...o&userid=28894
View this thread: http://www.excelforum.com/showthread...hreadid=486416

Cacoe: here is one that should work:

'--------------------------------------------------------------------
' *** INSTRUCTIONS ***
'
' STEP 1
' Copy this into any Module in the workbook you want the Macro
' to be available in.
' STEP 2
' Select the entire range of cells you want to process:
' From your question, I would assume that area to be something
' like (S1 To S?20).
'
' STEP 4
' Run the macro 'CopyAbove' from the TOOLS...MACRO...MACROS Dialog
'
' That's it!!!
'--------------------------------------------------------------------
Public Sub CopyAbove()

Dim curRng As Range
Dim Evaluate As Range

Set Evaluate = Application.Selection
For Each curRng In Evaluate

If curRng.Row 1 Then

If curRng = "" And curRng.Offset(-1, 0) < "" Then

curRng = curRng.Offset(-1, 0)

End If

End If

Next curRng

End Sub

Both work well. Thank you very much

--
Caco
-----------------------------------------------------------------------
Cacoe's Profile: http://www.excelforum.com/member.php...fo&userid=2889
View this thread: http://www.excelforum.com/showthread.php?threadid=48641