Hi

I'm having problems with this and I thought it would be simple.
Basicly, I want to add a value to cell A1 when the spreadsheet opens,
then when its opened next add the same value to A2 and so on.

I thought it would be done with a simple if statement.

Any help is welcome.

cheers

It would probably be easiest to do this with a macro - naming the
macro auto_open will invoke the macro when the workbook is opened.

Use an IF in the macro to make sure it's not empty, and then once
there is some data you can use the Selection.End(xlDown).Select
command to find the last item in the list and just move down one.

On Mar 5, 12:21 pm, wrote:
Hi

I'm having problems with this and I thought it would be simple.
Basicly, I want to add a value to cell A1 when the spreadsheet opens,
then when its opened next add the same value to A2 and so on.

I thought it would be done with a simple if statement.

Any help is welcome.

cheers

On 5 Mar, 17:48, wrote:
It would probably be easiest to do this with a macro - naming the
macro auto_open will invoke the macro when the workbook is opened.

Use an IF in the macro to make sure it's not empty, and then once
there is some data you can use the Selection.End(xlDown).Select
command to find the last item in the list and just move down one.

On Mar 5, 12:21 pm, wrote:



Hi

I'm having problems with this and I thought it would be simple.
Basicly, I want to add a value to cell A1 when the spreadsheet opens,
then when its opened next add the same value to A2 and so on.

I thought it would be done with a simple if statement.

Any help is welcome.

cheers- Hide quoted text -

- Show quoted text -

I dont want this to be visible on screen , I want it in a hidden
sheet. Its basicly going to be used as a tacker to see how often this
SS is being used.

wrote...
I'm having problems with this and I thought it would be simple.
Basicly, I want to add a value to cell A1 when the spreadsheet opens,
then when its opened next add the same value to A2 and so on.

I thought it would be done with a simple if statement.

Nope. What you describe requires a macro, specifically either a
Workbook Open macro or an Auto_Open macro. Try the following in the
ThisWorkbook class module.


Private Sub Workbook_Open()
Const INCREMENT As Double = 1
Dim c As Range

Set c = Worksheets(1).Range("A1")

If Not IsEmpty(c.Value2) Then
Set c = c.End(xlDown)
If c.Row = c.Parent.Rows.Count Then
If Not IsEmpty(c.Value2) Then
MsgBox Prompt:="Column A completely filled.", Title:="ERROR"
Else
Set c = c.End(xlUp)
End If
End If
Set c = c.Offset(1, 0)
End If

c.Value2 = c.Value2 + INCREMENT
End Sub


You'd then need to save the file in order for it to know to move to
the next cell down in column A the next time it's opened.

I think I'd add an "Exit Sub" after the msgbox.

Or something to avoid the .offset(1,0) line.

Harlan Grove wrote:

wrote...
I'm having problems with this and I thought it would be simple.
Basicly, I want to add a value to cell A1 when the spreadsheet opens,
then when its opened next add the same value to A2 and so on.

I thought it would be done with a simple if statement.

Nope. What you describe requires a macro, specifically either a
Workbook Open macro or an Auto_Open macro. Try the following in the
ThisWorkbook class module.

Private Sub Workbook_Open()
Const INCREMENT As Double = 1
Dim c As Range

Set c = Worksheets(1).Range("A1")

If Not IsEmpty(c.Value2) Then
Set c = c.End(xlDown)
If c.Row = c.Parent.Rows.Count Then
If Not IsEmpty(c.Value2) Then
MsgBox Prompt:="Column A completely filled.", Title:="ERROR"
Else
Set c = c.End(xlUp)
End If
End If
Set c = c.Offset(1, 0)
End If

c.Value2 = c.Value2 + INCREMENT
End Sub

You'd then need to save the file in order for it to know to move to
the next cell down in column A the next time it's opened.

--

Dave Peterson