CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question VBA Macro Issue In Excel Sheet

    I Simple Want Balance Needs to be (Total – Issuence).But it does not come properly in the Total Column !!!.let me know please .any help would be highly appreciated !!!.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 7 Then
    Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
    End If
    End Sub
    

    Last edited by firoz.raj; December 19th, 2010 at 08:00 AM.

  2. #2
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: VBA Macro Issue In Excel Sheet

    I don't get what you mean by "does not come properly in the Total Column"? Could you please clarify?

    I, personally, wouldn't use a macro at all for that purpose. I would simply put the appropriate formula into the cell. But I suppose you have your reasons why you do that...

    Are you aware that you are setting the balance cell to a constant value that way? That means that the value won't change if the total or issuence value changes. If I would use a macro for that at all, I probably would have the macro enter a formula into the target cell by setting its .FormulaRC property to "=RC[-2]-RC[-1]".
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  3. #3
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: VBA Macro Issue In Excel Sheet

    don't get what you mean by "does not come properly in the Total Column"? Could you please clarify?

    I, personally, wouldn't use a macro at all for that purpose. I would simply put the appropriate formula into the cell. But I suppose you have your reasons why you do that...

    Are you aware that you are setting the balance cell to a constant value that way? That means that the value won't change if the total or issuence value changes. If I would use a macro for that at all, I probably would have the macro enter a formula into the target cell by setting its .FormulaRC property to "=RC[-2]-RC[-1]".
    Simple!!! . I need Automatic calculated value of (Total - Issuence ) in the Closing Column .Please let me know the idea .Any help would be highly appreciated . Kindly find the attachment also !!! .
    I probably would have the macro enter a formula into the target cell by setting its .FormulaRC property to "=RC[-2]-RC[-1]".
    What does the meaning of -2 or -1 ???.
    Attached Images Attached Images  
    Last edited by firoz.raj; December 19th, 2010 at 07:33 AM.

  4. #4
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: VBA Macro Issue In Excel Sheet

    Quote Originally Posted by firoz.raj View Post
    I need Automatic calculated value of (Total - Issuence ) in the Closing Column .Please let me know the idea .
    I'm afraid that doesn't really answer most of my open questions either.

    The value in the Closing colomn will be calculated automatically if it is computed by a formula in the cells of that column, no macro needed. At least that's how I understand your requirements from the information I have so far.

    What does the meaning of -2 or -1 ???.
    They have the same meaning as the -2 and -1 in this line of code in your OP:

    Code:
    Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  5. #5
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: VBA Macro Issue In Excel Sheet

    Your code does not make any sense.

    If I assume that column 7 is your total column then it appears your are changing it when the user changes it. Where you need it to change when the user changes columns 5 or 6 instead.

    As others have said why not just enter a formula into the total colum of the sheet and let excel do it's thing and generate the totals for you. That is after all what Excel does best.
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: VBA Macro Issue In Excel Sheet

    @firoz.raj

    If you have a question post it in the related thread and please stop spamming my PM box. To date I have received more unwanted PMs from you than total messages from all other users combined. Additional PMs from you will be deleted without being read so do not waste your time nor mine.

    The threads are for your questions and give you a much better chance at an answer when more than one person can see the question.
    Last edited by DataMiser; December 19th, 2010 at 01:48 PM.
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VBA Macro Issue In Excel Sheet

    He learned by being BLOCKED for a month or so, when I didn't see his posts
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: VBA Macro Issue In Excel Sheet

    I added the formula you asked for to your Excel file, but attached the result to this post instead of sending it per e-mail. That way more people can benefit from it. I decided to add the formula to the SALT KEES sheet of the file because that was the only one that actually contained more than one value in the Issuence column. You should easily be able to follow the pattern and add the formula to the other sheets accordingly.

    As to the PMs: Though I'm not actually angry (yet), I mainly support DataMiser's opinion issued in post #7. Please use PMs only for things that wouldn't fit into the forum and actually are personal. Keep in mind that spamming peoples's PM box can easily result in being put on their ignore list, leading to the consequence that they wouldn't even see messages from you that might actually be personal or even important.
    Attached Files Attached Files
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  9. #9
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: VBA Macro Issue In Excel Sheet

    I added the formula you asked for to your Excel file, but attached the result to this post instead of sending it per e-mail. That way more people can benefit from it. I decided to add the formula to the SALT KEES sheet of the file because that was the only one that actually contained more than one value in the Issuence column. You should easily be able to follow the pattern and add the formula to the other sheets accordingly.
    Oh NOooooooooooo.the following code is working nice !!! . Now I simple want Closing needs to be Come Automatically in the Next Day in the opening coloumn .let me know the idea .How should I Achieve the specific Task Using Code or Formulae ????????.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 5 Then
           Target.Value = Target.Offset(0, -2) + Target.Offset(0, -1)
    ElseIf Target.Column = 7 Then
           Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
    End If
    End Sub
    Attached Images Attached Images  
    Last edited by firoz.raj; December 20th, 2010 at 07:24 AM.

  10. #10
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: VBA Macro Issue In Excel Sheet

    Quote Originally Posted by firoz.raj View Post
    How should I Achieve the specific Task Using Code or Formulae ????????.
    In Excel, you can do pretty amazing things using formulae (or, in some cases, other features accessible from the GUI). There's far less need for VBA code than some people might think. And this particular one is especially easy: The formula doesn't actually do any sort of calculation, it just references another cell.

    The attached screen shot shows cell C5 in edit mode, highlighting the referenced cell. Note that in the spreadsheet, cell references that don't contain a $ sign (which is the default) are relative. Therefore, this formula, copied to the cell below (C6) will instantly mutate into =G5, and so on for other cells. Hope you get the idea.

    BTW, where did the post containing your version of the Excel file go?
    Attached Images Attached Images  
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  11. #11
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: VBA Macro Issue In Excel Sheet

    In Excel, you can do pretty amazing things using formulae (or, in some cases, other features accessible from the GUI). There's far less need for VBA code than some people might think. And this particular one is especially easy: The formula doesn't actually do any sort of calculation, it just references another cell.

    The attached screen shot shows cell C5 in edit mode, highlighting the referenced cell. Note that in the spreadsheet, cell references that don't contain a $ sign (which is the default) are relative. Therefore, this formula, copied to the cell below (C6) will instantly mutate into =G5, and so on for other cells. Hope you get the idea.

    BTW, where did the post containing your version of the Excel file go?
    But I want to user veryfirst time Reference .if any time when I click on Opening automatic needs to be come of day before closing .No Need to always write =G4,=G5 ,=G6 ………………………..See my attachment .when you will type receving automatic total is coming .and when you will type issuance automatic closing is coming .even in any rows in the whole sheet.see the attachment please.
    Attached Files Attached Files

  12. #12
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: VBA Macro Issue In Excel Sheet

    The formula is the correct way to do this and no you don;t need to keep doing it as Excel will do what you want just like it will add 2 columns or multiply or find an average and many other such things.

    In the years I have been programing the only code I have had to write in Excel was for creating a Gant chart with color coding for data stored in an Access mdb file.
    .
    Always use [code][/code] tags when posting code.

  13. #13
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: VBA Macro Issue In Excel Sheet

    There's no need to always retype the forumla. Since cell references are relative, you can simply copy the forumla down the column once you have entered it for the first time. This is most easily done by dragging the handle at the lower right corner of the box that marks the selected cell and applies to both the Opening and Closing column. The cells containing formulae will instantly reflect changes in the cells referenced by them (unless automatic recalculation is disabled, which it is not by default).

    If you don't want the user to do the cell copying, you can fill out the desired number of cells in advance. Formulae referencing empty cells may result in 0 or other meaningless values, but if you don't want that, you can take care of that using some more "cell magic" as well. This might make the formulae a bit more bulky, but you don't necessairily need to confront the user with them at all.
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured