I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

It doesn't work. :( I tried with and without the ".value")
I couldn't duplicate this in a new workbook either. So I checked this
workbook specifically:

-Range "MYRANGE" is correctly defined
-cboPhone_Number lists only options referenced to the contents of row 3
-the phone number I look up DOES have a corresponding Name 2 above it

What else can be wrong with it?

Die_Another_Day wrote:
I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

Keep NAME dimmed as a range, make sure there is no .Value after the
find, then place a break point on the Set Name = Blah Line. Query the
row of the find in the immediate window like this:
?Range("MYRANGE").Find(What:=Me.cboPhone_Number.Va lue).Row
Let me know what the returned row is. If you would like you can also
send me the worksheet to my posting address.

Charles

wrote:
It doesn't work. :( I tried with and without the ".value")
I couldn't duplicate this in a new workbook either. So I checked this
workbook specifically:

-Range "MYRANGE" is correctly defined
-cboPhone_Number lists only options referenced to the contents of row 3
-the phone number I look up DOES have a corresponding Name 2 above it

What else can be wrong with it?

Die_Another_Day wrote:
I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

the first number comes up as 6 then all the rest as 5
even though all show as 10 on my sheet
?


Die_Another_Day wrote:
Keep NAME dimmed as a range, make sure there is no .Value after the
find, then place a break point on the Set Name = Blah Line. Query the
row of the find in the immediate window like this:
?Range("MYRANGE").Find(What:=Me.cboPhone_Number.Va lue).Row
Let me know what the returned row is. If you would like you can also
send me the worksheet to my posting address.

Charles

wrote:
It doesn't work. :( I tried with and without the ".value")
I couldn't duplicate this in a new workbook either. So I checked this
workbook specifically:

-Range "MYRANGE" is correctly defined
-cboPhone_Number lists only options referenced to the contents of row 3
-the phone number I look up DOES have a corresponding Name 2 above it

What else can be wrong with it?

Die_Another_Day wrote:
I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue,
MatchCase:=True).Offset(-2, 0)

I changed it to match exactly but now I get an error that the with or
object variable not set


wrote:
the first number comes up as 6 then all the rest as 5
even though all show as 10 on my sheet
?


Die_Another_Day wrote:
Keep NAME dimmed as a range, make sure there is no .Value after the
find, then place a break point on the Set Name = Blah Line. Query the
row of the find in the immediate window like this:
?Range("MYRANGE").Find(What:=Me.cboPhone_Number.Va lue).Row
Let me know what the returned row is. If you would like you can also
send me the worksheet to my posting address.

Charles

wrote:
It doesn't work. :( I tried with and without the ".value")
I couldn't duplicate this in a new workbook either. So I checked this
workbook specifically:

-Range "MYRANGE" is correctly defined
-cboPhone_Number lists only options referenced to the contents of row 3
-the phone number I look up DOES have a corresponding Name 2 above it

What else can be wrong with it?

Die_Another_Day wrote:
I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!

GOOD GOLLY MISS MOLLY!
NOW it's working:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue,
MatchCase:=True).Offset(-2, 0)

The problem was that all name cells reference, in their formula, a
worksheet with their phone number and name as the title. (d'oh!)
Thanks for the help Charles!
:)

Michelle

wrote:
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue,
MatchCase:=True).Offset(-2, 0)

I changed it to match exactly but now I get an error that the with or
object variable not set


wrote:
the first number comes up as 6 then all the rest as 5
even though all show as 10 on my sheet
?


Die_Another_Day wrote:
Keep NAME dimmed as a range, make sure there is no .Value after the
find, then place a break point on the Set Name = Blah Line. Query the
row of the find in the immediate window like this:
?Range("MYRANGE").Find(What:=Me.cboPhone_Number.Va lue).Row
Let me know what the returned row is. If you would like you can also
send me the worksheet to my posting address.

Charles

wrote:
It doesn't work. :( I tried with and without the ".value")
I couldn't duplicate this in a new workbook either. So I checked this
workbook specifically:

-Range "MYRANGE" is correctly defined
-cboPhone_Number lists only options referenced to the contents of row 3
-the phone number I look up DOES have a corresponding Name 2 above it

What else can be wrong with it?

Die_Another_Day wrote:
I'm not sure it will help but dim NAME as a Range instead of variant,
then use the set command:
Set NAME = Range("MYRANGE").....
Let me know if that doesn't work.

Charles


wrote:
okay, DANGIT!

why wouldn't this work:

Dim NAME as Variant
NAME = Range("MYRANGE").Find(What:=Me.cboPhone_Number.Val ue).Offset(-2,
0)

BUT for some reason if the 2 is positive the whole thing works!

How can I get around this?



wrote:
YAY!!!!!!!!!!!!!!!!!!!

It's fixed! I've been working on this forEVER and I didn't even think
to do that!
Thank you so much Charles!
Thank you thank you!!!

Michelle :)
Die_Another_Day wrote:
If PROJECTTITLE is dimmed as a range then eliminate the .value at the
end of the statement, if it is dimmed as a string or variant, then
eliminate the "Set" statement at the beginning.

HTH

Charles Chickering

wrote:
I have a userform that allows users to select from a combo box the
title of a project they want to open. There is also a combo box to
select the work order number. The range "DATA" is a set of information
organized with the project title and work order number 6 cells below
it.


If Me.cboProject_Title_Select.Value = "" Then

Set PROJECTTITLE =
(Range("DATA").Find(What:=Me.cboWork_Order_Number_ Select.Value).Offset(-6,
0).Value)

WORKORDER = Me.cboWork_Order_Number_Select.Value

Else
If Me.cboWork_Order_Number_Select.Value = "" Then

Set WORKORDER =
Range("DATA").Find(What:=Me.cboProject_Title_Selec t.Value).Offset(6, 0)

PROJECTTITLE = Me.cboProject_Title_Select.Value
End If



I keep getting an Error 1004 message on:

Set PROJECTTITLE = (Range("DATA").Find_
What:=Me.cboWork_Order_Number_Select.Value).Offset (-6, 0).Value)

What is wrong with my code? arg!