Excel 2003 "Find" is finding wrong item... 2

[DougP]

I have a sheet of Photo names in numerical order like 23.jpg or 235.jpg and some 1000 others,
this corresponds to a samples 23 or 235 so the photo of sample 23 is different a sample from 235.
But the "find" is finding looking for a 23 but returns 235 becasue the first two characters match. How can I keep it from doing that?
the photo for 235 is not the correct photo for 23

here is my code

Sub createPhotoHyperlink()
    On Error GoTo Err_Handler
    'run this 4th
    ' this sub creates a hyperlink based on the sample number
    ' like so ../photos/PE-1234.jpg
    Dim SampleNum, WEBURL, MakeHyperlink As String
    
    With Worksheets("Unit 1").Range("C8:C300")
        For a = 8 To 300
            SampleNum = Cells(a, 3).Value
            'find the sample number in the PhotoNames sheet
            Set r = Worksheets("PhotoNames").Range("b1:b1500").Find(SampleNum)
            PhotoName = Worksheets("PhotoNames").Cells(r.Row, 1)
            Worksheets("PhotoNames").Cells(r.Row, 3) = "GotIt"
            WEBURL = "../photos/" & SampleNum & ".jpg"
            MakeHyperlink = "=HYPERLINK(" & Chr(34) & WEBURL & Chr(34) & "," & Chr(34) & PhotoName & Chr(34) & ")"
            Cells(a, 15).Value = MakeHyperlink
nextrec:
        Next
    End With
    
Exit_createPhotoHyperlink:
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 91
            Resume nextrec
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
        Resume 'Exit_createPhotoHyperlink
        
End Sub

DougP

 

[SkipVought]

Look for...
[tt]
23.
[/tt]



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Sorry, that will not work either, as you could have 123. or 223. etc.

You could also test then LENGTH of the value.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pinkgecko]

Not sure if it's the best way to do it, but I tinkered with it until I got it to work. Not my If statement, While Loop, and Findnext statement inserted into code below

Sub createPhotoHyperlink2()
    On Error GoTo Err_Handler
    'run this 4th
    ' this sub creates a hyperlink based on the sample number
    ' like so ../photos/PE-1234.jpg
    Dim SampleNum, WEBURL, MakeHyperlink As String
    
    With Worksheets("Unit 1").Range("C8:C300")
        For a = 8 To 300
            SampleNum = Cells(a, 3).Value
            'find the sample number in the PhotoNames sheet
            Set r = Worksheets("PhotoNames").Range("b1:b1500").Find(SampleNum)
            If Not r Is Nothing Then
                While r <> SampleNum
                    Set r = Worksheets("PhotoNames").Range("b1:b1500").Findnext(r)
                Wend
            End If
            
            PhotoName = Worksheets("PhotoNames").Cells(r.Row, 1)
            Worksheets("PhotoNames").Cells(r.Row, 3) = "GotIt"
            WEBURL = "../photos/" & SampleNum & ".jpg"
            MakeHyperlink = "=HYPERLINK(" & Chr(34) & WEBURL & Chr(34) & "," & Chr(34) & PhotoName & Chr(34) & ")"
            Cells(a, 15).Value = MakeHyperlink
nextrec:
        Next
    End With
    
Exit_createPhotoHyperlink:
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 91
            Resume nextrec
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
        Resume 'Exit_createPhotoHyperlink
        
End Sub
[code]

[blue]When birds fly in the correct formation, they need only exert half the effort.  Even in nature, teamwork results in collective laziness.[/blue]

 

[DougP]

I found the problem,
Some of the photos are missing. for example photo 24 is missing so it finds photo 246. Phots 246 is not missing and puts it in that place. if there is a photo for a particular number it gets it just fine. It’s when there is a missing photo that there is a problem.
So I can edit those manually or just delete what it finds since there is no photo anyway.


Thank you both

DougP

 

[WinblowsME]

This is not as efficient, but it's easier for me to read.

Sub Create_Photo_Hyperlink()
   Dim u As Integer, p As Integer
   Dim sample_num As String, file_name As String, photo_name As String, url As String, h_link As String
   
   For u = 8 To 300
      sample_num = Trim(Sheets("Unit 1").Cells(u, 3))
      
      If sample_num = "" Then Exit For
      
      For p = 1 To 1800
         file_name = Trim(Sheets("PhotoNames").Cells(p, 2))
         
         ' If file_name = "" Then Exit For
         
         If LCase(file_name) = sample_num & ".jpg" Then
            Sheets("PhotoNames").Cells(p, 3) = "Got Milk?"
            photo_name = Sheets("PhotoNames").Cells(p, 1)
            url = "../photos/" & sample_num & ".jpg"
            h_link = "=HYPERLINK(" & Chr(34) & url & Chr(34) & "," & Chr(34) & photo_name & Chr(34) & ")"
            Sheets("Unit 1").Cells(u, 15).Value = h_link
         End If
      Next p
   Next u
End Sub

 

[xlbo]

why not just search for "23.jpg" ??? using lookin:=xlwhole

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DougP]

WinblowsME
that code worked perfect
Thank you Soooo Much
have a Star !!!

DougP

 

[DougP]

actually pinkgecko
yours works too, the first time I tried it I missed the "FindNEXT" I just had the Find
as in the line
Set r = Worksheets("PhotoNames").Range("b1:b1500").Findnext(r)

or something wrong.




DougP