Blank Cells and Filling Down R[-1]C

[AZIQN]

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!

[Bob Phillips]

Try this array formula


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

[francis]

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]

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]

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

[Bob Phillips]

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]

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.

[Bob Phillips]

I see you're also in Texas, what town?

Check that flag again.

[James Niven]

Thanks XLD, wow you're right, very close to the Texas state flag, Chile OK.

I need to adjust my glasses.


Thanks

[DanOfEarth]

I'll be damned. I thought that was Texas also.:doh: