PDA

View Full Version : [SOLVED] Is there a VBA that will search all formulas in a sheet or book



Larbec
10-11-2015, 04:42 PM
You all were so kind to find me a Macro that compares my conditional formatting which has been so beneficial plus it finds all my #REF! errors and I know where to go to fix them as it gives me everything on an output sheet.

Is there a VBA that would do the same thing for formulas that are written in cells and perhaps tell me if they are correct or not as a bonus? Basically go through my sheet(s) and record all the formulas I am using on an "output" sheet

Thanks

SamT
10-11-2015, 07:10 PM
This creates a new sheet for each existing sheet and places the formulas, as Text, into the same cell on the new sheet as they are in the existing sheet. AFAIK, there is now way to use VBA to see if the formulas are good.


Option Explicit

Sub ListFormulas()
Dim Mysheets As Object
Dim Sht As Worksheet
Dim FormSht As Worksheet
Dim Cel As Range

Set Mysheets = Application.Worksheets

'UnComment next line after passes testing
'Application.ScreenUpdating = False

For Each Sht In Mysheets
Worksheets.Add After:=Sheets(Sht.Name)
ActiveSheet.Name = Sht.Name & "_Formulas"
Set FormSht = ActiveSheet

For Each Cel In Sht.UsedRange
If InStr(Cel.Formula, "=") = 1 Then _
FormSht.Range(Cel.Address) = "'" & Cel.Formula
Next Cel
Next Sht

Application.ScreenUpdating = True
End Sub

mikerickson
10-11-2015, 09:50 PM
The built in Error Checking routines should be able to flag any cell with a bad formula.

Larbec
10-12-2015, 05:01 AM
Thanks for replying Sam. I am trying to run the code from a book of 18 sheets. It does the 1st sheet perfectly and then I get a run time error for the "sheet name" After it runs the 1st sheet the Macro places a blank sheet with this error. Is there a way to fix this? This will do exactly like I need. I am not sure why it says sheet 6. As you can see its actually sheet 4 (not sure if that matters, I have it in a Module) OR if this is what its suppose to do....what do I do?

Thanks

14561 14562

Aflatoon
10-12-2015, 05:11 AM
Wouldn't it be easier to just copy the sheet, remove any constants and then replace = with '= instead:

Sub ListFormulas()
Dim Mysheets As Object
Dim Sht As Worksheet


Set Mysheets = Application.Worksheets


'UnComment next line after passes testing
Application.ScreenUpdating = False


For Each Sht In Mysheets
Sht.Copy After:=Sht
ActiveSheet.Name = Sht.Name & "_Formulas"
On Error Resume Next
With ActiveSheet.UsedRange
.SpecialCells(xlCellTypeConstants).Clear
.Replace "=", "'="
End With
On Error GoTo 0
Next Sht


Application.ScreenUpdating = True
End Sub

Larbec
10-12-2015, 05:18 AM
Thanks for helping! This actually copies the 1st sheet and made a duplicate and then another sheet with the formulas and then stops

14563

Aflatoon
10-12-2015, 05:27 AM
That's because you already have a sheet called "Drawn Numbers_Formulas" - probably from running SamT's code. Delete that before running the code I just posted.

Paul_Hossler
10-12-2015, 06:22 AM
I'd just start with something straight forward like this




Option Explicit
Sub ListFormulas()
Dim wsFormulas As Worksheet, ws As Worksheet
Dim rFormulas As Range, rCell As Range
Dim iOut As Long

Application.ScreenUpdating = False


'delete existing
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Formulas").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'add new one
Worksheets.Add.Name = "Formulas"
Set wsFormulas = Worksheets("Formulas")

'add headers
iOut = 1
With wsFormulas
.Cells(iOut, 1).Value = "Sheet Name"
.Cells(iOut, 2).Value = "Cell Address"
.Cells(iOut, 3).Value = "Formula"
End With
iOut = iOut + 1
'go through the sheets looking for formulas
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name = "Formulas" Then GoTo NextSheet

