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
203 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
  • 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create excel pivot chart 12 43
Excel - Data Validation 3 28
Copy a range from 1..n excel sheets to one destination sheet 2 33
sumifs excel 2013 3 15
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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