Lande
10-27-2008, 06:29 AM
Hello,
My problem is that I am trying to use a fill down macro and for one column, where I had previously used a formula to populate it, the values are not being properly filled down.
I am using the following to fill down to blank cells:
Columns("A:C").Select
Dim oRng As Range
Set oRng = Selection
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Font.Bold = False
Selection.FormulaR1C1 = "=R[-1]C"
oRng.Copy
oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
This works fine, except for column A. In column A, I had previously used a formula to fill the cells. I then deleted all but every ten or so cells. I then pasted special so that it appears as if all but every tenth cell or so is populated and the others are blank, with no formulas:
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[1]=""A"",RC[1]=""B""),RC[2],""XXX"")"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A269"), Type:=xlFillDefault
Range("A1:A269").Select
Columns("A:A").Select
Selection.Replace What:="XXX", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The problem is that it looks like Excel is not reading those cells as blank. They are not being filled in.
Thank you in advance for your help.
My problem is that I am trying to use a fill down macro and for one column, where I had previously used a formula to populate it, the values are not being properly filled down.
I am using the following to fill down to blank cells:
Columns("A:C").Select
Dim oRng As Range
Set oRng = Selection
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Font.Bold = False
Selection.FormulaR1C1 = "=R[-1]C"
oRng.Copy
oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
This works fine, except for column A. In column A, I had previously used a formula to fill the cells. I then deleted all but every ten or so cells. I then pasted special so that it appears as if all but every tenth cell or so is populated and the others are blank, with no formulas:
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[1]=""A"",RC[1]=""B""),RC[2],""XXX"")"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A269"), Type:=xlFillDefault
Range("A1:A269").Select
Columns("A:A").Select
Selection.Replace What:="XXX", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The problem is that it looks like Excel is not reading those cells as blank. They are not being filled in.
Thank you in advance for your help.