Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error using..... .Value = .Value
.....no merged cells...
same as all the previous ones that worked.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |