Cannot convert month number to month name in Excel 2007 VBA

Hi all.

I'm trying to display the month name starting in cell A9 and continuing down column A as long as there are month numbers starting in cell R5. Below is my code, at the bottom is my monthname() code, but it doesn't look at all the records starting in R5, it looks at the R5 only and not R6, R7 etc. and it also displays the month name in A9 and then the same month name 3 cells above A9 and then in A10. What am I doing wrong?

Thank you in advance.

Dim dtLoopDate As Date
Dim dtEndDate As Date
Dim lngWriteRow As Long

dtLoopDate = DateSerial(Range("D1"), Range("B1"), 1)
dtEndDate = DateSerial(Range("D2"), Range("B2") + 1, 1)
lngWriteRow = 5

Do Until dtLoopDate = dtEndDate
    Range("R" & lngWriteRow) = Month(dtLoopDate)
    Range("S" & lngWriteRow) = Year(dtLoopDate)
    dtLoopDate = DateSerial(Year(dtLoopDate), Month(dtLoopDate) + 1, 1)
    lngWriteRow = lngWriteRow + 1
Loop



'==========================================================================================
'==========================================================================================
'==========================================================================================

Dim cnPubs1 As ADODB.Connection
Set cnPubs1 = New ADODB.Connection

Dim strConn1 As String

strConn1 = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=###;DATABASE=###;USER=###;PASSWORD=###;Option=3"

cnPubs1.Open strConn1

Dim rsPubs1 As ADODB.Recordset
Set rsPubs1 = New ADODB.Recordset

With rsPubs1

    .ActiveConnection = cnPubs1
    Dim I3
    Dim rng1 As Range
    Set rng1 = Range("R5")
    Dim rng1a As Range
    Set rng1a = Range("S5")
    Dim strSQL1 As String
        
    Dim rng2 As Range
    Set rng2 = Range("A9")

    While rng1.Value <> "" And rng1a.Value <> ""
  
    strSQL1 = "SELECT COUNT(*) From FieldSuperDataEntry WHERE Type = 1 AND MONTH(CreateDate) = '" & rng1.Value & "' AND YEAR(CreateDate) = '" & rng1a.Value & "'"
    .Open strSQL1
    
        If Not rsPubs1.EOF Then
        'rng1.CopyFromRecordset rsPubs1
            rsPubs1.MoveFirst
            For I3 = 0 To rsPubs1.Fields.Count - 1
          
                rng2.Offset(, I3 + 1).Value = rsPubs1.Fields(I3).Value
                                      
            Next I3
        
        End If
        

        rsPubs1.Close
        Set rng1 = rng1.Offset(1)
        Set rng1a = rng1a.Offset(1)
        Set rng2 = rng2.Offset(1)
    Wend
      
End With

cnPubs1.Close

Set rsPubs1 = Nothing
Set cnPubs1 = Nothing

'==================================================================================================
'==================================================================================================
'==================================================================================================
'==================================================================================================

Dim cnPubs2 As ADODB.Connection
Set cnPubs2 = New ADODB.Connection

cnPubs2.Open strConn1

Dim rsPubs2 As ADODB.Recordset
Set rsPubs2 = New ADODB.Recordset

With rsPubs2

    .ActiveConnection = cnPubs2
    Dim I4
    Dim rng3 As Range
    Set rng3 = Range("R5")
    Dim rng3a As Range
    Set rng3a = Range("S5")
    Dim strSQL2 As String
    
    
    Dim rng4 As Range
    Set rng4 = Range("A9")

    While rng3.Value <> "" And rng3a.Value <> ""
    strSQL2 = "SELECT COUNT(*) From FieldSuperDataEntry WHERE Type = 2 AND MONTH(CreateDate) = '" & rng3.Value & "' AND YEAR(CreateDate) = '" & rng3a.Value & "'"
    .Open strSQL2
    
        If Not rsPubs2.EOF Then
            rsPubs2.MoveFirst
            For I4 = 0 To rsPubs2.Fields.Count - 1
          
                rng4.Offset(, I4 + 2).Value = rsPubs2.Fields(I4).Value
                                      
            Next I4
        
        End If
        

        rsPubs2.Close
        Set rng3 = rng3.Offset(1)
        Set rng3a = rng3a.Offset(1)
        Set rng4 = rng4.Offset(1)
    Wend
      
End With

cnPubs2.Close

Set rsPubs2 = Nothing
Set cnPubs2 = Nothing
'==================================================================================================
'==================================================================================================
'==================================================================================================
'==================================================================================================
Dim rng5 As Long

With Worksheets("Sheet1")

    rng5 = .Range("R" & Rows.Count).End(xlUp).Row

    .Range("A9:A" & rng5).Formula = "=TEXT(R5,""mmmm"")"

End With



End Sub

Open in new window

Sim1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
As far as I can see the code at the end should work as long as you have dates in column R.

There's nothing in it that would put the formula above A9.

Do you have calculation set to automatic?

What's the value of Rng5?
0
FaustulusCommented:
In column R you only have the month. The year is in column S, and no day.
Therefore amend the formula you write at the end to read as follows.
    Dim rng5 As Long
    
    With Worksheets("Sheet1")
        rng5 = .Range("R" & Rows.Count).End(xlUp).Row
        .Range("A9:A" & rng5).Formula = "=TEXT(DATE(S5, R5, 1),""mmmm"")"
    End With

Open in new window

Actually, since you only want to show the month, DATE(, R5, 1) or DATE(1, R5, 1) would work equally well. The point is that you need a proper date consisting of year, month and day.
BTW, you don't need to declare rng5 at this point. Instead, you can reuse one of the previously declared Longs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sim1980Author Commented:
I added concatenation to do: month/1/year and now the TEXT() function works. I also changed the Range from "R" to "B" and it no longer places months above A9.

Dim rng5 As Long

With Worksheets("Sheet1")

    rng5 = .Range("B" & Rows.Count).End(xlUp).Row

    .Range("A9:A" & rng5).Formula = "=TEXT(R5,""mmmm"")"

End With
0
FaustulusCommented:
Lovely!
Looks good.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.