run time error '91' when clicking on text field 1

[aburamewolf]

Hi I'm new to VBA, I've been trying to fix the error I'm getting when creating a user form in excel, but I'm kind of stuck, the code is presented below.

What the macro should do is, once I click on inotes.text it should automatically use the "ifind" variable to search through the sheet for the account number typed on ian.text and with this obtain the row number of where the account number is.

Once I have the row number, I know if it's already listed or not, if it's listed it should pull the notes that were typed in for that customer number and create two line breaks and a time stamp on inotes.text

if not then it should time stamp inotes.text.

however it keeps coming with an run time

"error '91':

Object variable or With block variable not set"

Could someone help me see where my mistake is?

Thanks in advance!

Here's the code I have so far:

Private Sub inotes_Enter()

Sheets("inbound").Activate
ActiveCell = Cells(1, 1)

Dim timestamp As Variant
Dim x1 As Variant
Dim ifind As Variant

timestamp = Format(Now, "mmm ddd dd yyyy hh:mm:ss AM/PM")

If ian.Text <> "" Then
ifind = Cells.find(what:=ian.Text, after:=ActiveCell)
If IsEmpty(ifind) = True Then
ifind = 1
inotes = timestamp + ""
Else
ifind = Cells.find(what:=ian.Text, after:=ActiveCell).Row
inotes = Cells(ifind, 10) + (Chr(10)) + (Chr(10)) + timestamp
End If
End If
End Sub

 

[PHV]

Which line of code raises the error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aburamewolf]

Thanks, that would be:

ifind = Cells.find(what:=ian.Text, after:=ActiveCell)

and the code wrks perfectly when I input an account that is already in the "inbound" sheet but not when imputing a new account.

 

[SkipVought]

error on what statement?

What happens with the value in inotes???

Your FIND logic is flawed. The ELSE is NOTHING FOUND!

dim rFound as range

set rFound = Cells.find(what:=ian.Text, after:=cells(1,1))

if not rfound is nothing then
  'found -- now do something
else
  'NOT FOUND -- what to do???
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[aburamewolf]

in that case I would just need it to create a time stamp on inots itself and then generate a space, as:

if not rfound is nothing then
'found -- now do something
else
'NOT FOUND --
inotes = timestamp + ""
end if

hope this helps clear up what I'm trying to do.

 

[SkipVought]

Well does that clear things up for you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

So, you wanted something like this ?

Private Sub inotes_Enter()
Dim timestamp As String
Dim rFound As Range
Sheets("inbound").Activate
timestamp = Format(Now, "mmm ddd dd yyyy hh:mm:ss AM/PM")
If ian.Text <> "" Then
  Set rFound = Cells.Find(what:=ian.Text, after:=Cells(1, 1))
  If Not rFound Is Nothing Then
    inotes = Cells(rFound.Row, 10) & Chr(10) & Chr(10) & timestamp
  Else
    inotes = timestamp
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aburamewolf]

Excellent!

Thanks so much that solved the issue!

Man I need some lessons!

Thanks again!