Solved: Column search returning wrong results

[Adonaioc]

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.

[lucas]

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"

[Adonaioc]

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

[lucas]

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

[rory]

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.

[rory]

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

[lucas]

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?

[rory]

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?

[Adonaioc]

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

[lucas]

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.

[lucas]

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.

[Adonaioc]

Attachment 8339


there it is, BTW you guys are incredible

[lucas]

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

[Adonaioc]

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. #

[Adonaioc]

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?

[lucas]

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?

[Adonaioc]

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

[lucas]

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...

[lucas]

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.

[mdmackillop]

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]