Programming
 

Hi all
I have a problem.
I want to write a program using VB in Excel.
I put a txtbox and a cmdbutton in my worksheet. When I click on cmdButton, I
want my program to do this:
See if the word in the text box, exists in column "A" or not. If it exists
in column A, then its related number in column B must get 1 grade higher
than before, but if the word doesn't exist in column "A", add the word to
the end of the column.
For example if these are my columns:
A B C
1 Name No
2 apple 10
4 banana 2
5 Lemon 15
6

Now if the word in txtbox is "Apple" , then the worksheet must be like this:
A B C
1 Name No
2 apple 11
4 banana 2
5 Lemon 15
6

But if the word is "Cherry", the worksheet must be changed into this:

A B C
1 Name No
2 apple 10
4 banana 2
5 Lemon 15
6 Cherry 1

Hope its clear. How can I do this? I don't know how to search for a word in
a column or how to find the position of end of a column.
Any help is appreciated.
Thank you
Javad

Programming
 

You can find that next open cell in column A with something like:

dim NextCell as range
with worksheets("sheet1")
set nextcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

It's kind of like going to A65536, hitting the End key, then up arrow. Then
hitting the Down arrow to get to the open cell.

You can check to see if the value is in the range (A2:A(lastrow)) in a few ways.

One of them:

dim res as variant
dim myRng as range
dim myStr as string
dim NextCell as range
dim myValue as variant

'not sure where that textbox is.
mystr = textbox1.value

with worksheets("sheet1")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
set nextcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

if mystr = "" then
'do nothing
else
res = application.match(mystr,myrng,0)
if iserror(res) then
'not found
nextcell.value = mystr
nextcell.offset(0,1).value = 1
else
'was found
myvalue = myrng(res).offset(0,1).value
if isnumeric(myvalue) then
myrng(res).offset(0,1).value = myvalue + 1
else
msgbox "Non-numeric value!
end if
end if
end if


=======
I'm not sure where/what the textbox was--on a userform on a worksheet (and from
the Drawing toolbar or from the Control toolbox toolbar).


===
Just another option to look for existing values:

if application.countif(myrng,mystr)0 then
'found one
else
'didn't find one
end if




Javad wrote:

Hi all
I have a problem.
I want to write a program using VB in Excel.
I put a txtbox and a cmdbutton in my worksheet. When I click on cmdButton, I
want my program to do this:
See if the word in the text box, exists in column "A" or not. If it exists
in column A, then its related number in column B must get 1 grade higher
than before, but if the word doesn't exist in column "A", add the word to
the end of the column.
For example if these are my columns:
A B C
1 Name No
2 apple 10
4 banana 2
5 Lemon 15
6

Now if the word in txtbox is "Apple" , then the worksheet must be like this:
A B C
1 Name No
2 apple 11
4 banana 2
5 Lemon 15
6

But if the word is "Cherry", the worksheet must be changed into this:

A B C
1 Name No
2 apple 10
4 banana 2
5 Lemon 15
6 Cherry 1

Hope its clear. How can I do this? I don't know how to search for a word in
a column or how to find the position of end of a column.
Any help is appreciated.
Thank you
Javad

--

Dave Peterson