Excel - Start Error

[Wicca]

Merry Meet,
I have a funny problem
When I open my Application and run the macro I get
#N/A message in the cells

However if I run it from the ALREADY open state I get the correct data

The data is being lifted from a PLC and place into a Temporary spreadsheet

How do I overcome this problem?
While it was open I could run it twice I suppose but that seems ridiculous solution

Any help you can give will be gratefully accepted

Many thanks



Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford

 

[BiggerBrother]

The N/A occurs because the cells are not formatted to accept the correct type of data. You need to place coding in the macro to change the cell data type.

BB

 

[Wicca]

I thought that two, but if I run the macro from the "already Application running" status, without changing anything else, the data drops nicely into place.

It is only when I start the application that this error occurs


This is the code as it exists at present


Sub Autpen()
'
' AutoOpen Macro
' Created 16/3/2004 by Jonathan (Wicca) Stammers

' Sets timer to activate macro in Module 1 between 00:05 and 00:10 hours according to computer clock


If timer < 600

Application.WindowState = xlMinimized
Sheets("DataInput").Visible = True
Sheets("FLOWDATA").Visible = True
Sheets(Sheets("DataInput").Range("D1").Value + Sheets("DataInput").Range("D3").Value).Unprotect
Calculate

'Make new user sheet Name it with month and year

If Sheets("DataInput").Range("d2") = 1 Then
Sheets("MASTER").Visible = True

Sheets("MASTER").Copy before:=Sheets("Master")
Sheets("MASTER (2)").Activate
Sheets("MASTER (2)").Name = (Sheets("DataInput").Range("D1").Value + Sheets("DataInput").Range("D3").Value) 'Name new sheet to current month&Year
Sheets(Sheets("DataInput").Range("D1").Value + Sheets("DataInput").Range("D3").Value).Activate
Sheets(Sheets("DataInput").Range("D4").Value + Sheets("DataInput").Range("D3").Value).Visible = False
Range("a2,b52") = Sheets("DataInput").Range("d1").Value 'Puts Month on active sheet
Range("B2,c52") = Sheets("DataInput").Range("d3").Value 'Puts Year on active sheet

Sheets("MASTER").Visible = xlSheetVeryHidden


End If


Sheets(Sheets("DataInput").Range("D1").Value + Sheets("DataInput").Range("D3").Value).Visible = True

Call Menus

End If
End Sub
Sub Menus()
'
' Menus Macro
' Created 16/3/2004 by Jonathan (Wicca) Stammers
' Makes a menu entry next to "Help" to allow manual action if necessary
With MenuBars("Worksheet")
.Reset
.Menus.Add ("&Macros")

With .Menus("&Macros")
.MenuItems.Add ("PlantData.xls&1"), "datatransfer"
End With

End With
Application.Run "PlantData.xls!datatransfer"

End Sub

Sub DataTransfer()
'
' DataTransfer Macro
' Created 16/3/2004 by Jonathan (Wicca) Stammers

' Transfers the data and resets all startpoints

Sheets("DataInput").Select
Range("c1:c9").Select
Selection.Copy

'Selection of new Active "Cell" based on current Month

Sheets("FLOWDATA").Activate
InputCell = Sheets("DataInput").Range("E2").Value 'InputCell is calculation for number of rows to go down at beginning of each month
Range("B" & InputCell).Select

ActiveCell.Offset(0, Sheets("DataInput").Range("D2").Value).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' User sheet data then insert data into user sheet, reprotect sheet

Sheets(Sheets("DataInput").Range("D1").Value + Sheets("DataInput").Range("D3").Value).Activate


Range("B53").Select 'Select active cell

ActiveCell.Offset(0, Sheets("DataInput").Range("D2").Value).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Paste Data to required cell
Application.CutCopyMode = False
ActiveSheet.Protect

'Protect sheets and non-user inserted data


Sheets("FLOWDATA").Visible = xlSheetVeryHidden
Sheets("DataInput").Visible = xlSheetVeryHidden

'Save and close workbook

Workbooks("PlantData.xls").Save
Application.Quit

End Sub

I stress here that it puts the data into the correct place but it does not give the correct information. Again I stress, when I run the macro with the workbook already open it runs as sweet as a nut.

I change nothing but do ' out the first IF statement and the Application close statement

However, even with them ' out I still cannot get it to put the correct info into the cell if I open the workbook ONLY when the workbook is open and I run the macro will it go properly.....sorry to labour the point but its dang frustrating Im a user really and I dont have a clue what is causing this.

Many thanks



Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford

 

[Wicca]

Looks like I am not gonna solve this one

Never mind, such is life

"A loyal friend laughs at your jokes when they're
not so good, and sympathizes with your problems
when they're not so bad." -Arnold H. Glasow

Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford