Cell value unseen on first open

chaynes · September 22nd, 2008, 04:44 AM

Hi. I have a problem and would be very grateful for any help as it's an awkward error on an otherwise fully functioning project.

Background: I wrote this code to take the input of a supplier or list of suppliers (and brands) and two dates and to retrieve the sales for that brand over the given dates from a different worksheet (by opening the source file, filtering on the input and transferring the data).

The input of the supplier(s) and/or brand(s) is by a dropdown list. The code builds an array from the input to use as the filter (and also a filename to save the data retrieved).

Problem: When I first open the spreadsheet, the code seems not to recognise the input cell's value - the reference to it is correct, and on any further runs it works fine, it is just the first time the file is opened.

It's a big file so I've just included the first part of the relevant subroutine, if more is required please just let me know and I can email the file or post more code. Sorry there is so much, the offending line is actually near the bottom of the code section, marked with a ***** comment ******, I thought I should include enough to explain the different variables.

Also apologies if my coding is ugly, I've taught myself in a rush!

Many thanks in advance for any help.
Craig

Code:

Sub GetNewData()
    'Copies sales and units data from master sales sheet for the Suppliers, Brands and dates specified

    With Application
        .Calculate
        .ScreenUpdating = False
    End With

    ' Define Input Sheets
    Dim wsInput As Worksheet
    Dim wsNewSales As Worksheet
    Dim wsNewUnits As Worksheet

    Set wsInput = Workbooks("SalesSheetCreator.xls").Worksheets("UserInput")
    Set wsNewSales = Workbooks("SalesSheetCreator.xls").Worksheets("SalesData")
    Set wsNewUnits = Workbooks("SalesSheetCreator.xls").Worksheets("UnitsData")

    Dim myFileName As String
    Dim myFileFull As String
    Dim fromYear As Integer
    Dim toYear As Integer
    Dim fromDate As Date
    Dim toDate As Date

    If IsEmpty(fromDate) And IsEmpty(toDate) Then
        MsgBox "Please enter dates"
    End If

    fromYear = wsInput.Cells(14, 5).Value
    toYear = wsInput.Cells(15, 5).Value
    fromDate = wsInput.Cells(14, 4).Value
    toDate = wsInput.Cells(15, 4).Value

    ' Input Validation
    If fromYear <> toYear Then
        MsgBox "Please enter dates in the same sales year"
        Exit Sub
    End If
    If toDate < fromDate Then
        MsgBox "From Date must be before To Date"
        Exit Sub
    End If

    myFileName = fromYear & " Sales Sheet - DO NOT DELETE.xls"
    'myFileFull = "\\Buyingfs\buying\Ocado Total Sales Sheets\" & myFileName
    myFileFull = "C:\Documents and Settings\craig.haynes\Desktop\SuppSelling\" & myFileName

    ' Open Master Sales Sheet
    If Not IsWorkbookOpen(myFileName) Then
        Workbooks.Open fileName:=myFileFull, Notify:=False, UpdateLinks:=2, Password:="monkey", ReadOnly:=True
    End If

    Dim wsMasterSales As Worksheet
    Dim wsMasterUnits As Worksheet
    Set wsMasterSales = Workbooks(myFileName).Worksheets("Cash Sales")
    Set wsMasterUnits = Workbooks(myFileName).Worksheets("Unit Sales")

    Dim inputSuppliers As Range
    Dim inputBrands As Range
    Dim numSuppliers As Integer
    Dim numBrands As Integer
    Dim arrayCounter As Integer

    ' Delete old data and formatting
    Call ClearSheet(wsNewSales)
    Call ClearSheet(wsNewUnits)

    ' Define ranges
    Set inputSuppliers = wsInput.Range("B3:B12")
    Set inputBrands = wsInput.Range("C3:C12")

    ' Check for correct input
    numSuppliers = Application.CountA(inputSuppliers)
    numBrands = Application.CountA(inputBrands)

    If numSuppliers = 0 And numBrands = 0 Then
        MsgBox ("You must enter at least one supplier or at least one brand")
        Exit Sub
    End If

    Dim i As Integer
    Dim j As Integer
    Dim aSuppliers() As String
    Dim aBrands() As String
    Dim fileName As String
    fileName = ""

    ' Loop through input lists and build an array, ignoring empty cells
    With wsInput

        j = .Range("inputSupps").Column
        arrayCounter = 1

        If numSuppliers > 0 Then
            ReDim aSuppliers(1 To numSuppliers)
            For i = 3 To 12
                If (Not IsEmpty(Cells(i, j))) And arrayCounter <= numSuppliers Then
                    ' ****** The next line is the problem ******
                    aSuppliers(arraycounter) = .Cells(i, j).Value
                    ' Build file name for later use
                    If fileName = "" Then
                        fileName = .Cells(i, j).Value
                    Else
                        fileName = fileName & "-" & .Cells(i, j).Value
                    End If
                    arrayCounter = arrayCounter + 1
                End If
            Next i
        End If

        'Reset and do the same for Brands
        j = .Range("inputBrnds").Column
        arrayCounter = 1

        If numBrands > 0 Then
            ReDim aBrands(1 To numBrands)
            For i = 3 To 12 Step 1
                If (Not IsEmpty(Cells(i, j))) And arrayCounter <= numBrands Then
                    If fileName = "" Then
                        fileName = .Cells(i, j).Value
                    Else
                        fileName = fileName & "-" & .Cells(i, j).Value
                    End If
                    arrayCounter = arrayCounter + 1
                End If
            Next i
        End If