Solved: Column search returning wrong results

This is my code, it works fine for 1-9 but 10 and 11 return results for 1 and 12 returns results for 1 and some of 2. I would appriciate any tips.



[VBA]
Sub CompileM10()
'Selects cells containing "10" in column g
Sheets("Master").Select
Range("A1").Select

Dim Col As Integer
Dim CEL As Range
Dim SelRange As Range
For Col = 7 To 7 'columns g through g
For Each CEL In Range(Cells(1, Col), Cells(65536, Col).End(xlUp))
If CEL Like "[10]" Then
If SelRange Is Nothing Then
Set SelRange = CEL
Else
Set SelRange = Union(SelRange, CEL)
End If
End If
Next CEL
Next Col
SelRange.Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveWindow.SmallScroll Down:=9

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("M10").Select
Range("A1").Select
ActiveSheet.Paste

Selection.sort Key1:=Range("G1"), Order1:=xlAscending, Key2:=Range("E1") _
, Order2:=xlAscending, Key3:=Range("F1"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub
[/VBA]

Edit Lucas: Adonaioc, when posting code...select the code and hit the vba button.

Hi Adonaioc,
I'm missing something from your description. Could you possibly remove any sensitive info from the workbook and post it. Add a few notes to it so that whoever has time to look at it can understand exactly what you are trying to do.

go to the button that says post reply at the bottom left of the last post. When the editor loads scroll down and look for the button that says "manage attachments"

I cant post the sheet, removing said materials would leave not much of a sheet, but this script is supposed to search column g on sheet "master" for "10" and select the row that "10" is contained in and then copy it to a sheet called M10. I used the same script for the M1-M9 sheets and it works great, but for some reason after running it for M10, sheet M10 is compiled of column g values 1 and 0 not "10" M12 returns 1 and 2 not "12". i hope that explains my problem

is 10 the only thing that will be in the cell or is it likely to be part of a larger string?

That's the way Like works - if you include the square brackets it thinks you are giving it a range of characters to check - so your comparison is actually:
is 1 like [1 or 0]
etc.
if you want to check the contents, specify "10" as the search and specify Lookat:=xlPart.

Sorry, forgot you are not using Find so ignore the last bit. Check if:
[VBA]Instr(CEL, "10") > 0[/VBA]

There seems like your code could be improved considerably if we knew exactly what you were trying to do.

You appear to want to move all of each row with a specific number in col g to a sheet that is named the same as the number....but you are using a different script for each number....? is that close?

You can tell it's Friday afternoon - you can just change:
[VBA]CEL Like "[10]"[/VBA]
to:
[VBA]
CEL Like "*10*"[/VBA]
unless you are actually searching for the [] characters too?

yes that is precisely what i am doing, i have a list of rows each one is routed to a different team, i want to select all the rows with "n" (n being 1,2,3,4,5,6,7,8,9,10,11,12,13) in column G and move them to sheet M"n" but the all cell background color must be transfered as well. then from sheet all sheets M"n" A Daily Schedule is compiled team 1,2,3 etc. I know its not the best written code, any improvements would be appriciated. I tried to upload it but it is 1.86mb and the limit is 1

Hi rory, I was thinking there must be some way around all of the selecting, etc. too......

Adonaioc, zip your file and you should be able to post it.

This is more what I thought you might be looking for. It doesn't create the new sheets from a variable but if you know what numbers you will be using it is easy to add them in the code.

Attachment 8339


there it is, BTW you guys are incredible

I thought this was what you were trying to do......

I cant view your attachment, my post count is not high enough.

EDIT: I got it, that is GREAT, that is exactly what i want, is there any way to clear those M1,M2,M3... pages and refresh them with the new data entered on the master, when the macro is clicked. and then bring them to the daily sched, by mach. #

also for some reason M10 is collecting data from 3 4 5 8 9 and 11, why is that?

EDIT: Oh I see its searching all cells, is there a way to make it only search column G?

Quote:

---

is there any way to clear those M1,M2,M3... pages and refresh them with the new data entered on the master

---

You mean delete them and start over?

yeah or just clear them and refill them. whatever is easier because the master is always changing and those pages will be different everyday

hmm.....I can't put my finger on what is going on with number 10.....anyone have any ideas.

and Adonaioc it is only looking in column G...

Add this right below this line:
Application.ScreenUpdating = False

[VBA]Application.DisplayAlerts = False
Sheets(Array("M12", "M11", "M10", "M9", "M8", "M7", "M6", "M5", "M4", "M3", _
"M2", "M1")).Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True[/VBA]

Will delete the old sheets and let the script run with updated new sheets.

An alternative
[vba]
Option Explicit
Option Base 1
Sub CopyDataToNewWorksheets2()
Dim CLL As Range, MasterWS As Worksheet, DestWS As Worksheet
Dim arr(12), i As Long
Dim Rng As Range, LRw As Long
'Turn off ScreenUpdating for faster macro runtime so screen won't flash while running
Application.ScreenUpdating = False
'Set variables
Set MasterWS = Sheets("Master")
MasterWS.Rows("1:2").Copy
Application.DisplayAlerts = False
For i = 1 To 12
'Delete previous version of sheet
Sheets("M" & i).Delete
' Add worksheets M1, M2, M3, M4 after worksheet Master
Worksheets.Add(After:=Worksheets("Cable List")).Name = "M" & i
'Copy header row from Master to each of the new worksheets
MasterWS.Rows("1:2").Copy Range("A1")
'Create array of sheet names for use later
arr(i) = "M" & i
Next
Application.DisplayAlerts = True

'Filter column G of Master for each value 1 to 12, Copy visible rows to the appropriate sheet
With MasterWS
LRw = .Cells(Rows.Count, 7).End(xlUp).Row
Set Rng = .Range("G2:G" & LRw)
'Copy selected data
For i = 1 To 12
Rng.AutoFilter Field:=1, Criteria1:=CStr(i)
.Range("A3:K" & LRw).SpecialCells(xlCellTypeVisible).Copy
Sheets("M" & i).Range("A3").PasteSpecial xlAll
Next
Rng.AutoFilter

'Fix column widths
Sheets(arr).Select
Sheets("M12").Activate
Range("A1").Activate
For i = 1 To 12
Cells(1, i).ColumnWidth = .Cells(1, i).ColumnWidth
Next
.Activate
End With

'Turn ScreenUpdating back on
Application.ScreenUpdating = True
'Release memory reserved for variables
Set CLL = Nothing
Set MasterWS = Nothing
Set DestWS = Nothing
End Sub


[/vba]

Steve,
You were collecting a few extra columns here
[VBA]For Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 1).End(xlUp))[/VBA]

