Check couple of charachters in a name, then print that name.

[pietrafesa]

Dear friends, first of all excuse me for my not perfect written English.
I hope you can help me, if you can.

I have an excel sheet where, in a column, there is a list of names (Amateur Radio callsigns) that refresh every two minutes.
For example, I have:
IZ7VHF
DL1GKA
0A4QXB
ZL4BKF
G0ABC
QX3RTL

.... and so on (up to 3000 callsigns).

My goal would be to check if there is one or more names that contains a specific 1st and 3rd charachter, and, if any, print somewhere this entire name, and maintain it also after column refresh.
For example, in the list above, I would check if there is a name that has 1st character "Q" and 3rd character "3".
Than, at same time, I would check if there is a name that has 1st character "0" and 3rd character "4".
So, if I put these two conditions, the function has to return the names:
0A4QXB and QX3RTL (print these two names somewhere in a cell).
When two minutes pass, and the list has been refreshed with new names, I would that these two names above are still inside the cell (0A4QXB and QX3RTL) and, if any new name in the column with same conditions are present, e.g. 0B4LKJ or QA3SSX, it has to print these new names besides old two.
Thank you for your patience in helping this old italian man.
Have a nice day.
Roberto

 

[Micron]

Not sure I can help beyond prompting you for a bit more clarity.
Will the check always be for Q & 3 and 0 & 4? If not, where will the values come from?
How do you want to cause this check to happen, sheet button click? Userform button click?

 

[pietrafesa]

Mr Micron, thank you so much for the help. I appreciate.

>>Will the check always be for Q & 3 and 0 & 4?
No, they can change, for example, I can put these values in a couple of cells somewhere: in the first cell I put first charachter, in the second cell the third one.

>>How do you want to cause this check to happen, sheet button click? Userform button click?
Sheet button may be fine.

Thank you for the time you steal to your works to help me!
Roberto

 

[Micron]

This seems to work but I think right now it's a mess. Coming from Access with its defined data types, I still marvel how in Excel vba 0 does not equal 0. I guess that's where variables are more useful than using only expressions such as Left(rng.Value, 1) = Range("D2")

Private Sub CommandButton1_Click()
Dim rng As Range
Dim Lrow As Long

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))
     If Left(rng.Value, 1) = Range("B2").Value And CStr(Mid(rng.Value, 3, 1)) = CStr(Range("C2").Value) Then
          Lrow = Cells(Rows.count, 6).End(xlUp).Row
          Range("F" & Lrow + 1) = rng.Value
     End If
     If CStr(Left(rng.Value, 1)) = CStr(Range("D2")) And CStr(Mid(rng.Value, 3, 1)) = CStr(Range("E2").Value) Then
          Lrow = Cells(Rows.count, 6).End(xlUp).Row
          Range("F" & Lrow + 1) = rng.Value
     End If
Next

End Sub

Your sheet would need to look like this:

Call Signs	Condition 1 1st	Condition 1 3rd	Condition 2 1st	Condition 2 3rd	Stored Signs
IZ7VHF	Q	3	0	4
DL1GKA
0A4QXB
ZL4BKF
G0ABC
QX3RTL

I'll see if I can clean that code up a bit if you want to play with that in the meantime. I'll add an error handler and disable/enable events as well.
EDIT - already I see a potential problem. If you run the code again, the prior values are added to the list again IF you leave the same values in (e.g. if 0 4 remains in the cells, they will be added again. I think I'd rather you just make sure you edit the conditions as needed rather than trying to prevent that.

 

[Micron]

Many things one could do to achieve a result, such as using array, creating more string variables to avoid Cstr function, offsetting from B and C to get at D and E values, etc. I think I'll stop tinkering at this:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim Lrow As Long, intCount As Integer
Dim strFind1 As String, strFind2 As String, strFind3 As String, strFind4 As String

On Error GoTo errHandler
Application.EnableEvents = False
strFind1 = Range("B2"): strFind2 = Range("C2"): strFind3 = Range("D2"): strFind4 = Range("E2")

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))
     If strFind1 = CStr(Left(rng.Value, 1)) And strFind2 = CStr(Mid(rng.Value, 3, 1)) Then
          Lrow = Cells(Rows.count, 6).End(xlUp).Row
          Range("F" & Lrow + 1) = rng.Value
          intCount = intCount + 1
     End If
     If strFind3 = CStr(Left(rng.Value, 1)) And strFind4 = CStr(Mid(rng.Value, 3, 1)) Then
          Lrow = Cells(Rows.count, 6).End(xlUp).Row
          Range("F" & Lrow + 1) = rng.Value
          intCount = intCount + 1
     End If
Next

exitHere:
Application.EnableEvents = True
If intCount = 0 Then MsgBox "No values were found"
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[pietrafesa]

Dear Micron, I will check your code next fiday, but in the meanwhile let me thank you very much indeed!
I will update you! Chapeau!
Roberto.

 

[pietrafesa]

Dear Micron, good morning! Where can I send you a file, if possible? Is it possible by mail?
Thank you.

 

[Fluff]

You will need to upload the file to a share site such as OneDrive, GoogleDrive, DropBox. Then mark for sharing & post the link you are given to the thread.

 

[pietrafesa]

You will need to upload the file to a share site such as OneDrive, GoogleDrive, DropBox. Then mark for sharing & post the link you are given to the thread.

Thank you for "teaching" me about this possibility.