wildcard

[Chas17]

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

 

[Juan Pablo González]

You could use

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

if I understood you correctly !

 

[parry]

Perhaps

And Left(Cells(x, 4), 1) Like [123] Then

 

[Chas17]

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]

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]

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