1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Increasing value in Excel

Discussion in 'Business Applications' started by lindzi, Jul 17, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. lindzi

    lindzi Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    From looking others threads,I could see that here are many experts and I hope you can help me to.
    What I need is(for example) in sheet1,cellA1,increasing value from sheet2, every time
    I click button.When I push button for the first time,the value is 1.This value is printed in sheet2,cell A1,next time I click on button the value must increase for 1 and put into sheet2,cell A2 and the sheet2 must auto save itself every time the value is added.Butt only
    sheet2 must be saved.
    thanks for any help.
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I do not think it is possible to save a worksheet on it's own and have it remain in the original workbook, I think the whole workbook will be saved.
    If you make it a sheet in a different workbook it will work very well.
    THe code will just look up the second workbook's sheet 1, in the last cell in column "A" and increment the number and put it in the next row and then save the workbook.The second workbook does not even need to be open at the time, the VBA ca take care of that as well.
     
  3. lindzi

    lindzi Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    Thanks for quick response,I will try to work this out and if I wont,than I will ask for
    help again.
     
  4. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    lindzi, I have written the code for you. it is on a button in Workbook BookA, it opens Workbook BookB and increments the value each time you click it, saves the workbook and closes it.
    The two workbooks need to be in the same directory.
     

    Attached Files:

  5. lindzi

    lindzi Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    Working great.thanks again
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    A couple of pointers here (also see: http://forums.techguy.org/business-applications/483895-ms-excel-data-sort.html, not trying to pick on you either Tony, just pointing it out) which may affect the code's performance under certain conditions.

    Firstly, declare all your variables...

    Code:
    Dim wb As Workbook
    If you require variable declaration (Tools | Options | Editor (tab) | Require Variable Declaration) you will find these easily enough upon code compiling.

    Secondly, I would have a test (function) to check if the workbook is open or not ...

    Code:
    Function IsWbOpen(wbName As String) As Boolean
        On Error Resume Next
        IsWbOpen = Len(Workbooks(wbName).Name)
    End Function
    So the final code might look like ...

    Code:
    Private Sub CommandButton1_Click()
        Dim wb As Workbook, rng As Range
        If IsWbOpen("BookB.xls") = False Then
            Set wb = Workbooks.Open("C:\PathHere\BookB.xls")
        Else
            Set wb = Workbooks("BookB.xls")
        End If
        Set rng = wb.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)
        If rng.Value = 0 Or rng.Value = "" Then
            rng = 1
        Else
            rng.Offset(1, 0) = rng + 1
        End If
        wb.Close SaveChanges:=True
    End Sub
    HTH
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/483900

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice