|
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
Find and offset in named Range
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! |
| All times are GMT +1. The time now is 09:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com