R/T Error 1004
 

Code is Bombing 4 lines from bottom;
How can I alter to fix?

Sub FillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Dim RngArea As Range
If Selection.Columns.Count 1 Then
MsgBox "You can Only Select 1 Column at a time"
Exit Sub
End If
Set Rng1 = Selection
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng2
.Copy
.PasteSpecial xlPasteValues ' R/T 1004 - CANNOT BE USED ON MULTIPLE
SELECTIONS
End With
Application.CutCopyMode = False
End Sub

R/T Error 1004
 

Hi Jim,

Try:

'=============
Public Sub FillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Dim RngArea As Range

If Selection.Columns.Count 1 Then
MsgBox "You can Only Select 1 Column at a time"
Exit Sub
End If

Set Rng1 = Selection

On Error Resume Next 'In case no blanks found!
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not Rng2 Is Nothing Then
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng1
.Value = .Value
End With
End If
End Sub
'<<=============


---
Regards,
Norman



"Jim May" wrote in message
news:tWQpg.49358$fG3.31811@dukeread09...
Code is Bombing 4 lines from bottom;
How can I alter to fix?

Sub FillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Dim RngArea As Range
If Selection.Columns.Count 1 Then
MsgBox "You can Only Select 1 Column at a time"
Exit Sub
End If
Set Rng1 = Selection
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng2
.Copy
.PasteSpecial xlPasteValues ' R/T 1004 - CANNOT BE USED ON MULTIPLE
SELECTIONS
End With
Application.CutCopyMode = False
End Sub

R/T Error 1004
 

Norman,
Anytime when returning to my PC after posting a Question AND I see
"Norman Jones" - I know I'm gonna get a good reply +++ learn something
GOOD - Your
Suggested code is perfect - Yeah it's the original Values Rng1 that need
Converting to Values, not having to refer to the Rng2. Thanks Again;
Jim May

"Norman Jones" wrote in message
:

Hi Jim,

Try:

'=============
Public Sub FillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Dim RngArea As Range

If Selection.Columns.Count 1 Then
MsgBox "You can Only Select 1 Column at a time"
Exit Sub
End If

Set Rng1 = Selection

On Error Resume Next 'In case no blanks found!
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not Rng2 Is Nothing Then
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng1
.Value = .Value
End With
End If
End Sub
'<<=============


---
Regards,
Norman



"Jim May" wrote in message
news:tWQpg.49358$fG3.31811@dukeread09...

Code is Bombing 4 lines from bottom;
How can I alter to fix?

Sub FillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Dim RngArea As Range
If Selection.Columns.Count 1 Then
MsgBox "You can Only Select 1 Column at a time"
Exit Sub
End If
Set Rng1 = Selection
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng2
.Copy
.PasteSpecial xlPasteValues ' R/T 1004 - CANNOT BE USED ON MULTIPLE
SELECTIONS
End With
Application.CutCopyMode = False
End Sub