Run macro "fill-blank" code, rise out "#REF!"?

[rainl]

Hi MrExcel,
Could you help to resolve the problem?Thank you very much!

After run the following code, it appears wrong value!
[
Sub Payment()
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Range("A1").Select
End Sub
]
[/QUOTE]

 

[Sandeep Warrier]

Hi

I'm not sure what exactly you are doing with this code. I think you want to fill all the blank cells with the value of the cell above it. Am I correct?

If yes, then the code below will do that for you. The code assumes that the 1st row does not contain any blanks.

Sub Test()

    Dim rcell As Range
    
    For Each rcell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Cells
    
        rcell.Value = rcell.Offset(-1, 0).Value
    
    Next rcell

End Sub

If you only want to look in column A, then change

ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Cells

to

Range("A:A").SpecialCells(xlCellTypeBlanks).Cells<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Peter_SSs]

rainl

Welcome to the MrExcel board!

If your question has not been resolved, perhaps you could fill us in a bit more about your original data, what 'wrong' values the code produces and what the 'right' values would be.

 

[rainl]

Dear Sandeep ,
Thank you for your reply .
I wish to fill data in "A1" joining blank ,so your code is very well, I will use it ,thank you!
I want to attach Excel file in broad, but I didn't know how to do.
And I think the problem had been solved ,the reason is that should make all the data as general mode ,I had try it ,that's OK.

 

[rainl]

Dear Peter,

I want to attach Excel file in broad, but I didn't know how to do.
Could you tell me? For more question use since .Thank you!

 

[Sandeep Warrier]

Hi rainl

You cannot attach workbooks on the forum.

You can however show your spreadsheet using HTML makers.

You could download Excel Jeanie OR PM Richard Scholar for the Board's version of an HTML maker.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">