PDA

View Full Version : [SOLVED] Cell value unseen on first open



chaynes
09-22-2008, 03:05 AM
Hi, firstly great site, really helpful. :thumb

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!