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.

 

[Micron]

Don't know if this is your issue, but by declaring the same variable again you've changed its type to a range object, so what the variable returns can't be text.

 

[aRush113]

Thanx for the reply.
No, the dims are just the two different attempts i tried to solve the issue.
I first used one then the other. Sorry i should have specified that.
Neither of them worked.

Update:
I just tried using
=IF($H5="","",$H5)
which returns the cell H5 value (that is a person's name) and works but if i use
=IF($H5="","",$H5&"APT"&H$2) ---H$2 containig a numer value so the result would be "TestnameAPT1" (no quotes)
it don't work.

 

[Micron]

Suggest you post exactly the code you have, not parts of it. That should help clear up one or two things. Are you trying to write a formula in a cell by using vba? If not, you can't use If like that in code. If not, then the formula would not be properly concatenated as you have it.

 

[Micron]

Have to go out soon. In case you're trying to set a cell formula on sheet 001 in cell I5 which refers to H5 and H2 using vba, then more like this
Sheets("001").Range("I5").Formula = "=IF($H5="""","""",$H5 & ""APT"" & H$2)"

 

[aRush113]

No the formula is already in the cell i'm reading, i just need to use the result it's finding (and put in the userform textbox) to be read as a text value when i search the row number of that value.
This is the vba code i'm using. The rFind works fine when in the cell i type the 1APT1 but it doesn't if it's generated by the formula.
i'm thinking, maybe i need to convert the fname to "text" before inputting it in the "Set rFind" code. But i don't know how.

Dim rFind As Range
Dim fname As String
Dim ContRow As Long
Dim ContCol As Long
Dim ws As Worksheet
Set ws = Sheet1
ws.Select
fname = IDtextbox
    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

ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Untested since I don't have your whole set-up, but try adding this to the .Find line

Set rFind = .Find(What:=fname, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

 

[aRush113]

Cheers m8, sorry about that, i did look for it but obviously i was,'t looking hard enough...
Thanx.

 

[aRush113]

Tried Peter's suggestion but no dice. It still goes to "else" "Not Found" msgbox.
Even if i directly input the text in the input box on the userform manually.
Is there a code to force Format the value as "text"?
Sorry for the ignorence but i'm not a real wiz at vba.

 

[Peter_SSs]

If the value is like you gave in post 1 (eg 1APT1) then that is text, there is no need to 'force' it.