Looking through Excel cells for Date

[VBAnovice43]

Before I start, I just want to let everyone know this is not a VBA question. I am working with a stand along program through VB 6.0. I have made an Excel Template that was pre-made. Its a workbook where there is a sheet for every month of the year (Jan '04, Feb '04, Mar '04, Apr '04, etc..). In each worksheet, starting with cell "A8" going down the column to "A38" or whatever the last day of the month is is the days of the month like so:

12/1/03
12/2/03
12/3/03
12/4/03
12/5/03
12/6/03
...
...

When a person runs the program, there is a textbox that has the current date (automatically put in by the program). And it reads this date and finds the correct worksheet (according to month) to open. The program reads a textfile with the info that consists of mainly "As-of-Balances". The current day's info is there, along with the previous three month's End Of Month Balance. What I'm having trouble with is the program finding the correct cell to write one of the dollar figures to. So, the program can find the correct worksheet just fine...its finding the current date in the "A: column that I can't get. Here's my code so far:

'Opens up Workbook
    Set objExcel = CreateObject("Excel.Application")
    Set objWb = objExcel.Workbooks.Open("e:\Excel Workbooks\Alliance\AllianceTotals.xls")
    Set objWs = objWb.Worksheets 'Might need something in ()
    
    objExcel.Application.Visible = True
    Workbooks.Open "e:\Excel Workbooks\Alliance\AllianceTotals.xls"
    
    'Read the variable "CurrentDate" as Date
    Dim CurrentDate As Date
    CurrentDate = Date   'Reads input from textbox
    
    'Checks date and opens correct worksheet
    'Also sets focus on cell B5 on worksheet
    Select Case CurrentDate
        Case "11/1/03" To "11/30/03"
            Worksheets("Nov '03").Activate
            [B5].Select
        Case "12/1/03" To "12/31/03"
            Worksheets("Dec '03").Activate
            [B5].Select
        Case "1/1/04" To "1/30/04"
            Worksheets("Jan '04").Activate
            [B5].Select
        Case "2/1/04" To "2/29/04"
            Worksheets("Jan '04").Activate
            [B5].Select
        Case "3/1/04" To "3/31/04"
            Worksheets("Mar '04").Activate
            [B5].Select
        Case "4/1/04" To "4/30/04"
            Worksheets("Apr '04").Activate
            [B5].Select
        Case "5/1/04" To "5/31/04"
            Worksheets("May '04").Activate
            [B5].Select
        Case "6/1/04" To "6/30/04"
            Worksheets("Jun '04").Activate
            [B5].Select
        Case "7/1/04" To "7/31/04"
            Worksheets("Jul '04").Activate
            [B5].Select
        Case "8/1/04" To "8/31/04"
            Worksheets("Aug '04").Activate
            [B5].Select
        Case "9/1/04" To "9/30/04"
            Worksheets("Sept '04").Activate
            [B5].Select
        Case "10/1/04" To "10/31/04"
            Worksheets("Oct '04").Activate
            [B5].Select
        Case "11/1/04" To "11/30/04"
            Worksheets("Nov '04").Activate
            [B5].Select
        Case "12/1/04" To "12/31/04"
            Worksheets("Oct '04").Activate
            [B5].Select
        Case Else
            DateAlert = MsgBox("You entered an invalid date!", _
            vbExclamation + vbCritical, "Invalid Date")
        End Select
    
    'Declare Variables
    Dim LineofText As String
    Dim DDA As Currency
    Dim MMDA As Currency
    Dim Sav As Currency
    Dim TCDS As Currency
    Dim TTDeposits As Currency
    Dim TTAssets As Currency
    Dim TTSweep As Currency
    Dim Comml As Currency
    Dim Re As Currency
    Dim Constr As Currency
    Dim Consumer As Currency
    Dim NetLoans As Currency
    Dim Undis As Currency
    Dim GrossLoans As Currency
    
    Dim intLinesReadCount As Integer
    intLinesReadCount = 0

    'String variable for # of lines
    Dim strBuffer As String

    'Count Lines and find starting line to read
    Do
        'Read file for # of lines
        Input #1, strBuffer
        'Update Count
        intLinesReadCount = intLinesReadCount + 1
    Loop Until intLinesReadCount = 8
    
    'Read Overall Totals
    Do
        Line Input #1, LineofText$
            Current = Mid(LineofText$, 46, 59)
            PrevMonth1 = Mid(LineofText$, 64, 77)
            PrevMonth2 = Mid(LineofText$, 82, 95)
            PrevMonth3 = Mid(LineofText$, 100, 113)
            
            ActiveCell.Offset(0, 0) = PrevMonth1
            ActiveCell.Offset(1, 0) = PrevMonth2
            ActiveCell.Offset(1, 0) = PrevMonth3
            
            'ActiveCell.Offset(1, 0) =
            
            ActiveCell.Offset(1, 0).Select
    Loop Until intLinesReadCount = 21

Any suggestions or ideas would be appreciated.

 

[irlbinky]

just a suggestion but if you split the date up 3 parts Month, Day and Year.

Dim myDate as Date
Dim eachword as variant
dim individualword
dim mymonth, myday, myyear as int

eachword = split(mydate,"/&quot
mymonth = individualword
next individualword
myday = individualword
next individualword
myyear = individualword

then the row you want is going to 7 + myDay. I know its probably not the nicest way for it, but it does work