Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
I have 4 columns data on my w/sheet.
my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
Dim res as variant
dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
Thank you Dave,activate Commandbutton means,the button should be enabled.
Dave,can you please amend this code, If empty cell found in rande D:D range ,commandbutton1 caption should be "OUT" else commandbutton caption be "IN" "Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
You didn't say what should happen if there is no match.
So maybe, near the top: me.commandbutton1.enabled = false me.commandbutton1.caption = "Out" then later: if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true me.commandbutton1.caption = "in" end if TUNGANA KURMA RAJU wrote: Thank you Dave,activate Commandbutton means,the button should be enabled. Dave,can you please amend this code, If empty cell found in rande D:D range ,commandbutton1 caption should be "OUT" else commandbutton caption be "IN" "Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
Thank you , but the code is not working.
"Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
I am hereby re-entering my question:
my W/sheet looks like this; Col A--------Col B--------Col C-----------Col D raju---------04/01/09----08:00 AM------04:00 Pm David--------04/01/09---07:35 AM-------04:10 PM raju----------04/02/09---08:00 AM------- David--------04/02/09----07:50 Am------ In my UseForm TextBox1 value entered is raju,CommandButton1 caption=Find and CommandButton2 caption=In I need code match userform.textbox1 value with sheet1.range("a1:a30") ,loop through all the cells in a1:a30 and find if any corresponding cells in in D1:D30 is empty,if empty found change userform.commmandButton2.caption to "out". "TUNGANA KURMA RAJU" wrote: Thank you , but the code is not working. "Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
It worked ok for me.
TUNGANA KURMA RAJU wrote: Thank you , but the code is not working. "Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
search a value in W/sheet
your match function is finding row no. of first occurance,where as i have
many similar matching values in Col A.Plese see my sample w/sheet data. "Dave Peterson" wrote: It worked ok for me. TUNGANA KURMA RAJU wrote: Thank you , but the code is not working. "Dave Peterson" wrote: Dim res as variant dim myRng as range with worksheets("Somesheetnamehere") set myrng = .range("a1:A99") 'your list end with res = application.match(me.textbox1.value, myrng, 0) me.commandbutton1.enabled = false if iserror(res) then 'no match between the textbox and the range else if isempty(myrng(res).offset(0,3).value) then me.commandbutton1.enabled = true 'me.commandbutton1.setfocus '???? end if end if Untested, uncompiled. Watch for typos. And I'm not sure what activate a commandbutton means. TUNGANA KURMA RAJU wrote: I have 4 columns data on my w/sheet. my userform has textbox in which a value entered. I need code for: match the value with w/sheet Col A:A and find which corresponding Col D:D cell is empty,if empty, activate a commandbutton on userform. thanks... -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
how do i use vlookup to search for a value on another sheet? | Excel Worksheet Functions | |||
search form in sheet | New Users to Excel | |||
Search and Display Sheet Name | Excel Discussion (Misc queries) | |||
Search for a Sheet | Excel Discussion (Misc queries) |