Excell Cell referencing problem

[chris-uk-lad]

Hi,

I have a problem with a line of code which checks against 2 cells and searches for it in all the worksheets in the workbook which works fine in the most case however, it seems to be looking for a likeness, not the exact value.

Set foundCell = .Range("S:S").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("S" & Count))

This should return:
01_Access/aa1242

But instead returns:
01_Access/aa12

How do i make it so it picks up the accurate value, and not the first one that looks like it?

Thanks

(for reference, ws.Cells(newCount, 4).Value = 01_Access/dd12)

 

[chergh]

You need to specify the lookat parameter.

Set foundCell = .Range("S:S").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("S" & Count), lookat:= xlwhole)

 

[chris-uk-lad]

You need to specify the lookat parameter.

Set foundCell = .Range("S:S").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("S" & Count), lookat:= xlwhole)

Tried but to no avail, even lookat:=xlpart didnt work right. Your solution just returned NO for nearly everything

If Not foundCell Is Nothing Then
ws.Cells(newCount, 5) = oneSheetName
Else
ws.Cells(newCount, 5).Value = "No"

Ive tried making it so it needs to abide by the length of the first cell its referencing, but then that causes problems with cells that have more than one reference inside it as it doesnt read after the first.

why wont it just pick the cell exactly! instead of a likeness

 

[chergh]

Can you post all your code please, or upload your spreadsheet.

 

[chris-uk-lad]

Sub mappingReference()
 
    On Error GoTo ErrorHandler:
 
    Dim ws As Worksheet, SheetNames() As Variant, oneSheetName As Variant
    Dim seekForCell As Range, foundCell As Range
    Dim newCount As Long, Count As Long, LastRow As Long
 
    Set ws = ThisWorkbook.Sheets("Fields Mapped & Not Mapped")
    Count = Application.CountA(ws.Columns(1))
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    SheetNames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
    newCount = 2
 
    While newCount <= Count
        For Each oneSheetName In SheetNames
            With ThisWorkbook.Sheets(oneSheetName)
                Set foundCell = .Range("S:S").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("S" & Count))
                If Not foundCell Is Nothing Then
                    ws.Cells(newCount, 5) = oneSheetName
                    ws.Cells(newCount, 6).Value = foundCell.Offset(0, -18).Value
                    Exit For
                    Else
                        Set foundCell = .Range("T:T").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("T" & Count))
                            ws.Cells(newCount, 5) = oneSheetName
                            ws.Cells(newCount, 6).Value = foundCell.Offset(0, -19).Value
                            Exit For
                        Else
                            Set foundCell = .Range("U:U").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("U" & Count))
                            If Not foundCell Is Nothing Then
                                ws.Cells(newCount, 5) = oneSheetName
                                ws.Cells(newCount, 6).Value = foundCell.Offset(0, -20).Value
                                Exit For
                            Else
                                Set foundCell = .Range("V:V").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("V" & Count))
                                If Not foundCell Is Nothing Then
                                    ws.Cells(newCount, 5) = oneSheetName
                                    ws.Cells(newCount, 6).Value = foundCell.Offset(0, -21).Value
                                    Exit For
                                Else
                                    Set foundCell = .Range("W:W").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("W" & Count))
                                    If Not foundCell Is Nothing Then
                                        ws.Cells(newCount, 5) = oneSheetName
                                        ws.Cells(newCount, 6).Value = foundCell.Offset(0, -22).Value
                                        Exit For
                                    Else
                                        ws.Cells(newCount, 5).Value = "No"
                                    End If
                                End If
                            End If
                        End If
                End If
            End With
JumpIn:
        Next oneSheetName
        newCount = newCount + 1
    Wend
Exit Sub
ErrorHandler:
    If Err.Number = 9 Then
       Resume JumpIn
    End If
End Sub

Sorry to not upload my spreadsheet, some of the values are sensitive.

 

[chergh]

Right the find method does not search for similar values it finds values where the entire variable matches or the entire variable matches part of the value.

So if your were using the find method to search for '01_Access/aa12' if you specify xlwhole only cells that have the value '01_Access/aa12' would be found, if you had xlpart specified then '01_Access/aa1242' could be returned as could any other cell that contains that exact string.

I'm thinking your not searching for what you think you are searching for. you need to add some debugging code to find your issues liek this:

Set foundCell = .Range("S:S").Find(what:=ws.Cells(newCount, 4).Value, After:=.Range("S" & Count))
If Not foundCell Is Nothing Then
debug.print "Range S searched for value: '" & ws.Cells(newCount, 4).Value & "'"
debug.print "Range S value Found: '" & foundcell.Value & "'"
ws.Cells(newCount, 5) = oneSheetName
ws.Cells(newCount, 6).Value = foundCell.Offset(0, -18).Value

I've also included single quotes in this as i can't remeber if the find method includes white space in it's search criteria and that could be messing things up for you. Put similar statements wherever you use the find method.

The debug.print will output the values to the immediate window (press ctrl-g if this isn't visible in the VBE)

 

[chris-uk-lad]

Hi, thanks for putting the time in to help.

I took your advice and it told me what i knew really :x

Range S searched for value: '01_Access/aa12'
Range S value Found: '01_Access/aa1242'
Range S searched for value: '01_General/aa1242'
Range S value Found: '01_Access/aa1242'

Surprisingly, it had run aa12 several cells earlier and correctly could not find aa12 in the worksheets (inserting NO as id removed), though changed it afterwards once it did the search for aa1242, shown above.

 

[chergh]

Your above results are consistent with xlpart being the default value for the lookat parameter. Add 'lookat:= xlwhole' to wherever you are using the find method.

 

[chris-uk-lad]

If its correct in that i should use xlWhole, then somethings wrong with my code as it returns NO on nearly every string it searches for / leaves me at a dead end

 

[chergh]

I'm thinking you might have white space at the end of the values in some of your cells.

Pick a value that you know should be being found by the code and then in the immediate window do:

debug.print "'" & ws.range("XX") & "'"

Where XX is the cell reference of the value you know exists. You will then be able to tell if there is white space at the end of the cell as there will be a space between the value the quote mark.

 

[chris-uk-lad]

Some of the cells contain more than just the afformentioned line, and i didnt mention that this counted for aa1242

'01_Access/aa1242 (Name)'

where as the first is still

'01_Acccess/aa12'

Could this be causing an issue? aa12 does not contain a psace in front of it, as to your earlier question

 

[chergh]

You didn't think to mention this earlier?

This info makes pretty much everything above irrelevant Anyway i don't have time today to start this afresh maybe someone else can help.