Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find method?
Good Day,
Try to figure out how to solve the following: Have a range of one column and indefinite rows. To start, this range is empty. The range will be filled with values (strings) due to choices in Textbox1. The first time a selected item apperas it will be stored in the range and its corresponding number value will be stored in the cell to the right of the string. Next time the same item is selected I will try to find it in the range and only add its number value in the right column. Is the FindMethod the best way to approach this problem. Thanks for any hints. Brgds CG Rosén |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find method?
First of all, in your question, you didn't say clearing "what is the corresponding number". Therefore, I make some assumptions below
On an userform, you got 2 Textboxes "TextBox1" and "TextBox2 Also, one commandbutton "CommandButton1 If value in TextBox1 does not exist in column(1) of activesheet, insert it in a new row, and put the TextBox2 value to the right of it. (I assume the text in TextBox2 is the "corresponding number you mentioned" So, put the following code into your userform '-------------------------------------- Private Sub CommandButton1_Click( Dim With ActiveSheet.Range("A1").CurrentRegion.Columns(1 Set c = .Find(TextBox1.Text, LookIn:=xlValues If c Is Nothing The .Cells(.Rows.Count + 1).Value = TextBox1.Tex .Cells(.Rows.Count + 1).Offset(0, 1).Value = TextBox2.Tex Els c.Offset(0, 1).Value = TextBox2.Tex End I End Wit End Su '-------------------------------------- Regards Edwin Ta ----- CG Rosén wrote: ---- Good Day Try to figure out how to solve the following Have a range of one column and indefinite rows. To start, this rang is empty. The range will be filled with values (strings) due to choice in Textbox1. The first time a selected item apperas it will be stored in th range and its corresponding number value will be stored in the cel to the right of the string. Next time the same item is selected I will tr to find it in the range and only add its number value in the right column Is the FindMethod the best way to approach this problem Thanks for any hints Brgd CG Rosé |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find method?
Good Day again, Many thanks for your advise. Have modified the code as below and it works fine. Are little confused regarding line 1, the definition of the range. Tried this: With ActiveSheet.Range("DG5:DG30") .This puts the values in cell "DG31". Is this syntax a dead end? Thanks again. Brgds CG Rosén Private Sub CommandButton5_Click() Dim c With ActiveSheet.Range("DG5").CurrentRegion.Columns(1) Set c = .Find(ComboBox1.Text, LookIn:=xlValues) If c Is Nothing Then .Cells(.Rows.Count + 1).Value = ComboBox1.Text .Cells(.Rows.Count + 1).Offset(0, 1).Value = TextBox3.Text Else c.Offset(0, 1).Value = c.Offset(0, 1).Value + TextBox3.Text End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find method example | Excel Programming | |||
The find method | Excel Programming | |||
Help with the Find method | Excel Programming | |||
The Find Method | Excel Programming | |||
The Find Method | Excel Programming |