Find VBA not finding 2 digit numbers in '000' number format (eg. 020)

[benjhardie]

Hello!

I have a spreadsheet that I use to assign people lockers. The user inputs the person's name and required locker number which then finds the locker number in the male or female sheet and copies their details.

The numbers are in a 3 digit format, however my find code doesn't seem to be finding numbers that begin with 0 (e.g 020) - which only applies to the female locker numbers.

Here is the code I am using;

Sub AssignLockerBtn() 
 
Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 
Male.Unprotect Password:="" 
Female.Unprotect Password:="" 
 
'Check for missing fields 
Dim r As Range 
Dim totalCells As Integer 
Set r = ActiveSheet.Range("C4:C10") 
totalCells = r.Count - WorksheetFunction.CountBlank(r) 
If totalCells = 7 Then 
 
Dim N$, M$, S$() 
N = Range("C4") 
M = "" 
S = Split(N) 
If UBound(S) < 1 Then Call FullNameRequired 
S(0) = Left(S(0), 1) & "." 
N = Join(S) 
 
If Range("C6") = "M" Then 
Dim LockerM As Range 
Set LockerM = Male.Range("C12:AO40").Find(AssignLocker.Range("C7"), LookIn:=xlValues, Lookat:=xlWhole) 
 
    If LockerM Is Nothing Then 
    MsgBox "Invalid Locker #" 
    ElseIf LockerM.Offset(1, 0) <> "" Then 
    MsgBox "This locker is already assigned to a member. Please choose a different locker" 
    Else 
    LockerM.Offset(1, 0) = N 
    LockerM.Offset(2, 0) = AssignLocker.Range("C5") 
    LockerM.Offset(3, 0) = AssignLocker.Range("C8") 
    LockerM.Offset(4, 0) = Format(AssignLocker.Range("C9"), "DD/MM/YY") & " - " & Format(AssignLocker.Range("C10").Value, "DD/MM/YY") 
    'Success message box 
    MsgBox "Locker assigned successfully!" 
    'Clear entry fields 
    Range("C4: C9") = "" 
    Range("C4").Select 
    End If 
 
ElseIf Range("C6") = "F" Then 
Dim LockerF As Range 
Set LockerF = Female.Range("C12:Q40").Find(AssignLocker.Range("C7"), LookIn:=xlValues, Lookat:=xlWhole) 
 
    If LockerF Is Nothing Then 
    MsgBox "Invalid Locker #" 
    ElseIf LockerF.Offset(1, 0) <> "" Then 
    MsgBox "This locker is already assigned to a member. Please choose a different locker" 
    Else 
    LockerF.Offset(1, 0) = N 
    LockerF.Offset(2, 0) = AssignLocker.Range("C5") 
    LockerF.Offset(3, 0) = AssignLocker.Range("C8") 
    LockerF.Offset(4, 0) = Format(AssignLocker.Range("C9"), "DD/MM/YY") & " - " & Format(AssignLocker.Range("C10").Value, "DD/MM/YY") 
    'Success message box 
    MsgBox "Locker assigned successfully!" 
    'Clear entry fields 
    Range("C4: C9") = "" 
    Range("C4").Select 
    End If 
 
'Missing entry message box 
Else: MsgBox "Please complete all fields" 
End If 
 
End If 
 
Application.ScreenUpdating = True 
Application.Calculation = xlCalculationAutomatic 
AssignLocker.Calculate 
Male.Protect Password:="" 
Female.Protect Password:="" 
 
End Sub  

I have also attached a copy of my workbook so you can see how it all works.

Would anyone be able to help me figure out why this happening? I'd really appreciate the help!

Thank you!

[shg]

Try just searching for the number (e.g., 0 or 20 instead of 000 or 020).

[benjhardie]

Try just searching for the number (e.g., 0 or 20 instead of 000 or 020).

This doesn't work either

[Zer0Cool]

Your find seems to look for the value typed into C7. Its then looking for that in your find range.

The most likely issue is that if you are actually entering ex: "020" in C7 as a text string (IE the leading 0 appears in the formula bar and the cell and formatting istn keeping the leading 0 displayed) then it wont match as your headers are formatted to have leading 0's, they dont actually contain them. IE 020 displayed in the cell is really 20, with a format like "000" applied to it.

"020" as text and "20" formatted 000 are not the same thing thus no match.

[benjhardie]

Hey, thanks for your explanation. This makes sense in theory but even when I type "20" in C7 it doesn't find "20" in the range - even though the values in each formula bar match exactly ("20"). Both C7 and the headers are formatted the same (Custom format "000") so surely it should be finding them?

[shg]

Ran you code and had the same problem. No immediate suggestion, sorry.

[shg]

Except I would add this: I would do this completely differently: Have a database sheet (which could remain hidden) with the particulars in standard format (header row, one record per row, ...), and then generate the "report" sheets from that.

[Marc L]

Hi !

It's very not a good habit to not specify the property of the cell within a VBA code !

As the Find method well works in your case with the cell property Text on my end …

[benjhardie]

Hi !

It's very not a good habit to not specify the property of the cell within a VBA code !

As the Find method well works in your case with the cell property Text on my end …

Thanks for the advice Mark! So how do I put that into my existing code? I'm quite new to this so learning as I go.

[Marc L]

Just add the Text property to the search cell of Find method …

In order you can mod your gas factory code, first enter F as gender and a female locker # then run this demo :

Sub Demo0()
       Dim Rg As Range
       If AssignLocker.[C6].Value2 <> "F" Then Beep: Exit Sub
    With Female
       Set Rg = .UsedRange.Find(AssignLocker.[C7].Text, , xlValues, xlWhole)
        If Rg Is Nothing Then
            Beep
        Else
           .Activate
           .Unprotect
            Rg.Resize(5).Select
            Set Rg = Nothing
            Application.Wait Now + 0.00002
           .Protect
        End If
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Zer0Cool]

Does anyone else see the format of the headers on the female locker sheet as this? EDIT (also noticed C7 on assign is set to this too)

adasdasda.PNG

Im gonna clear out formatting and see if that works.

[Marc L]

Yes I saw that but as Find works as it's displayed the reason why I use the property Text, as usual, not a special case …

[Zer0Cool]

Ah ok, as @Marc L points out, and this isnt what I would have expected, find seems to be going by the displayed value, not the actual value in the cell.

IE Find is seeing numbers (really text) as '000' format, but sees C7 (the lookup value) as a number despite the format. Thus Ex: its looking for C7 = 20 (when C7 shows 020) but sees locker 20 on the female sheet as 020 (when the value is really 20 formatted '000', hence no match).

The recommendation given is likely the best way to handle this, as it instead gets the text/displayed value of the cell C7 "020" and then matches "apples to apples" in the find.

Another option is:

Please Login or Register  to view this content.

The above also worked for me but is longer to type. Conversly it may make more sense to someone else looking at it or yourself later to understand what its doing.

[Marc L]

The other option is not to format the cell but to just look for Value2 in formulas via LookIn:=xlFormulas
as the common trick for searching a date for example …

The lesson is to never use a range without a property as VBA choose one of three
(often Value but sometimes Text or Value2), a lottery !

[Zer0Cool]

LookIn:=xlFormulas

I am mad I didnt notice they had used xlValues, that explains the behavior. I habitually have it on xlFormulas so didnt even look at that.