Error using..... .Value = .Value
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 |
Error using..... .Value = .Value
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). |
Error using..... .Value = .Value
Just a wild guess...
Do you use merged cells on that troublesome sheet? "J.W. Aldridge" wrote: 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 -- Dave Peterson |
Error using..... .Value = .Value
.....no merged cells...
same as all the previous ones that worked.. |
Error using..... .Value = .Value
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:D" & 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:D") .Value = .Value End With End With Application.DisplayAlerts = True End Sub "J.W. Aldridge" wrote: ....no merged cells... same as all the previous ones that worked.. -- Dave Peterson |
All times are GMT +1. The time now is 06:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com