Set rFormulas = Nothing
On Error Resume Next
Set rFormulas = .UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rFormulas Is Nothing Then GoTo NextSheet

For Each rCell In rFormulas.Cells
wsFormulas.Cells(iOut, 1).Value = rCell.Parent.Name
wsFormulas.Cells(iOut, 2).Value = rCell.Address

If IsError(rCell.Value) Then wsFormulas.Cells(iOut, 3).Interior.Color = vbRed

wsFormulas.Cells(iOut, 3).Value = "'" & rCell.Formula

iOut = iOut + 1
Next


End With
NextSheet:
Next

Application.ScreenUpdating = True
End Sub

Larbec
10-12-2015, 06:39 AM
That's because you already have a sheet called "Drawn Numbers_Formulas" - probably from running SamT's code. Delete that before running the code I just posted.

I did not have a 2nd sheet when I ran it. When I put the new code as suggested I get this?

14565

Larbec
10-12-2015, 06:44 AM
Paul,

Thanks for chiming in and helping us

I am running this and see what results we get. I have some errands 2while it runs and will report back. It seems to be taking a longer time than the one above. Perhaps that is normal

SamT
10-12-2015, 06:44 AM
Remove all the sheets added by this thread's code.

Insert one sheet. Put this code in that sheet's Code Page and run it.

Option Explicit



Sub Test1()

Dim Mysheets As Object
Dim i As Long

Set Mysheets = Application.Worksheets
On Error GoTo ErrHandler
For i = 1 To Mysheets.Count
Cells(i, "A").Value = Mysheets(i).Name
Next i

Set Mysheets = Nothing

Exit Sub
ErrHandler:
MsgBox "Count is " & i & ", Name is " & Mysheets(i).Name

Application.ScreenUpdating = True
End Sub

Aflatoon
10-12-2015, 06:54 AM
Tried my code in Paul's workbook without error. Here's another version to test:


Sub ListFormulas() Dim Mysheets As Object
Dim n As Long



'UnComment next line after passes testing
Application.ScreenUpdating = False




For n = 1 To ThisWorkbook.Sheets.Count
Sheets(n).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = Sheets(n).Name & "_Formulas"
On Error Resume Next
With ActiveSheet.UsedRange
.SpecialCells(xlCellTypeConstants).Clear
.Replace "=", "'="
End With
On Error GoTo 0
Next n




Application.ScreenUpdating = True
End Sub

snb
10-12-2015, 07:00 AM
or


Sub M_snb()
For Each sh In Sheets
sh.Activate
ActiveWindow.DisplayFormulas = True
Next
End Sub

SamT
10-12-2015, 07:26 AM
Option Explicit




Sub Test1()

Dim Mysheets As Object
Dim i As Long

Set Mysheets = Application.Worksheets
On Error GoTo ErrHandler
For i = 1 To Mysheets.Count
Cells(i, "A").Value = Mysheets(i).Name
Next i

Set Mysheets = Nothing

Exit Sub
ErrHandler:
MsgBox "Count is " & i & ", Name is " & Mysheets(i).Name

Application.ScreenUpdating = True
End Sub


Sub ListFormulas()
Dim Mysheets As Object
Dim Sht As Worksheet
Dim FormSht As Worksheet
Dim Cel As Range

Set Mysheets = Application.Worksheets

'UnComment next line after passes testing
'Application.ScreenUpdating = False

For Each Sht In Mysheets
Worksheets.Add After:=Sheets(Sht.Name)
ActiveSheet.Name = Sht.Name & "_Formulas"
Set FormSht = ActiveSheet

For Each Cel In Sht.UsedRange
If InStr(Cel.Formula, "=") = 1 Then _
FormSht.Range(Cel.Address) = "'" & Cel.Formula
Next Cel
Next Sht

Application.ScreenUpdating = True
End Sub




Sub DeleteFormulaListSheets()

Dim i As Long
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error GoTo CleanEnd

For i = Sheets.Count To 1 Step -1
If InStr(Sheets(i).Name, "_Formulas") > 0 Then Sheets(i).Delete
Next i

