Identify Number Patterns

**KjBox** · Apr 1st 2014

Re: Identify Number Patterns

Can you explain fully the condition(s) that determine values of 1 or 2 in columns L and O.

larbec · Apr 2nd 2014

Re: Identify Number Patterns

For for the purpose of sorting (searching) the 1's and 2's can be taken out and replaced with just a black line I forgot they were there as they're hidden They serve another function for crunching a percentage for how many times a certain counter is displayed

Quote from KjBox;708171

Can you explain fully the condition(s) that determine values of 1 or 2 in columns L and O.

**KjBox** · Apr 2nd 2014

Re: Identify Number Patterns

Is this what you are looking for? I have redone the conditional formatting so that the red and green fill for 1 or 2 value in columns L and O is no longer a format rule and the entire, used column is now black, The 1 and 2 values are still there.

Use the option buttons to select the type of view and pattern search you want to do.

larbec · Apr 2nd 2014

Re: Identify Number Patterns

Will check this out in a couple of hours.in on my iPad Had some personal things to deal with last night. Thanks KJ

larbec · Apr 2nd 2014

Re: Identify Number Patterns

My original workbook would be the same if I uploaded a new one. I basically deleted the data from it and added some other counter numbers and titles. I left the conditional formatting in it and that may be why it was so large

I ran the file after loading it with data. It ran fro about 3 minutes and came up with the following error in the pic

Code

[Blocked Image: http://i867.photobucket.com/albums/ab240/skyline6969/searchnotcomlete_zpseb606bd7.png]

Code

**KjBox** · Apr 2nd 2014

Re: Identify Number Patterns

How did all those 0s get there? Did you paste the values only of your data set onto the wotkbook I uploaded? Column AC is supposed to be hidden, it is used to hold the values in the black column D if you copy/paste patterns to search for from the data, if you manually enter into the 4 visible columns (AA ,AB, AD, AE) then rows of column AC within the search criteria range get a 0 when the macro runs.

Did you test on my workbook first, before adding your data?

I think you need to upload your full workbook to dropbox, I can then test with it.

**KjBox** · Apr 2nd 2014

Re: Identify Number Patterns

Looking at the picture again I see that you have filled in search criteria in column AC, that is why the essage box appeared and the macro failed. Either start afresh and re-enter the data set (values only) and keep column AC hidden, or clean up all those 0s then hide column AC and try again, with the search criteria being entered in columns AA, AB, AD and AE.

larbec · Apr 2nd 2014

Re: Identify Number Patterns

Let me double check everything, been a long night. Give me a few minutes , off work all day to play around with this

larbec · Apr 2nd 2014

Re: Identify Number Patterns

I remember what I did and yes I did unhide that column. I thought I was to place the 4 digits under the "Pattern Up Down" row 6? If I want to search for Up Down what cells do I use for the 4 digits? Doo I use AA-AB-AD-AE? That's where my confusion came in

larbec · Apr 2nd 2014

Re: Identify Number Patterns

Here is the original 1st book. The present download does not work. When I copy and paste this 1st book it keeps wanting to only give me results for the top 20 rows and sheet 2 has some garbage on it which I can delete

Code

https://www.dropbox.com/s/9l29…yfy/Bingo%201%20Book.xlsx

Code

**KjBox** · Apr 2nd 2014

Re: Identify Number Patterns

That is not the same sheet as the one you used in the picture in post #25.

I copied the data from that upload into my workbook and all worked OK. Do you understand what I mean by paste values only? Looking at the conditional formatting in your latest upload I see it is a mess, and includes a couple of the conditions from my workbook so I suspect that you have been using just a simple copy/paste.

To paste values only, select the cells that need to be copied, go to the destination cell, then there are 2 methods;

1) right click and select Paste Special and select the option 'Values' in the top section of the menu window, click 'OK'

2) Goto the Home tab on the ribbon and click the small down arrow below the Paste icon on the extreme left of the ribbon, then click 'Paste Values' in the dropdown menu.

Change the code for OptionButton2 to:

Code

Private Sub OptionButton2_Click()
    Application.ScreenUpdating = False
    Columns("B:I").Cut
    Columns("P:P").Insert Shift:=xlToRight
    For Each rCel In Range("e2", Range("e2").End(xlDown)).Offset(, -1)
        If rCel = vbNullString Then rCel = "0"
    Next
    ActiveSheet.Shapes("Button 1").Visible = False
    ActiveSheet.Shapes("Button 2").Visible = True
    Application.ScreenUpdating = True
End Sub

Display More

Better to add the zeros to the whole data set rather than just the search criteria.

larbec · Apr 3rd 2014

Re: Identify Number Patterns

I changed the Macro Do I place the digits for the Up/Down I columns AA,AB,AD,AE? If so, when I test it it came up no pattern and I used this book

I am copying and pasting values and it does not place the border when copied to a new sheet

Quote from KjBox;708330
That is not the same sheet as the one you used in the picture in post #25.

I copied the data from that upload into my workbook and all worked OK. Do you understand what I mean by paste values only? Looking at the conditional formatting in your latest upload I see it is a mess, and includes a couple of the conditions from my workbook so I suspect that you have been using just a simple copy/paste.

