Error using..... .Value = .Value

[J.W. Aldridge]

Running the following two codes...

About halfway running through each page in my workbook, it stops and
gives me an error message on the following line. Any clues as to why
it completes this function on about 15 sheets, then stops halfway with
an error?

Is there a better solution? (Should I combine both columns to fill
simultaneously, change the line to another code, ???)

.Value = .Value





Sub Fill_down_C()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = .Range("C1").Column

Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then

Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With
Application.DisplayAlerts = True
End Sub


Sub Fill_down_D()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = .Range("D1").Column

Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then

Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With
Application.DisplayAlerts = True
End Sub

 

[J.W. Aldridge]

Running them one at a time works fine....
So, I think the only question is how could I combine the two?
Fill down columns C & D (with one code).

 

[Dave Peterson]

Just a wild guess...

Do you use merged cells on that troublesome sheet?

Running the following two codes...

About halfway running through each page in my workbook, it stops and
gives me an error message on the following line. Any clues as to why
it completes this function on about 15 sheets, then stops halfway with
an error?

Is there a better solution? (Should I combine both columns to fill
simultaneously, change the line to another code, ???)

.Value = .Value

Sub Fill_down_C()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = .Range("C1").Column

Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then

Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With
Application.DisplayAlerts = True
End Sub

Sub Fill_down_D()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = .Range("D1").Column

Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then

Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With
Application.DisplayAlerts = True
End Sub

 

[J.W. Aldridge]

.....no merged cells...

same as all the previous ones that worked..

 

[Dave Peterson]

I don't have another guess why it didn't work:

Option Explicit
Sub Fill_down_CD()
Application.DisplayAlerts = False
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long

Set wks = ActiveSheet
With wks

Set Rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

On Error Resume Next
Set Rng = .Range("C2" & LastRow).Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

With .Range("C")
.Value = .Value
End With

End With
Application.DisplayAlerts = True
End Sub