find method?

  • Thread starter Thread starter CG Rosén
  • Start date Start date
C

CG Rosén

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
 
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
(e-mail address removed)


----- 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é
 
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
 
Back
Top