Formula generated ID value result is not read as text in vba.

[aRush113]

I'm using this formula to generate an ID value

=IF($H5="","",ROW()-4&"APT"&H$2)

it returns, based on the cell's values, a code like 1APT1, 1APT2, 1APT3 etc... or 2APT1, 3APT2... blah, blah, blah...
I then Find and Load these codes in a vba userform TextBox.

The problem is that for some reason when i try to use this TextBox value to return it's Row or Column number to modify data it does not find it.
If i type the exact code manually into the cell it works but the formula generated code does not.

In the vba code i tried:

dim fname as String
dim fname as Range

fname = IDbox

but the Find routine doesn't read it as "plain text"

What am i doing wrong?

Thank You in advance for any help.

 

[Alex Blakenburg]

We can't see what you have in H5 or H2.
After the line fname = IDtextbox
put this line and show us what you see in the immediate box (Ctrl+G if you can't see the immediate window)

Debug.Print "fname ", fname, Len(fname)

 

[Micron]

OP says they want to put what is found in a userform textbox but to me, fname = textboxname is doing it backwards? Also a range object is being set, but I don't see any code that attempts to pass a value to userform textbox. Besides, there might be something that was omitted from the posted code because as it is, it would not even compile. Missing parts might be misleading everyone. Look at the code when what was given is indented as it should be:

With Range("B5:EY10000")
    Set rFind = .Find(What:=fname, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        ContRow = rFind.Row
        ContCol = rFind.Column
        ws.Unprotect
        With Me
        ' do something here

        On Error Resume Next
End With '<<<<
    Else
        MsgBox "Not found"
    End If
End With '<<<<

 

[aRush113]

Micron i will try your suggestion and let you know...

I'm totally changing my approach.

Just to explain what i'm trying to achieve:

I have a booking sheet with checkin and checkout dates, and other data that i insert in a single row using a UserForm.
The booking obviously needs to be unique, hence the ID value that was automatically generated by the formula in my first post ("1APT1") which is the "Room Number" (there are 5 rooms) "APT" which is short for "Appartment" and the first, second, third etc. booking for that room.
If i need to modify an existing booking i need to find the unique ID to modify it's data.

My new approach now is inserting the "1APT1" text value in the cell when i create the booking. (This IS read correctly).

In my "Create New" code i added:

Dim IDtext As String
Dim IDrow As String  'Instead of "As Integer"
Dim IDcam As String  'Instead of "As Integer"


                    IDrow = lastRow - 1 'lastRow is defined previously
                    IDcam = CamNum 'CamNum is the room number textBox value from the UserForm

                IDtext = IDcam & "APT" & IDcam
                .Cells(nextrow, cCol - 5).Value = IDtext ' "1APT1"

It seems to work fine.

For me it's problem solved.
Don't know if i should mark the thread as solved or not... in the sence that it could be useful to find out why the previous method wasn't working.

 

[aRush113]

OP says they want to put what is found in a userform textbox but to me, fname = textboxname is doing it backwards? Also a range object is being set, but I don't see any code that attempts to pass a value to userform textbox. Besides, there might be something that was omitted from the posted code because as it is, it would not even compile. Missing parts might be misleading everyone. Look at the code when what was given is indented as it should be:

With Range("B5:EY10000")
    Set rFind = .Find(What:=fname, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        ContRow = rFind.Row
        ContCol = rFind.Column
        ws.Unprotect
        With Me
        ' do something here

        On Error Resume Next
End With '<<<<
    Else
        MsgBox "Not found"
    End If
End With '<<<<

Tried your suggestion. Nothin'

Thanx to everyone that has taken the time, and the patience, to help me out.
God Bless.