Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CD Programming | Excel Discussion (Misc queries) | |||
programming | Excel Discussion (Misc queries) | |||
Please help with programming | Excel Programming | |||
Programming lag | Excel Programming | |||
How to add via programming ? | Excel Programming |