Cell value unseen on first open

[chaynes]

Hi, firstly great site, really helpful.

I have a problem and would be very grateful for any help as it's an awkward error on an otherwise fully functioning project. I am using Excel 2007 but saving as .xls for compatability with previous versions.

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 workbook (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.

The file is too big too attach 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

 
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