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...

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

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

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

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

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

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

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