CleanEnd:
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Paul_Hossler
10-12-2015, 07:32 AM
@Aflatoon --

It would seem that would generate a '_Formulas' sheet for every worksheet?

I took the OP's request #1 to mean a single output sheet



Basically go through my sheet(s) and record all the formulas I am using on an "output" sheet

Aflatoon
10-12-2015, 07:45 AM
Yes - I was basically following SamT's logic but thought it would be quite slow looping through cells one by one, and this seemed a faster option. It would be as easy to just highlight them in-place IMO.

SamT
10-12-2015, 08:35 AM
I assumed that the OP had many sheets and would want to compare the Formulas to the Sheet they applied to in an easy manner.

The probable Ideal would to use both methods. Run a sheet by sheet Procedure, then run Paul's Simple_List Procedure

And Aflatoon, I agree that yours is going to be faster.

Larbec
10-12-2015, 08:43 AM
I assumed that the OP had many sheets and would want to compare the Formulas to the Sheet they applied to in an easy manner.

The probable Ideal would to use both methods. Run a sheet by sheet Procedure, then run Paul's Simple_List Procedure

And Aflatoon, I agree that yours is going to be faster.


SamT,

You are correct, I want to be able to compare each sheet. Let me fully explain. I have 5 books. 1 book has 17 sheets. Out of the 17 sheets 1o are supposed to be the same and I know they are not. I have corrected most of the CF but not the formulas. In my other 4 books they have 32 sheets with 3000 rows and 27 columns so its a lot to go through. Out of the 32 sheets 18 should be the same. Hope this makes sense.

I am a somewhat of a newbie with Macros and I am sorry to say I am very confused which Macro to use at this point lol. I do know how to use the modules and get them to run though

I noticed that some have a red shade. Does this mean I have errors in those formulas? If so, should I do a new post on suggestions how to correct them keeping in mind this is somewhat new to me but I want to learn and be self sufficient to be able to help others down the road as you are helping me

Again, thanks for all the help y'all are giving me as this has already proven to find mistakes at a glance now for me

14570

Here is an example: All the Games should have the same formulas and CF on them that I am trying to verify

14571

Paul_Hossler
10-12-2015, 10:19 AM
It looks that the results were from my suggestion

If you check G3 on DrawnNumbers, it should show some sort of error

The only way I know to determine that there's an error is like this, and I marked them in red



If IsError(rCell.Value) Then wsFormulas.Cells(iOut, 3).Interior.Color = vbRed


Others may have better ideas

Larbec
10-12-2015, 10:41 AM
I assumed that the OP had many sheets and would want to compare the Formulas to the Sheet they applied to in an easy manner.

The probable Ideal would to use both methods. Run a sheet by sheet Procedure, then run Paul's Simple_List Procedure

And Aflatoon, I agree that yours is going to be faster.

I totally agree i see benefits both ways THANK YOU BOTH!

Larbec
10-12-2015, 11:12 AM
It looks that the results were from my suggestion

If you check G3 on DrawnNumbers, it should show some sort of error

The only way I know to determine that there's an error is like this, and I marked them in red



If IsError(rCell.Value) Then wsFormulas.Cells(iOut, 3).Interior.Color = vbRed


Others may have better ideas

Thanks Paul ill scroll through all this

Do 1 of these Macros do multiple sheets in a book?

SamT
10-12-2015, 11:20 AM
Do 1 of these Macros do multiple sheets in a book?
All of them do.

Aflatoon's, Paul's snb's and mine.

Larbec
10-12-2015, 12:37 PM
It looks that the results were from my suggestion

If you check G3 on DrawnNumbers, it should show some sort of error

The only way I know to determine that there's an error is like this, and I marked them in red



If IsError(rCell.Value) Then wsFormulas.Cells(iOut, 3).Interior.Color = vbRed


Others may have better ideas

Paul,

If you do not mind help me understand because I do not see any errors. This is the other sheet that its pointing to.



Thanks

Larbec
10-12-2015, 12:43 PM
Tried my code in Paul's workbook without error. Here's another version to test:


