Excel Macro Help (autofill)

Posted on 2003-02-20
Medium Priority
Last Modified: 2008-02-01
I'm in the process of writng an excel application to update a bunch of charts... I'm in the very beginning of the processs.  where I'm taking searching column A for the first empty cell then inputting by month a new year (12 cells)... where i'm having trouble is the autofill to get the rest of the values filled in.

Private Sub cmdNewYear_Click()
Response = MsgBox("Are you wanting to input a new year ?", vbQuestion + vbYesNo, "Question")
     'Message Box Asking if user wants to make a new year
If Response = 6 Then            
     'If Yes then
     'Activate Worksheet named Data
Dim a As Integer
Dim b As Integer
a = 1
Do While Not IsEmpty(ActiveSheet.Cells(a, 1))
     'checking to see if cells are empty then moving
      to the next cell down col A
a = a + 1
ActiveSheet.Cells(a, 1).Select
     'activate first empty cell
b = a + 12
a = a + 1
InputYear = InputBox("What year are you entering ?", "Enter Year", "1/1/20xx")
     'input box for new year
ActiveCell.Value = InputYear
     'input value from new year box into first empty cell
Cells(a, 1).AutoFill Cells(a, 1), Cells(b, 1)

any help would be appreciated

End If
End Sub
Question by:scottbaugh
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

Accepted Solution

lvngstn earned 825 total points
ID: 7990196
Replace the line:
Cells(a, 1).AutoFill Cells(a, 1), Cells(b, 1)
Cells(a, 1).AutoFill Range(Cells(a, 1), Cells(b, 1))

One thing you might notice is that this does not fill in 12 months, but rather 12 days if the user sends a full date (i.e. "1/1/2003") through the input box.  To remedy this, replace the autofill line with this for-loop:

For i = 1 To 12
  Cells(a + i, 1).Value = DateSerial(Year(inputyear), i, 1)
Next i

Hope this is what you're looking for.

Author Comment

ID: 7993790
Thanks for the help... I can actually continue my work now.  I had to tweak it a little... ended up adding ActiveSheet. to the beginning of the For...Next statement.  Thanks again for your help

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

800 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