• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Checking for consistent formulas in columns

Bonnieha

New Member
Is there a way to quickly check to make sure the same formula is being used in a column? Much like the way a column can be highlighted and using conditional formatting to find duplicates. Currently, I use formulas, show formulas and have to visually scan long columns to ensure the same formula is being used for calculation.
 

Debraj

Excel Ninja
Hi Bonnieha!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


* If in a single Column's, all formula are same, and few are different, Excel trigger you the same by giving a Error Checking.. Please check on ..Excel > Option > Formulas > Error Checking Rules > Checked. or in VBA press Ctrl + G and write >


Code:
Application.ErrorCheckingOptions.InconsistentTableFormula = TRUE

* If you select Single Column.. (i.e. Column P) and then press Alt + E + G + S + M (Go to > Special > Column Difference), It will select all the cells which are not same as currently selected cell.


Try to play with both the option, hope it will help you in some case..


Regards,

Deb
 

Faseeh

Excel Ninja
Hi Bonnieha,


Welcome to the forums,


Simply press "Show formula" in excel 2007 and they will show up so that you can check them. :)


Faseeh
 

Hui

Excel Ninja
Staff member
Or you can use an Excel Addin like John Walkenbach's PUP which has a function that shows the complexity of cell formulas

http://spreadsheetpage.com/index.php/pupv7/home
 

Faseeh

Excel Ninja
Hi Deb,


I was also trying with this code can you tell me what went wrong with it: (Plz donot laugh while reading :D

[pre]
Code:
Sub CheckConsistentFormula()
Dim i As Range
Set i = Worksheets(1).Range("A1:B6")
For Each cell In i
If ActiveCell.Offset(1, 0).Formula = ActiveCell.Formula Then ActiveCell.Offset(1, 0).Interior = RGB(100, 10, 10)
ActiveCell.Offset(1, 0).Activate
Next cell
End Sub
[/pre]

Regards,

Faseeh
 

NARAYANK991

Excel Ninja
Hi Faseeh ,


Two points :


1. It gives an error message at the statement : ActiveCell.Offset(1, 0).Interior = RGB(100, 10, 10) , which can be corrected by changing it to : ActiveCell.Offset(1, 0).Interior.Color = RGB(100, 10, 10)


2. It does not do what you think it should be doing.


Make the change shown in (1) and then you can start modifying it to do what you want it to do.


Narayan
 

Debraj

Excel Ninja
Hi Faseeh..


* First of Thanks for your interest and welcome to the VBA Zone.

[list type=decimal]
[*]From Programming point of View:

1.very nice approach.

2.Just use one more level of hierarchy to set color .Interior.Color

3.Try to avoid activate / Select for faster processing.

4.You have set I as Range, but you are checking ActiveCell
.. Both may not be same. Try to understand it properly or SELECT/ACTIVATE before proceed


From Logical point of View:

1.Excel is a very popular and nicely coding world accepted and most advanced software.

2.In Programming, the hardest part is ERROR CHECKING, and excel is very much good in this.

3.It automatically adjust Cell references according to drag,
Code:
=Sum(B1:B3) and [code]=Sum(C1:C3)
both are look a like formula, but from Textual point of view, both are not same. But excel knows it well and checked in perfectly.

[/list type=decimal]

*I would like to suggest to take help of EXCEL rather than adapt the same by personal codding.

[pre]Sub Macro1()
Selection.ColumnDifferences(ActiveCell).Select
Selection.Interior.Color = RGB(100, 10, 10)
End Sub[/code][/pre]

Regards,

Deb
 

shrivallabha

Excel Ninja
Hi Faseeh,


Open a blank workbook and then go to Excel Options | Formula | check option 'R1C1 reference style'. And write simple formula in C1 to add A1 and B1 which will look:

=RC[-2]+RC[-1]

It means stay on the same row but look 2 and 1 columns to left respectively.


Now fill down this formula to C10. You will be amazed to see that formula stays the same. Now copy formula from C1:C10 to D1:D10 and still formula remains the same. This is how Excel probably interprets formulas internally for processing data.


Now go to Excel Options | Formula | uncheck option 'R1C1 reference style' and you will see that formulas are converted to what we see everyday i.e. A1+B1 in C1 and B10+C10 in D10. For us, it is easier to read A1 styled formulas.


So use ActiveCell.FormulaR1C1 [R1C1 styled notation] for comparison in VBA rather than Activecell.Formula [A1 styled notation] as it is evident that A1 styled notation will change for every cell though intrinsically formula is not changing.
 

Faseeh

Excel Ninja
@ Narayan,


Thanks you!


@ Deb,


Thank you for welcome. Your first point is clear. I have understood that. Point # 2: what i have understood that if i try to differentiate between cells on the basis of formulas then it will ignore changing cell references? Is that correct.


I do use the excel help but it will help some time to get expertise in it. :) I will keep asking until i completely grip it and the macro you write is working. :p


@ shrivallabha,


Well, it was a bit hard to digest, i am not familiar with R1C1 style working, neither i have found it in J Walk's book i am reading so i will have to practice it. I will revert with further questions, let me get acquainted with it first.


Thanks all,


I will revert with questions on some other thread.


Faseeh
 
Top