blank cells
 

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

blank cells
 

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

---
Mark Bigelow
mjbigelow at hotmail dot com
http://www.insideprospects.com

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

blank cells
 

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]"


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

blank cells
 

thank you!
-----Original Message-----

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]"


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

blank cells
 

thank you!
-----Original Message-----
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

---
Mark Bigelow
mjbigelow at hotmail dot com
http://www.insideprospects.com

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

blank cells
 

thank you!
-----Original Message-----
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

---
Mark Bigelow
mjbigelow at hotmail dot com
http://www.insideprospects.com

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