Sub ListFormulas() Dim Mysheets As Object
Dim n As Long



'UnComment next line after passes testing
Application.ScreenUpdating = False




For n = 1 To ThisWorkbook.Sheets.Count
Sheets(n).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = Sheets(n).Name & "_Formulas"
On Error Resume Next
With ActiveSheet.UsedRange
.SpecialCells(xlCellTypeConstants).Clear
.Replace "=", "'="
End With
On Error GoTo 0
Next n




Application.ScreenUpdating = True
End Sub


I placed this in a Macro and could not run it. I could not even find it when I went to assign the Macro. What am I missing? Thanks

Larbec
10-12-2015, 12:45 PM
or


Sub M_snb()
For Each sh In Sheets
sh.Activate
ActiveWindow.DisplayFormulas = True
Next
End Sub


wow!!! Lightening speed

Paul_Hossler
10-12-2015, 12:46 PM
They're there.

I removed all Fill and Conditional Formatting and set the font to black

I don't know if the errors are a result of a link that I don't have, but do what I did and see on your machine

Larbec
10-12-2015, 12:48 PM
Option Explicit




Sub Test1()

Dim Mysheets As Object
Dim i As Long

Set Mysheets = Application.Worksheets
On Error GoTo ErrHandler
For i = 1 To Mysheets.Count
Cells(i, "A").Value = Mysheets(i).Name
Next i

Set Mysheets = Nothing

Exit Sub
ErrHandler:
MsgBox "Count is " & i & ", Name is " & Mysheets(i).Name

Application.ScreenUpdating = True
End Sub


Sub ListFormulas()
Dim Mysheets As Object
Dim Sht As Worksheet
Dim FormSht As Worksheet
Dim Cel As Range

Set Mysheets = Application.Worksheets

'UnComment next line after passes testing
'Application.ScreenUpdating = False

For Each Sht In Mysheets
Worksheets.Add After:=Sheets(Sht.Name)
ActiveSheet.Name = Sht.Name & "_Formulas"
Set FormSht = ActiveSheet

For Each Cel In Sht.UsedRange
If InStr(Cel.Formula, "=") = 1 Then _
FormSht.Range(Cel.Address) = "'" & Cel.Formula
Next Cel
Next Sht

Application.ScreenUpdating = True
End Sub




Sub DeleteFormulaListSheets()

Dim i As Long
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error GoTo CleanEnd

For i = Sheets.Count To 1 Step -1
If InStr(Sheets(i).Name, "_Formulas") > 0 Then Sheets(i).Delete
Next i

CleanEnd:
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub




Sam,

Like I explained, I am new at this so excuse me for asking. Do I place each of these in a separated module and run separately? Will this give me individual sheets if I run them in a book of 30 sheets?

Thanks

Larbec
10-12-2015, 12:57 PM
or


Sub M_snb()
For Each sh In Sheets
sh.Activate
ActiveWindow.DisplayFormulas = True
Next
End Sub


Snb,

This Macro is fast but it messed up all my sheets. Now all I have are formulas in the cells and it took away the actual numerical vaules. Here is a snip of how it looks

14575

snb
10-12-2015, 01:01 PM
I know how it looks, that's why I posted it.
to 'dismess' things:


Sub M_snb()
For Each sh In Sheets
sh.Activate
ActiveWindow.DisplayFormulas = false
Next
End Sub

If you want to check formulae you need them to be readable.....

Another approach:


Sub M_snb()
On Error Resume Next
For Each sh In Sheets
For Each cl In Cells.SpecialCells(-4123, 16)
c00 = c00 & "|" & sh.Name & "_" & cl.Address(0, 0) & "_" & cl.Formula
Next
Next
If c00 <> "" Then
sn = Split(Mid(c00, 2), "|")

Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End If
End Sub

Paul_Hossler
10-12-2015, 01:02 PM
On an unrelated note, I did notice that you have a gazillion styles in the WB, probably from a lot of copy/pasting. They do take up space and time, you can put the little macro below in a standard module and run it





