# wildcard

#### Chas17

##### Well-known Member
Sub SearchENC() ' Entries Not Completed
NN = Range("NextTN").Value

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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Juan Pablo González

##### MrExcel MVP
You could use

And InStr(1, "123", Left\$(Cells(x, 4), 1), 1) > 0 Then

if I understood you correctly !

#### parry

##### MrExcel MVP
Perhaps And Left(Cells(x, 4), 1) Like  Then

#### Chas17

##### Well-known Member
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

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
UN = wildcard ' accepts 123
else
end if

#### parry

##### MrExcel MVP
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
UN = wildcard ' accepts 123
else
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
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???)
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 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,168,037
Messages
5,856,966
Members
431,841
Latest member
jaybeem ### 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.

### Which adblocker are you using?    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

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