Malcolm, do you have any idea why the number 10 is not working in the code I tried.....just a mystery to me.......it is in the first module of the file posted at 13....

You're collecting 10s from column B, See my last post.

dang......shame on me
[VBA]Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 7).End(xlUp))[/VBA]

thanks malcolm

For the original poster.....Malcolm's code is much cleaner....I like mine because I use it often and am (theoretically)more familiar with it.

No problems with your version Steve, but loops can be slow with many rows. eg checking 20,000 rows 12 times for each value, so I though I'd toss in a filter solution.
For another variation with sorted data, you could find the first and last positions of each value and use that to define the rows to be copied.

Lucus, the script is pulling all those random lines in M10 because in the operation column, there is a 10, and one was there because the quantity column was 10, somehow its pulling from all cells.

Adonaioc, I'm sorry I doubted you.....I missed that and you were right.

Guess if you have any more questions you will ask......Malcolm has explained the advantage of his code.....

Here it is fixed....

Malcolm, i tried to use your script but i got an error and it did something to the couple hundred rows, they are now missing, and I cannot unhide them.

runtime error 1004 no cells found

when i hit debug it highlights

.Range("A3:K" & LRw).SpecialCells(xlCellTypeVisible).Copy


i dont know how to get the cells back either

If rows are hidden, remove the filter.
Can you repost your workbook so I can test the code later?

If there is no data for a filter value, then you would get that error when you try and grab the visible cells.

Attachment 8368

Here it is I cleared out my old macros, the one Malcolm posted is copytoneworksheets2

Check for data before running the filter
[VBA]
'Copy selected data
For i = 1 To 12
If Application.CountIf(Rng, i) > 0 Then
Rng.AutoFilter Field:=1, Criteria1:=CStr(i)
.Range("A3:K" & LRw).SpecialCells(xlCellTypeVisible).Copy
Sheets("M" & i).Range("A3").PasteSpecial xlAll
End If
Next

[/VBA]

That works great now, one more question, I have it set to copy the first 30 rows from each M* sheet to the daily sheet under the appropriate machine, but I want a way to limit how many rows it pulls. for instance, column J totals the run time per machine, say we are running a 8 hour day how can i tell it to pull only until the total for that machine in column J jumps above 8 hours then stop. say M5 looks like this

job1 2 hours
job2 4 hours
Job3 1.5 hours
job4 3 hours pull this job but no more
job5 13 hours
job6 4 hours

totalling 10.5 hours but do not stop pulling to the daily sched maching 5 list until the total in column j is greater than 8

but I still need it to pull in larger jobs like the 13 hours one even though it alone is greater than 8 hours, when it is the first one on the list.

I hope that makes sense.

Attachment 8372

Your post 34 is a bit of a logic puzzle. Please take the time to make things simple for me to follow. Add comments/highlight your sample showing what should be copied and to where.