?
Solved

Excel Macro Help (autofill)

Posted on 2003-02-20
2
Medium Priority
?
461 Views
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
ThisWorkbook.Worksheets("Data").Activate
     '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
Loop
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
0
Comment
Question by:scottbaugh
[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 Comments
 
LVL 2

Accepted Solution

by:
lvngstn earned 825 total points
ID: 7990196
Replace the line:
Cells(a, 1).AutoFill Cells(a, 1), Cells(b, 1)
with:
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.
lvngstn
0
 

Author Comment

by:scottbaugh
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
0

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