Cell value unseen on first open

**chaynes** · 09-22-2008, 03:05 AM

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

**Bob Phillips** · 09-22-2008, 03:52 AM

A better version

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
    End With
End Sub

**chaynes** · 09-22-2008, 03:57 AM

Thanks xld, didn't realise it had displayed all the code on one line!

Thread: Cell value unseen on first open

Thread Tools

Display

Cell value unseen on first open

Posting Permissions