Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Excel Macro Help (autofill)

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
1 Solution
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.
scottbaughAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now