Perform a "Find" on 2 criteria on an "or" basis

[Regouin]

Hello everyone,

I have the following problem, might be something very small which I just overlooked but I can't think of a proper solution (unless working with listboxes, but i think there is no need for it to be so complicated.

I have a worksheet with in column A all sorts of names, column B I just for some criteria for other search options with comboboxes. so it sort of looks like this

name1 A
name2 B
name3 B
name4 A
name5 B
name6 B
name7 A
name8 B
name9 B

now i want VBA to present me a list (in a new or empty worksheet) of all the names in the correct order. So

name1
name2
name3
name4
name5
name6
name7
name8
name9

now, is it possible to search on 2 criteria at once? or is there another way to work around this? I know I can work with listboxes and define ranges but that seems too complicated for this purpose.

TIA
Frank

[Zack Barresse]

Hi Frank, :hi:

Can you explain what it is that you are trying to do? Your tactic is not too clear at present. Please try describing as much detail as possible.

[Bob Phillips]

As presented, all you need is


Range("A1:A9").Copy Destination:=Worksheets("Sheet2").Range("A1")

but I think there is more to it than you tell us (so far).

[Zack Barresse]

xld - some info for ya I have edited your post to take advantage of them, hope you don't mind. :)

[Regouin]

Ok I'll try to describe it a little bit more detailed. When i reread it i figured i would be the only one that would've made sense out of it.
The only things in this case that matter are Column A and Column B. Column A tells me all sorts of names of parts and systems. Now the parts belong to a certain system. Now row B tells me if the data in column A is a part, a system or neither of them. When the data in column A is a part I put "part" in column B and when the data in column A is a system I put "system" in column B. When the data doesnt fit into any of these columns i leave it empty (dont use "empty" as a finding string, because they wont be empty later on.)
I'll try to describe a quick layout.

system1 system
part1 part
---
---
---
part2 part
---
---
---
part3 part
---
---
---
system2 system
part4 part
---
---
---
part5 part
---
---
---
part6 part
---
---
---
system3 system
part7 part
---
---
---
part8 part
---
---
---
part9 part
---
---
---

Now dont take the number of empty rows, the number of parts or the number of systems seriously because i want them to be self-adaptable.

now with this find i can put all the systems in a list


Dim ws As Worksheet, cel As Range, rng As Range, firstAddy As String
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("B1:B" & ws.Range("B65536").End(xlUp).Row)
With rng
Set cel = .Find("system", LookIn:=xlValues, _
searchorder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=False)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
Me.CBox1.AddItem cel.Offset(, -1).Value
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With


and by replacing "system" with "part" i can put all the parts in a list. But here comes what I want. I want excel to present me a list, in whatever output format (new sheet will suffice) of the systems and their relating parts. So by using the example above, I will get the following list:

system1
part1
part2
part3
system2
part4
part5
part6
system3
part7
part8
part9

I hope this clears things up a bit, if you have any further questions please feel free to ask.

TIA
frank

[Regouin]

Ok, i solved the problem, but I think the solution is a bit far-fetched for something this easy. Here is the code:



Sub sort()
Worksheets("blad2").Range("A:B").Font.Bold = False
Worksheets("blad2").Range("A:B").Font.Size = 10
Dim ws As Worksheet, cel As Range, rng As Range, firstAddy As String, i As Long
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("B1:B" & ws.Range("B65536").End(xlUp).Row)
i = 0
With rng
Set cel = .Find("system", LookIn:=xlValues, _
searchorder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=False)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
i = i + 1
With Worksheets("blad2")
.Range("A" & i).Formula = cel.Offset(, -1).Value
.Range("A" & i).Font.Bold = True
.Range("A" & i).Font.Size = 14
.Range("B" & i).Formula = cel.Row
End With
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("B1:B" & ws.Range("B65536").End(xlUp).Row)
With rng
Set cel = .Find("part", LookIn:=xlValues, _
searchorder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=False)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
i = i + 1
With Worksheets("blad2")
.Range("A" & i).Formula = cel.Offset(, -1).Value
.Range("B" & i).Formula = cel.Row
End With
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With
With Worksheets("blad2")
.Range("A:B").sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("B:B").Delete
End With
End Sub

now I let it find the ones marked system and put them in the top cells of column A. In column B i put the numbers of the row where it gets the data from. Then I do the same with the parts. Now I sort on column B to make them appear in their "natural" order.
I also make the systems bold and font size 14.
At the end I delete Column B and i am left with a nice list of my systems and parts.

[Zack Barresse]

Can you upload a sample file? I think there may be an easier way.

[Regouin]

Ok i extracted the whole thing from the main workbook, see if you can work your magic with it :)

TIA

Frank