Printable View
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
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]".
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 !!! . :(Quote:
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]".
What does the meaning of -2 or -1 ???.Quote:
I probably would have the macro enter a formula into the target cell by setting its .FormulaRC property to "=RC[-2]-RC[-1]".
I'm afraid that doesn't really answer most of my open questions either.Quote:
Originally Posted by firoz.raj
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.
They have the same meaning as the -2 and -1 in this line of code in your OP:Quote:
What does the meaning of -2 or -1 ???.
Code:Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
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.
@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.
He learned by being BLOCKED for a month or so, when I didn't see his posts
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.
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 ????????.Quote:
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.
:(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
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.Quote:
Originally Posted by firoz.raj
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? :confused:
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.:(Quote:
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? :confused:
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.
.
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.