VBA to find exact match within a cell

[bisetti]

I am trying to code something up in VB that will look at a cell and find the initials "AV_" and a number (which changes as the loop progresses). for example, the cells looks something like this:

Cell J1: AV_110
blah blah blah blah

cell J2: AV_10
more words here

Each cell has at least 2 lines of text but the first line is always the thing that I am trying to match. I don't want to break it out separately from the cell.

The number that it is matching is looping though (the variable name I gave it is "unqid").

I have the code looking at the cell and trying to match "AV_" & unqid but the problem is that it is coming up with false positives. For example, AV_110 is wrongly being identified as AV_1. I have tried a few ways to doing this, but unfortunately none of them are working. Any ideas?

Thanks

 

[VoG]

Please post your code.

 

[bisetti]

unfortunately its in a classified area that i cant get it out of.

 

[VoG]

Maybe you need to loop and use InStr. Without more information I cannot advise further.

 

[bisetti]

Thanks. I will give it a shot. Maybe the problem is I don't know how to use InStr. That is what I was trying to use.... it went something like

if instr(1,activecell,"AV_" & unqid) then .....

but again, this was giving me the false positives.

 

[mirabeau]

Without having much idea of what kind of output you're wanting, you might consider the following:
Suppose that Cells A1 to A3 have the indicated entries:
A1 is pAV_11q
A2 is rAV_110S
A3 is tAV_1u

Then run the following code and see if it helps at all with your ideas about INSTR.

Sub using_instr()
Dim e As Range, x As Integer
For Each e In Range("A1:A3")
x = InStr(e, "AV_")
If (x > 0) And (IsNumeric(Mid(e, x + 3, 3))) Then
    e.Select
    Exit For
End If
Next e
End Sub

 

[bisetti]

awesome! thanks so much!