Run-time error 91
 

Run-time error 91
Object variable or With block not set

I keep getting the above error when the 2nd line of code runs. I dont
know why. I dont know if this helps but I had to use the InStr
function in order to quit getting an error in the 1st line of code.


FoundCommentCode = Trim(Left(ActiveCell.Value, InStr(ActiveCell.Value,
":") - 1))


TargetCommentCodeAddress =
Worksheets("Questionnaire").Range("P1:P2000").Find (FoundCommentCode).Address


Also I dont know if this will help but here is some other info:
This problem code is in a module in a a public sub that is called from
a Worksheet_SelectionChange precedure in the worksheet module.



Thanks in advance
Todd

Run-time error 91
 

Todd

Find returns a range object so you will need to declare your variable
TargetCommentCodeAddress as range and then 'Set' it, which you must do for
any object. The address variable needs to be declared as a string, say
AddStr

Set
TargetCommentCodeAddress=Worksheets("Questionnaire ").Range("P1:P2000").Find(FoundCommentCode)

Then you can get the address by using

AddStr=TargetCommentCodeAddress.Address

You may find that you also need to check if anything is found by using

If Not
TargetCommentCodeAddress=Worksheets("Questionnaire ").Range("P1:P2000").Find(FoundCommentCode)
Is Nothing then
AddStr=TargetCommentCodeAddress.Address
End If

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Todd Huttenstine" wrote in message
ups.com...
Run-time error 91
Object variable or With block not set

I keep getting the above error when the 2nd line of code runs. I dont
know why. I dont know if this helps but I had to use the InStr
function in order to quit getting an error in the 1st line of code.


FoundCommentCode = Trim(Left(ActiveCell.Value, InStr(ActiveCell.Value,
":") - 1))


TargetCommentCodeAddress =
Worksheets("Questionnaire").Range("P1:P2000").Find (FoundCommentCode).Address


Also I dont know if this will help but here is some other info:
This problem code is in a module in a a public sub that is called from
a Worksheet_SelectionChange precedure in the worksheet module.



Thanks in advance
Todd

Run-time error 91
 

Nevermind, I finally found out what it was. I had formulas in the
cells in Column P instead of real values. I change the formulas to
values and it worked.