Option Explicit
Sub CleanupStyles()
Dim i As Long

On Error Resume Next

For i = ActiveWorkbook.Styles.Count To 1 Step -1
If Not ActiveWorkbook.Styles(i).BuiltIn Then
ActiveWorkbook.Styles(i).Delete
End If
Next i


End Sub

Paul_Hossler
10-12-2015, 01:07 PM
After I ran the styles cleanup it went from 16,491 styles in 1.75 MB down to 47 styles in 32 KB

And they were easier to navigate also

SamT
10-12-2015, 02:42 PM
Sam,

Like I explained, I am new at this so excuse me for asking. Do I place each of these in a separated module and run separately? Will this give me individual sheets if I run them in a book of 30 sheets?

Thanks
They all do separate things.

Test1 should go in the code page of a new blank worksheet. It just tests that there are no problems with the custom collection "MySheets". IF it runs and you get a list of all the sheet names in the book, it proves the problem is not with "MySheets" or any names in the book. If you get a Mesage box, it should tell which sheet name is the problem. if it doesn't work and you don't get a message box, that clue will still help.

DeleteFormulaListSheets does what it says, so you don't have to manually delete the sheets Aflatoon's and my procedures put in the Workbook.It can go in the module with the rest of the code you're getting here. Just run it manually when needed to clean up the workbook so you can try again.

Larbec
10-12-2015, 04:13 PM
I know how it looks, that's why I posted it.
to 'dismess' things:


Sub M_snb()
For Each sh In Sheets
sh.Activate
ActiveWindow.DisplayFormulas = false
Next
End Sub

If you want to check formulae you need them to be readable.....

Another approach:


Sub M_snb()
On Error Resume Next
For Each sh In Sheets
For Each cl In Cells.SpecialCells(-4123, 16)
c00 = c00 & "|" & sh.Name & "_" & cl.Address(0, 0) & "_" & cl.Formula
Next
Next
If c00 <> "" Then
sn = Split(Mid(c00, 2), "|")

Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End If
End Sub

I agree and understand I just saved it as another file to compare to the other sheets. Thank you! I will come back and give you a reputation point to. I gave 1 earlier and it will not allow me to give another yet

Larbec
10-12-2015, 04:15 PM
On an unrelated note, I did notice that you have a gazillion styles in the WB, probably from a lot of copy/pasting. They do take up space and time, you can put the little macro below in a standard module and run it





Option Explicit
Sub CleanupStyles()
Dim i As Long

On Error Resume Next

For i = ActiveWorkbook.Styles.Count To 1 Step -1
If Not ActiveWorkbook.Styles(i).BuiltIn Then
ActiveWorkbook.Styles(i).Delete
End If
Next i


End Sub



Oh wow, never thought about that. Thanks!!!

Larbec
10-12-2015, 09:41 PM
They're there.

I removed all Fill and Conditional Formatting and set the font to black

I don't know if the errors are a result of a link that I don't have, but do what I did and see on your machine

Ah, I have those errors all over but they do not affect the output and I have no clue where or why I am getting them. I have been blanking (whiting) them out. Is that a bad thing?

SamT
10-12-2015, 11:18 PM
In rows 2 to 8 of Drawn numbers you have many cells with the formula
"='DO NOT DELETE OR ALTER!!!!!!!!!'!R2='DO NOT DELETE OR ALTER!!!!!!!!!'!(Cell Address here)'"
which in turn refers to 'D:\EXCEL SHEETS\Texas Two Step\[(Texas 2Step Macro Numbers Input).xlsm]Input!(Cell Address here)
That Cell on that Sheet in That Workbook can be a "?" or a number. If it's question mark it seems to cause a problem

For example BL2 refers to DN2 refers to DM2 refers to BK2 which has the above formula

However, those formulas are duplicated in Row 9, so they aren't the only problem. And I haven't looked to see why I don't see an error in those columns all the way down the sheet

