ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   comparing textbox value with values in named range (https://www.excelbanter.com/excel-programming/444387-comparing-textbox-value-values-named-range.html)

jeff

comparing textbox value with values in named range
 
I have textbox1 that will hold a date value entered by user.
I have a named range "PayWeek5" that shows a series of dates.

I want to do an If-Then that compares the date entered by user to the
dates in the range "PayWeek5".

If the date from the testbox matches any one of the dates in the
range, then.....

Here's a simple example of what I need. I've looked in this site, but
could not see anything like what I'm looking for.

If TextBox1.Value = Range "PayWeek5".Value then 'matching any of the
dates in the range.
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TextBox2.Value 'enters value in textbox2

Thanks
jeff

Dave Peterson[_2_]

comparing textbox value with values in named range
 
You could loop through each of the cells in the range.

dim FoundAMatch as boolean
dim myCell as range
foundamatch = false
for each mycell in worksheets("Sheet99").range("payweek5").cells
if mycell.value = cdate(me.textbox1.value)
foundamatch = true
exit for
end if
next mycell

if foundamatch then
...

=========
If the range is a single area, you could try:

if application.countif(worksheets("sheet99").range("p ayweek5"), _
cdate(me.textbox1.value)) then

'there's a match.

========
Remember to validate that the value in the textbox is an actual date, too!



On 03/30/2011 12:23, jeff wrote:
I have textbox1 that will hold a date value entered by user.
I have a named range "PayWeek5" that shows a series of dates.

I want to do an If-Then that compares the date entered by user to the
dates in the range "PayWeek5".

If the date from the testbox matches any one of the dates in the
range, then.....

Here's a simple example of what I need. I've looked in this site, but
could not see anything like what I'm looking for.

If TextBox1.Value = Range "PayWeek5".Value then 'matching any of the
dates in the range.
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TextBox2.Value 'enters value in textbox2

Thanks
jeff


--
Dave Peterson

jeff

comparing textbox value with values in named range
 
On Mar 30, 6:04*pm, Dave Peterson wrote:
You could loop through each of the cells in the range.

dim FoundAMatch as boolean
dim myCell as range
foundamatch = false
for each mycell in worksheets("Sheet99").range("payweek5").cells
* * if mycell.value = cdate(me.textbox1.value)
* * * *foundamatch = true
* * * *exit for
* * end if
next mycell

if foundamatch then
* *...

=========
If the range is a single area, you could try:

if application.countif(worksheets("sheet99").range("p ayweek5"), _
* * * * * * * * * * * * cdate(me.textbox1.value)) then

* * *'there's a match.

========
Remember to validate that the value in the textbox is an actual date, too!

On 03/30/2011 12:23, jeff wrote:





I have *textbox1 that will hold a date value entered by user.
I have a named range "PayWeek5" that shows a series of dates.


I want to do an If-Then that compares the date entered by user to the
dates in the range "PayWeek5".


If the date from the testbox matches any one of the dates in the
range, then.....


Here's a simple example of what I need. I've looked in this site, but
could not see anything like what I'm looking for.


If TextBox1.Value = Range "PayWeek5".Value then * 'matching any of the
dates in the range.
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TextBox2.Value * 'enters value in textbox2


Thanks
jeff


--
Dave Peterson- Hide quoted text -

- Show quoted text -


Thanks Dave. I don't understand the coding, but as with others, I'll
study it and figure it out along the way. In the mean time, this
works. I appreciate it very much. Thanks for taking the time.
jeff


All times are GMT +1. The time now is 05:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com