+ Reply to Thread
Results 1 to 6 of 6

Obtain formula value in VBA

  1. #1
    Registered User
    Join Date
    05-27-2007
    Posts
    5

    Obtain formula value in VBA

    Hi all,
    I have created a macro which looks like this:

    Please Login or Register  to view this content.
    It all works really well, however I have a problem. All the cells in column "M" are Sum formulas. The macro works fine if any of the cells in column M are plain numbers, but when they are a Sum formula, I can't seem to get the macro to work. Does anyone know how I could fix this?

    Help would be much appreciated!

    Cheers.

  2. #2
    Forum Contributor
    Join Date
    11-17-2006
    Posts
    152
    Try this, not sure if it will work but making the cell value a variable then inputting it will only use the actual value not the formula

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-27-2007
    Posts
    5

    Obtain formula value in VBA

    Hey thanks for the reply Jonn,

    I understand the theory of what you have done, but the code isn't working and the script editor is telling me to debug the line of code where the variable has been declared.
    I've tried declaring the variable just after the lngRow variable has been declared , but this hasn't seemed to make any difference (not that I thought it would, but hey, worth a shot)...

    Any other ideas Jonn or anyone?

    Cheers!

  4. #4
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544
    It might be helpfull if you post the file !
    Just keep it simple !


    http://www.excelguide.eu
    In English as well as in Dutch

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    If you put
    =IF(O1<M1,"Display Amount","") in B1
    =IF(O1<M1,"Display Date","") in C1
    and filled down, you might be able to reduce the _Change routine to only show the MsgBox.

    I think the code
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.
    I'd also try substituting CStr(LngRow) for LngRow, in the Range arguments (it couldn't hurt)
    Last edited by mikerickson; 06-03-2007 at 11:39 AM.

  6. #6
    Registered User
    Join Date
    05-27-2007
    Posts
    5

    Thanks

    Cheers for the input guys, I managed to get the problem solved on another forum, if you wanna check it out, here it is:
    http://forums.microsoft.com/MSDN/Sho...83803&SiteID=1
    Thanks once again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1