excel blank fill in works and doesn't

[rmyslik]

I have this macro that was working fine but not only works on the first column. The other colums I get #REF?
Here is what I have. My objective is to fill in the blank fields with above data. My problem was sometimes there was more than one blank field so filling in blank field with a blank field didn't work. This worked but why did it stop!!!!
Thanks

Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer

If Selection.Cells.Count = 1 Then
MsgBox "You must select your list and include the blank cells", _
vbInformation, "OzGrid.com"
Exit Sub
ElseIf Selection.Columns.Count > 1 Then
MsgBox "You must select only one column", _
vbInformation, "OzGrid.com"
Exit Sub
End If

Set rRange1 = Range(Selection.Cells(1, 1), _
Cells(65536, Selection.Column).End(xlUp))

On Error Resume Next
Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rRange2 Is Nothing Then
MsgBox "No blank cells Found", _
vbInformation, "OzGrid.com"
Exit Sub
End If

rRange2.FormulaR1C1 = "=R[-1]C"

iReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "OzGrid.com")
If iReply = vbYes Then rRange1 = rRange1.Value
End Sub

 

[xlbo]

how are you selecting the data ? please give as full a description as possible. Did the macro stop and error or did it run and you just got #REF!s in the cells ?

Also, given that you have quite obviously taken this from OzGrid.com, do you not think that it might be better asking them there?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmyslik]

I got this so long ago I didn't even look to see where it came from so I will ask there too. But quite simply I select the range of cells and it completes but returns the #REF. No errors and it seems like it is working until the results give #REF. Strangly enough it works on the A column?

 

[xlbo]

Do the other columns have formulae in them before you run the macro? If so, do any of these formulae return a #REF! result?

In terms of where you got it from, the clue is in this line:

vbInformation, "OzGrid.com"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question