Help with Code

[JimMay]

After Running the below Macro, certain cells (just 2 or 3) contain The
R[-1]C versus 65546 (a number type); How can I correct?
TIA;;


Private Sub FillNum()
' Macro recorded 4/23/2006 by Jim May
'
Lrow = Cells(Rows.Count, "E").End(xlUp).Row ' get last row using
amt col
Set MyNum = Range("F5:F" & Lrow)
MyNum.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
MyNum.Copy
MyNum.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A4").Select
End Sub

 

[Bob Phillips]

Are those cells formatted as Text?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

I wouldn't expect a problem with it but try

MyNum.Formula = MyNum.Value

rather than

MyNum.Copy
MyNum.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[Tim Williams]

Looks OK to me. Some slight improvement (avoid the copy/paste):


Sub FillNum()

Dim Lrow As Long, MyNum As Range

Lrow = Cells(Rows.Count, "E").End(xlUp).Row ' get last row using amt col
With Range("F5:F" & Lrow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Range("A4").Select


End Sub


Tim

 

[Jim May]

Yes, they are primarily Check Numbers,,, 63444, 63445, but an occiation
63446, Blamk, Blank, Blank, They were originally numbers - General Format; I
I looped thru trying to assign a " ' " prefix..

Are those cells formatted as Text?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

After Running the below Macro, certain cells (just 2 or 3) contain The
R[-1]C versus 65546 (a number type); How can I correct?
TIA;;


Private Sub FillNum()
' Macro recorded 4/23/2006 by Jim May
'
Lrow = Cells(Rows.Count, "E").End(xlUp).Row ' get last row using
amt col
Set MyNum = Range("F5:F" & Lrow)
MyNum.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
MyNum.Copy
MyNum.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A4").Select
End Sub