0

I am trying to write values from an array to a range (just one row) into an excel sheet. The vba script asks for a start and end date, and then generates the months for those dates in a specific format MMMYY (Jan19, Feb19, Mar19, etc). It stores each of those values in an array. Then I want it to write those values to a row in the excel sheet based on a selected cell where those values would start.

With what I have written it only writes Jan19 across the row. I've messed around with it, but I am not sure what I am doing wrong. I appreciate the help! Here is what I have so far.

Sub AddYearHeaders()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim iCtr As Integer

    Dim rStCell As Range
    Dim row As Integer
    Dim intStCol As Integer
    Dim arr() As Variant
    
    dtStart = InputBox("Please input start date of period (mm/dd/yyyy)", "User Input", "Enter start date HERE")
    dtEnd = InputBox("Please input end date of period (mm/dd/yyyy)", "User Input", "Enter end date HERE")
    
    iCtr = DateDiff("m", dtStart, dtEnd)
    
    Set rStCell = Application.InputBox(Prompt:="Please select a cell", Type:=8)
    
    row = rStCell.row
    intStCol = rStCell.Column
    
    For I = 0 To iCtr
        
        ReDim Preserve arr(0, I)
        arr(0, I) = Format(dtStart, "MMMYY")
        dtStart = DateAdd("m", 1, dtStart)
        
    Next I
    
    rStCell.Resize(1, UBound(arr, 2) + 1) = WorksheetFunction.Transpose(arr)

End Sub
1
  • Nix the Transpose. Commented May 5, 2022 at 19:10

1 Answer 1

1

This should do it...

rStCell.Resize(1, I) = arr
Sign up to request clarification or add additional context in comments.

3 Comments

The OP was asking about the actual writing of the array to the worksheet and specifically why only one value was appearing.
I like straight forward answers and that's exactly why I upvoted your post as (helpful) solution to OP's question. - On the other hand it lies precisely in the beauty of SO to show also modified approaches and further ways leading to Rome, and that's why I posted my answer (oriented on OP's code structure). @ExcelHero
Greg, did this work for you?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.