thutchinson
asked on
Using VBA in Excel, how can I copy a numeric text value down a column without entering as a series?
I am trying to copy a number as text down a column but my VBA code is writing the values in a series. In the attached example, I want to copy the string "301" down a formatted text column but Excel writes "301", "302", "303" etc. How do I do it? InsertOfficeNo.xlsm
Sub I_InsertOfficeNo()
'
' I_InsertOfficeNo Macro
' Inserts OfficeNo in COL D
'
Dim lastrow As Integer
Dim officeNo As String
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
officeNo = 301
Sheets("Sheet1").Activate
Columns("D:D").Insert
Range("D2").Value = officeNo
Range("D2").AutoFill Destination:=Range("D2:D" & lastrow), Type:=xlFillDefault
Range("D1").Value = "Office"
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for helping a rookie Kevin!
Sub I_InsertOfficeNo()
'
' I_InsertOfficeNo Macro
' Inserts OfficeNo in COL D
'
Dim lastrow As Integer
Dim officeNo As String
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
officeNo = 301
Sheets("Sheet1").Activate
Columns("D:D").Insert
Range("D2:D" & lastrow).Value = officeNo
Range("D1").Value = "Office"
End Sub
Kevin