To paste values only, select the cells that need to be copied, go to the destination cell, then there are 2 methods;

1) right click and select Paste Special and select the option 'Values' in the top section of the menu window, click 'OK'

2) Goto the Home tab on the ribbon and click the small down arrow below the Paste icon on the extreme left of the ribbon, then click 'Paste Values' in the dropdown menu.

Change the code for OptionButton2 to:
Code
Private Sub OptionButton2_Click()
    Application.ScreenUpdating = False
    Columns("B:I").Cut
    Columns("P:P").Insert Shift:=xlToRight
    For Each rCel In Range("e2", Range("e2").End(xlDown)).Offset(, -1)
        If rCel = vbNullString Then rCel = "0"
    Next
    ActiveSheet.Shapes("Button 1").Visible = False
    ActiveSheet.Shapes("Button 2").Visible = True
    Application.ScreenUpdating = True
End Sub
Display More
Better to add the zeros to the whole data set rather than just the search criteria.
Display More

**KjBox** · Apr 3rd 2014

Re: Identify Number Patterns

When you Copy>>Paste all values and formatting (including conditional formatting) are copied to the destination cells.

When you Copy>>Paste Special>>Values only the cell values are copied.

So, as the idea is to protect the conditional formatting on the Data sheet, when you paste new data to the sheet you must paste values only.

If you want to copy some or all of that data to another sheet you need to do Copy>>Paste so that the formatting is also copied. If you copy to a sheet that already has formatting then the new formatting would be applied as well as the old. The best way to overcome that is to clear all conditional formatting on the destination sheet before pasting the new data.

The attached workbook has a third sheet named Cpy-Pst, it shows data copy/pasted from the data sheet and all formatting is there. Not sure why you were getting the "No matching pattern found" message, test this attachment and let me know how it goes. I have moved the code that adds the zeros to both Worksheet_Activate and Worksheet_Change to ensure that when new data is added the zeros are also added.

larbec · Apr 3rd 2014

Re: Identify Number Patterns

I did not know this. Thanks for explaining this to me. So, when I open a "new" sheet all formatting is on "that new sheet" it's not really blank? If so do I go into view code and delete everything? I've seen all the code before on a blank sheet but was afraid to delete it thinking it deleted all my sheets and books?

"If you want to copy some or all of that data to another sheet you need to do Copy>>Paste so that the formatting is also copied. If you copy to a sheet that already has formatting then the new formatting would be applied as well as the old. The best way to overcome that is to clear all conditional formatting on the destination sheet before pasting the new data."

**KjBox** · Apr 3rd 2014

Re: Identify Number Patterns

If you create a new sheet in a workbook then that sheet has no conditional formatting no matter what formatting exists on other sheets in the workbook. If you copy>>paste to that new sheet from a sheet that has conditional formatting (for the copied range) then the condition rules are pasted to the new sheet. Copy>>Paste Special>>Values will paste only the cell values of the copied range and not any of the formatting.

larbec · Apr 3rd 2014

Re: Identify Number Patterns

Okay, that's what I thought. Then why when I copy from this book and paste to a new sheet using paste special values I will only get the numbers without the colored cells. If I paste special using value and source formatting the colored cells appear but no borders and will not allow me to place a border around the cells

Quote from KjBox;708482

If you create a new sheet in a workbook then that sheet has no conditional formatting no matter what formatting exists on other sheets in the workbook. If you copy>>paste to that new sheet from a sheet that has conditional formatting (for the copied range) then the condition rules are pasted to the new sheet. Copy>>Paste Special>>Values will paste only the cell values of the copied range and not any of the formatting.

larbec · Apr 3rd 2014

Re: Identify Number Patterns

The Up?Down side is asking me to enter 5 numbers and there are only 4 in a sequence. Is it wanting the hidden cell to be an entry. Do I add the numbers under AA-AB-AD-AE? I have asked this before and so sure

larbec · Apr 3rd 2014

Re: Identify Number Patterns

That was it, I have to enter the hidden column as a digit in order for it to work. I will load my book and report back Thanks KJ!!!!

Quote from larbec;708486

The Up?Down side is asking me to enter 5 numbers and there are only 4 in a sequence. Is it wanting the hidden cell to be an entry. Do I add the numbers under AA-AB-AD-AE? I have asked this before and so sure

**KjBox** · Apr 3rd 2014

Re: Identify Number Patterns

If you use my latest upload then the column between the Up and Down gets all the blank cells filled with a zero, so the hidden column in the search criteria will never have empty cells.

larbec · Apr 3rd 2014

Re: Identify Number Patterns

correction, it keeps asking me for 5 numbers when I try to use the up / down pattern side

Quote from larbec;708486

The Up?Down side is asking me to enter 5 numbers and there are only 4 in a sequence. Is it wanting the hidden cell to be an entry. Do I add the numbers under AA-AB-AD-AE? I have asked this before and so sure

Code

http://s867.photobucket.com/us…lete_zpseb606bd7.png.html

Code

Identify Number Patterns

Participate now!

Share

Users Viewing This Thread