PDA

View Full Version : Blank Cells and Filling Down R[-1]C



AZIQN
02-27-2009, 12:01 PM
Hi, I have an example workbook that demonstrates what my issue is. I need to fill the names in Col A down for the blank cells underneath each 'Vendor' so that I may go back and perform a lookup/index/match later on the data next to it (the names in red show what I need). I attempted to use the following code, but received an error stating the "Object doesn't support this property or method". Any ideas?

With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
.xlCellTypeBlanks.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With

Thanks for your help!

xld
02-27-2009, 12:28 PM
Try this array formula

=IF(C2="",SUM(D3:INDEX(D3:D20,MIN(IF(B3:B20<>"",ROW(B3:B20)-MIN(ROW(B3:B20))+1)))),"")

francis
02-28-2009, 02:57 PM
Your attached doesn't show any names in red, so I assume that you want
to fill in the blanks from the value above in Col A.

You don't need a macro or formula to do this

1) Select the range, A2 to A20 in your example
2) Press Ctrl+G
3) Click Special in the Go To box
4) Select Blanks option
5) Click Ok
6) In the formula bar, type =A2
7) Press Ctrl + Enter
8) Reselect the range and Edit >> Copy
9) Edit >> Paste Special >> Value >> OK

HTH

mikerickson
02-28-2009, 11:07 PM
With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error Goto 0
.Value = .Value
End With

James Niven
03-01-2009, 07:12 PM
Hi All,

I just found this site and this code that Mikeerickson posted does what I wish on a spreadsheet.


With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error Goto 0
.Value = .Value
End With

I wish to go a step further and looking for assistance.

The cells that were blank that were filled in from the cell above, "How do I color and Bold the filled in cells only"?
So, when I look at the column that was filled in where the blanks were, I can check to make sure everyting is in order!

Thanks

James Niven

xld
03-02-2009, 01:51 AM
With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
.Font.Bold = True
.Interior.ColorIndex = 38
End With

James Niven
03-02-2009, 08:51 AM
XLD,

Thanks for you reply, to tell you the truth, I was close myself to what you had in the code below, I kept getting "Object Required" error, I forgot the = true part of the code.

With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error Goto 0
.Value = .Value
.Font.Bold = True
.Interior.ColorIndex = 38
End With

Ok, what you have done is highlight the columns which were filled in and made them bold, wonderful, this will work for me!!

Thanks for your input, I am learning this new language, always willing to learn!!
I see you're also in Texas, what town?

Thanks

James Niven
Cedar Creek.

xld
03-02-2009, 08:56 AM
I see you're also in Texas, what town?

Check that flag again.

James Niven
03-02-2009, 10:48 AM
Thanks XLD, wow you're right, very close to the Texas state flag, Chile OK.

I need to adjust my glasses.


Thanks

DanOfEarth
03-02-2009, 08:08 PM
I'll be damned. I thought that was Texas also.:doh: