add value to cell if cell is not null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marshallp24@gmail.com - 05 Mar 2008 17:21 GMT

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

Reply to this Message

reitanospa1@yahoo.com - 05 Mar 2008 17:48 GMT

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, Marshall...@gmail.com wrote:

> Hi
>
[quoted text clipped - 7 lines]
>
> cheers

Reply to this Message

Marshallp24@gmail.com - 05 Mar 2008 17:57 GMT

On 5 Mar, 17:48, reitanos...@yahoo.com 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.
[quoted text clipped - 18 lines]
>
> - 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.

Reply to this Message

Harlan Grove - 05 Mar 2008 17:55 GMT

Marshall...@gmail.com 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.

Reply to this Message

Dave Peterson - 05 Mar 2008 18:09 GMT

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

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

> Marshall...@gmail.com wrote...
> >I'm having problems with this and I thought it would be simple.
[quoted text clipped - 30 lines]
> 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.

Signature

Dave Peterson

Reply to this Message