blank cells

[marta]

Hello,
What I would like to do is go through a list in excel. If
the cell is blank, then the value above the blank cell
should be pasted onto that cell. The process i've written
works for only the cell that is right below the value.
What if there is more than one blank cell?
I started experimenting with setting up a conditional
statement If Null, Then... but it does not seem to work.
How can I differentiate between blank cells and cells with
values in my code?
thanks for your help!
Marta

Dim blankcell As Integer

blankcell = 1

Dim ticker As String

ticker = Cells(blankcell, 1)

Do Until Cells(blankcell, 1) = "Totals"

If Cells(blankcell, 1) = Null Then

blankcell = blankcell - 1
Cells(blankcell, 1).Select
Selection.Copy
blankcell = blankcell + 1
Cells(blankcell, 1).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Cells(blankcell, 1) = ticker And Cells(blankcell,
1).Offset(1, 0) = "" Then
Cells(blankcell, 1).Select
Selection.Copy

blankcell = blankcell + 1

Cells(blankcell, 1).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End If
End If
blankcell = blankcell + 1
ticker = Cells(blankcell, 1)

Loop

 

[Mark Bigelow]

Hi Marta,

This is the code I use for doing that:

intLastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
intCount = 1
Do While intCount <= intLastRow
If Range("B" & intCount).Value = Empty Then
Range("B" & intCount).Value = Range("B" & intCount - 1).Value
End If
intCount = intCount + 1
Loop

Let me know if that doesn't work or you need anything else.

Mark

 

[Andy]

If you are happy to have the cell contents as a regference to the cell
above rather than an absolute value, then try:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]"

 

[marta]

thank you!

 

[marta]

thank you!

 

[marta]

thank you!