PDA

View Full Version : Solved: Column search returning wrong results



Adonaioc
04-04-2008, 06:21 AM
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.




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


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

lucas
04-04-2008, 07:13 AM
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
04-04-2008, 07:21 AM
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
04-04-2008, 09:00 AM
is 10 the only thing that will be in the cell or is it likely to be part of a larger string?

rory
04-04-2008, 09:10 AM
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
04-04-2008, 09:11 AM
Sorry, forgot you are not using Find so ignore the last bit. Check if:
Instr(CEL, "10") > 0

lucas
04-04-2008, 09:11 AM
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
04-04-2008, 09:17 AM
You can tell it's Friday afternoon - you can just change:
CEL Like "[10]"
to:

CEL Like "*10*"
unless you are actually searching for the [] characters too?

Adonaioc
04-04-2008, 09:28 AM
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
04-04-2008, 09:32 AM
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
04-04-2008, 09:49 AM
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
04-04-2008, 10:00 AM
8339


there it is, BTW you guys are incredible

lucas
04-04-2008, 10:50 AM
I thought this was what you were trying to do......

Adonaioc
04-04-2008, 11:22 AM
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
04-04-2008, 11:49 AM
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
04-04-2008, 12:29 PM
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
04-04-2008, 01:14 PM
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
04-04-2008, 01:18 PM
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
04-04-2008, 01:27 PM
Add this right below this line:
Application.ScreenUpdating = False

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

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

mdmackillop
04-04-2008, 01:47 PM
An alternative

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

mdmackillop
04-04-2008, 01:52 PM
Steve,
You were collecting a few extra columns here
For Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 1).End(xlUp))

lucas
04-04-2008, 01:57 PM
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....

mdmackillop
04-04-2008, 02:02 PM
You're collecting 10s from column B, See my last post.

lucas
04-04-2008, 02:22 PM
dang......shame on me
Each CLL In MasterWS.Range("G3", MasterWS.Cells(MasterWS.Rows.Count, 7).End(xlUp))

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.

mdmackillop
04-04-2008, 02:43 PM
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.

Adonaioc
04-04-2008, 02:59 PM
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.

lucas
04-04-2008, 03:06 PM
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.....

lucas
04-04-2008, 03:17 PM
Here it is fixed....

Adonaioc
04-07-2008, 06:14 AM
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

mdmackillop
04-07-2008, 06:22 AM
If rows are hidden, remove the filter.
Can you repost your workbook so I can test the code later?

rory
04-07-2008, 06:33 AM
If there is no data for a filter value, then you would get that error when you try and grab the visible cells.

Adonaioc
04-07-2008, 06:37 AM
8368

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

mdmackillop
04-07-2008, 10:26 AM
Check for data before running the filter

'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

Adonaioc
04-07-2008, 11:06 AM
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.

Adonaioc
04-07-2008, 11:08 AM
8372

mdmackillop
04-07-2008, 11:35 AM
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.