Simplify repetative userform code

[Macklin]

I have a userform with multiple buttons and textboxes. Each one needs to find the current row of the combobox value, so I use the same code in each sub. I have found how to declare the variable once. Is there a way to simplify the rest? Thank you for any help.

 MyVal = ComboBox1.Value
 
       With Sheets("Sheet1").Range("A:A")
         Set cell = .Find(MyVal, LookIn:=xlValues)
         rw = cell.row
       End With
   On Error GoTo 0

 

[Macklin]

I guess I should clarify my question. The code is simple enough, but is there a way to achieve the same thing the code does without repeating it in every sub procedure?

 

[dine]

Hi Macklin,

Hope you are trying to retrive the row number. Hence define the code as a function and use the function whereever the code is used.

Function rw_check(rw)
    MyVal = UserForm1.ComboBox1.Text
 
       With Sheets("Sheet1").range("A:A")
         Set cell = .Find(MyVal, LookIn:=xlValues)
         rw = cell.Row
       End With
   On Error GoTo 0
End Function

Call the Function using "Call rw_check(roww)" whereever you use the code.

Hope this works!!

Cheers,
Dine

 

[Macklin]

Thank you dine, This makes sense to me. It will certainly clean up the code. Do you know or think it would be more efficient as well, or is it about the same?

 

[dine]

I hope there will not be much difference in time taken to run the macro, it can be considered efficient because it will reduce the number of lines in coding and if there is a huge data if will be more effective.

Cheers,
Dine