Help with Code

J

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
 
B

Bob Phillips

Are those cells formatted as Text?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
T

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
 
T

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
 
J

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..

Bob Phillips said:
Are those cells formatted as Text?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

JimMay said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top