Some formulas refer to DF, DH, and DJ which are empty columns.
For example the formula in Column AV is
=SUMPRODUCT(--MID(DF3,ROW(INDIRECT("1:"&LEN(DF3))),1))
Columns AY and BB follow the same pattern.

Larbec
10-13-2015, 03:39 AM
Sam,

"That Cell on that Sheet in That Workbook can be a "?" or a number. If it's question mark it seems to cause a problem"

I place a ? in the cell until a whole number is placed in that same row in the "Drawn Numbers" sheet. Should I use something different than a (?)? That has been the reason I have been blanking the cells out with an IFERROR or ")) so I did not see the errors but you are indicating they are truly an error that may be causing an issue with my sheets and outcome of some of my answers perhaps? I can provide a smaller book with all the pages minus the extra games if if you think that would be best for you to look at. That way you will have all the sheets within that book. There are 4 to 5 "other" external books that link to this main "Input" book but if I can get help narrowing it down to which book my battle with errors may end as I can figure it out hopefully much easier. Let me know if you think that would help. If so I will start building a smaller book to upload

"Some formulas refer to DF, DH, and DJ which are empty columns"

My fault, the sheet I uploaded was for a 4 ball plus BB Game. Those empty cells are for a 5 ball plus BB Game. Ive added the Number 5 ball Game to this file to see if this helps.

Thank you so much for helping me. It is much appreciated

Larbec
10-13-2015, 03:43 AM
They all do separate things.

Test1 should go in the code page of a new blank worksheet. It just tests that there are no problems with the custom collection "MySheets". IF it runs and you get a list of all the sheet names in the book, it proves the problem is not with "MySheets" or any names in the book. If you get a Mesage box, it should tell which sheet name is the problem. if it doesn't work and you don't get a message box, that clue will still help.

DeleteFormulaListSheets does what it says, so you don't have to manually delete the sheets Aflatoon's and my procedures put in the Workbook.It can go in the module with the rest of the code you're getting here. Just run it manually when needed to clean up the workbook so you can try again.

Now that I had some "me" time away from the family, what you provided is exactly what I needed and can easily be executed in helping me to determine errors and what may be missing from some of the other Game sheets! Thanks

SamT
10-13-2015, 09:37 AM
"That Cell on that Sheet in That Workbook can be a "?" or a number. If it's question mark it seems to cause a problem"

The Formula on sheet Drawn Numbers in the Attachment refers to sheet DO NOT DELETE OR ALTER!!!!!!!!!

The the referred to formula on Sheet DO NOT DELETE OR ALTER!!!!!!!!! refers to the Workbook Texas 2Step Macro Numbers Input Sheet Input.

Now you're telling us that the Drawn Numbers sheet is for a 4 ball plus BB Game.

Something, (or somebody,) is mixed up.


I place a ? in the cell
In the Texas 2Step Workbook?

until a whole number is placed in that same row in the "Drawn Numbers" sheet
That is not what the Formulas say. The formulas show that the "?" or number originate in the Texas 2Step Workbook.


That has been the reason I have been blanking the cells out with an IFERROR or "))
Sorry, There are no IFERROR's in this workbook.



I'll bet that by now you have half-a-dozen copies of this Workbook saved. I get the same problem working on some workbooks.

