Solved

Using VBA in Excel, how can I copy a numeric text value down a column without entering as a series?

Posted on 2011-02-23
3
207 Views
Last Modified: 2012-05-11
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

Open in new window

0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 34965373
Use:

    Range("D2").AutoFill Destination:=Range("D2:D" & lastrow), Type:=xlFillCopy

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34965392
More specifically, just set the value:

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
0
 

Author Closing Comment

by:thutchinson
ID: 34965589
Thanks for helping a rookie Kevin!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question