wildcard

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Sub SearchENC() ' Entries Not Completed
NN = Range("NextTN").Value
AT = Range("AdminType").Text
UN = Range("AdminUnit").Text

z = 2

For x = 1 To NN
If Cells(x, 8) = "" And Cells(x, 5) = AT And Left(Cells(x, 4), 1) = UN Then
For y = 1 To 9
Cells(z, y + 26) = Cells(x, y)
Next y
z = z + 1
End If
Next x

End Sub

UN = Range("AdminUnit").Text is input as 0, 1, 2, or 3. If it is Zero 0, then I want Left(Cells(x, 4), 1) = UN to be true for 1, 2 or 3. I tried wild cards but must not have it right.

How can I make UN = to a wildcard char accepting 1 2 or 3?
UN = "?" ...

Chas
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Sort of. Yes that will work for 1 2 and 3. but I wanted it input to a cell named "AdminUnit", such that if the cell contained a 1, only entries from
Left(Cells(x, 4), 1) = UN starting with a 1 would show, same for 2 and 3. but if cell "AdminUnit" contained a 0, then Left(Cells(x, 4), 1) = UN would show for 1 2 and 3.

I stepped the process, probably not effitient but it works:

Sub SearchENC() ' Entries Not Completed
NN = Range("NextTN").Value
AT = Range("AdminType").Text
UN = Range("AdminUnit").Text

z = 2

If UN = 0 Then

'Unit ALL, Type ALL
If AT = "All" Then

For x = 1 To NN
If Cells(x, 8) = "" Then
For y = 1 To 9
Cells(z, y + 26) = Cells(x, y)
Next y
z = z + 1
End If
Next x
Exit Sub
End If
'Unit ALL, Type choosen
For x = 1 To NN
If Cells(x, 8) = "" And Cells(x, 5) = AT Then
For y = 1 To 9
Cells(z, y + 26) = Cells(x, y)
Next y
z = z + 1
End If
Next x
Exit Sub
End If

'Unit choosen, type ALL
If AT = "All" Then

For x = 1 To NN
If Cells(x, 8) = "" And Left(Cells(x, 4), 1) = UN Then
For y = 1 To 9
Cells(z, y + 26) = Cells(x, y)
Next y
z = z + 1
End If
Next x
Exit Sub
End If
'Unit choosen, type choosen

For x = 1 To NN
If Cells(x, 8) = "" And Cells(x, 5) = AT And Left(Cells(x, 4), 1) = UN Then
For y = 1 To 9
Cells(z, y + 26) = Cells(x, y)
Next y
z = z + 1
End If
Next x

End Sub

Thanks, Chas

I was trying to figure a way to say
if range("AdminUnit") = 0 then
UN = wildcard ' accepts 123
else
UN = range("AdminUnit").text
end if
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi Chas, your code doesnt make sense to me and is though you have repeated some code by mistake. Some things like checking if its AT in a cell then you exit sub then later you check if its AT again? The second bit of code will never run because youve exited the procedure after the first section.

If I understand you correctly your saying UN can be 0,1,2 or 3. So far so good, then you say this...

I was trying to figure a way to say
if range("AdminUnit") = 0 then
UN = wildcard ' accepts 123
else
UN = range("AdminUnit").text
end if

That doesnt make sense - both the true and false conditions result in the same thing. Do you mean if UN = 0 then do nothing? Also, why are you using the Text property instead of the Value property?
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Yes the code is repeated and works.
Cell d2 contains 1JRCEHV200
Cell d3 contains 2JRCEHV200
Cell d4 contains 3JRCEHV200

If Range("AdminUnit") = 1, then only return cell d2
etc for 2, 3. If Range("AdminUnit") = 0 then return all cells d2, d3, d4.

This is also true for another group of cells containing valve type
cell E2 = MOV
cell E3 = AOV
cell E4 = Man

If Range("AdminType") = "MOV" then only return cell E2
etc for AOV, Man. If Range("AdminUnit") = "All" then return all cells e2, e3, e4.

So in my code ( I could not figure the wildcard out ) I had to first check if
adminUnit was 0, then check if Type was ALL. Run code and exit.
If AdminUnit was not 0 then the next step I still had to check type again,
if type not "All" then run code, exit.
Next if Unit and type are not 0 / "All" , run code. yes it repeats.

(text because value did not work and text did???)
UN = range("AdminUnit").text
Left(Cells(x, 4), 1) = UN

so if UN = 1 only cell d2 returned
so if UN = 2 only cell d3 returned
so if UN = 3 only cell d4 returned

If UN = 0 then return all three...I thought UN could = a wild card to return all three.
Sorry for the confusion. The code I have works, just thought UN= wildcard would make it smaller, more effitient.

Thanks for the replies
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top