Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Nested IF - return a blank when compared cells are blank | New Users to Excel | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions |