add value to cell if cell is not null

[Marshallp24]

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

 

[reitanospa1]

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.

 

[Harlan Grove]

(e-mail address removed) 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 Autpen 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.

 

[Marshallp24]

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.







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

 

[Dave Peterson]

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

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