Copy the Original book, and move the copy (Named "Copy of " + original Name to another folder. Rname it to "A " + "Copy of " + original Name. That will keep it at the top of the folder. This folder in now your working folder.

Insert a sheet at the front and name it Book Version. Make any changes and Save As original Name + " v 1.0.0". Not as Copy of Name

In Book Version, Cell A1, type Version 1. In B2, note the usage and changes. Save and close
Never again alter v 1.0.0, just make a copy and name it Name + "v 1.1.0". do what you do to v 1.1.0, then in sheet Book Version, in Cell A2, type "Version 1.1" and in B2, note the usage and changes. Save and close. Never again alter v 1.1.0, use a copy named Name + "v 1.2.0". If you alter "v 1.2.0" name it "v 1.2.1"

When you incorporate changes into a previous version, append all the data from any deeper versions

When you make another copy of "Copy of " + original Name, name it Name + "v 2.0.0" , make your changes and save it, then follow the pattern as above.

Using the pattern "v 1.0.0," "v 1.1.0," and "v 1.1.1" keeps all the versions, (up to 3 levels deep,) in alphanumeric order in the Folder. Note that alphanumerically, "v 1." is before "v 1".

Larbec
10-13-2015, 01:54 PM
Sam,

I am honestly not confused and the last thing I want to do to you all is to be confusing. The sheet I originally posted is for the Texas Two Step 4 ball plus a Bonus Ball. I went back to that "same" sheet and added another ball to (columns DE to DJ) which now makes this "same" sheet capable of playing any 5 Ball Plus Bonus Ball Game only because you were asking about cells in post #36. You had said that "Some formulas refer to DF, DH, and DJ which are empty columns". So, I went back in and filled them in so you can see numbers in there and that the other referred cells should be reflecting on these cells. Does this make sense? OR are you saying because these are empty on the Two Step 4 ball game they were showing an error from another cell? I really do not want to be confusing

I can see where I confused you on the ? I have other Games sheets that are dedicated for 5 and 6 ball Games that I had to place an IFERROR formula in to clean up the sheet so I did not see all the ? and #VALUE! error symbols. I have "not" done that to this book yet. I was hoping to find out what was causing the errors and if I cannot then I will add the IFERROR to clean it up. Make sense now? I do have many books but I have them all straight and have folders that have the v1,2,3,4 on them but your way seems better.

okay, since you mentioned the sheet that says DO NOT DELETE OR ALTER!!!!!!!!! I have "added" the "Input" sheet to this attachment.

This is the "same" sheet I placed originally with the added "input" sheet and ball 5 data in (columns DE to DJ) The sheet that says DO NOT DELETE OR ALTER!!!!!!!!! already has the data for the 5th ball in columns N:O Perhaps this was causing an issue?

The Input book is not set up for a 5 ball game. It is set up for the Texas Two Step 4 ball game. If it needs to be set up as a 5 ball I can add numbers to column F in the
DELETE OR ALTER!!!!!!!!! book and change the Arrays

Just as a follow up. By me having (columns DE to DJ) (Drawn Numbers) empty and other cells referring to them ..... is that causing my errors? I did look and far as I can tell the "other cells" that reflect these are in AT:BC which is also that 5th ball I forgot to clean out. I am leaving the sheet as is but keep in mind I have column F in the DELETE OR ALTER!!!!!!!!! book empty so anything referring to that column can be null

What I am confused about is you mentioned that "However, those formulas are duplicated in Row 9" I cannot see what is a duplication?? Help me understand please and again I can’t thank you enough for your knowledge and patience.

Larbec
10-15-2015, 07:49 AM
On an unrelated note, I did notice that you have a gazillion styles in the WB, probably from a lot of copy/pasting. They do take up space and time, you can put the little macro below in a standard module and run it





Option Explicit
Sub CleanupStyles()
Dim i As Long

On Error Resume Next

For i = ActiveWorkbook.Styles.Count To 1 Step -1
If Not ActiveWorkbook.Styles(i).BuiltIn Then
ActiveWorkbook.Styles(i).Delete
End If
Next i


End Sub



Paul,

I tried to run this code and nothing happens. I placed it in a module and made a button which I assigned to it... has me puzzled

Paul_Hossler
10-15-2015, 01:35 PM
I tried to run this code and nothing happens. I placed it in a module and made a button which I assigned to it... has me puzzled

After running it the [Home] tab's [Styles] display should look something like this below and not have all the styles like in Post #30

Larbec
10-16-2015, 08:20 AM
After running it the [Home] tab's [Styles] display should look something like this below and not have all the styles like in Post #30

Ok. Gotcha. I thought when I run the Macro it may have changes all my font or do something to my spreadsheets. I'm still learning the ins and outs of excel. Thanks

Paul_Hossler
10-16-2015, 08:34 AM
Nope -- just deletes a style if it's not one of the built in ones