Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Value = .Value - Error
Got a code the runs 91% of the time, but every now and then I get an
error on this particular line.... (This messes with my mojo cause I keep having to stop and re-run it after I close Excel out) Sub FillColBlanksA() Dim wks As Worksheet Dim Rng As Range Dim lastrow As Long Dim col As Long Set wks = ActiveSheet With wks col = .Range("A1").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 MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Value = .Value - Error
Maybe you could try:
With .Cells(1, col).EntireColumn .copy .pastespecial paste:=xlpastevalues End With "J.W. Aldridge" wrote: Got a code the runs 91% of the time, but every now and then I get an error on this particular line.... (This messes with my mojo cause I keep having to stop and re-run it after I close Excel out) Sub FillColBlanksA() Dim wks As Worksheet Dim Rng As Range Dim lastrow As Long Dim col As Long Set wks = ActiveSheet With wks col = .Range("A1").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 MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Value = .Value - Error
Thanx....
Wurked purfectly! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
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 |