Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

match values in multiple comboboxes with multiple columns and copy values to next column

0

I no  know  if  this is  right  way  to  do  that  but   the  code  is  very slow  , so  my  aim  after  fill data  in  multiple  comboboxes  they should  match  with col A,B,C  and  when i fill textbox1,2,3  should  fill  the  values  begining   from  col D  then  when  I  wright  again  should  move  the  next  empty  col E every  time  fill data  on  userform  should  copy  values  and  move  next  empty  column  

so  if  there  is  way  making  the  code  a quick  it  will be  a great 

Private Sub CommandButton1_Click()
 Dim ws As Worksheet, matchFormula1, matchFormula2, matchFormula13 As String, cb1, cb2, cb3, cb4, cb5, cb6, cb7, cb8, cb9 As String
    Set ws = Sheets("Sheet1")
    cb1 = ComboBox1.Text
    cb2 = ComboBox2.Text
    cb3 = ComboBox3.Text
    cb4 = ComboBox4.Text
    cb5 = ComboBox5.Text
    cb6 = ComboBox6.Text
    cb7 = ComboBox7.Text
    cb8 = ComboBox8.Text
    cb9 = ComboBox9.Text

    matchFormula1 = "match(" & Chr(34) & cb1 & cb2 & cb3 & Chr(34) & ",A:A&B:B&C:C,0)"
    matchFormula2 = "match(" & Chr(34) & cb4 & cb5 & cb6 & Chr(34) & ",A:A&B:B&C:C,0)"
   matchFormula3 = "match(" & Chr(34) & cb7 & cb8 & cb9 & Chr(34) & ",A:A&B:B&C:C,0)"
    On Error Resume Next
    ws.Cells(Evaluate(matchFormula1), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox1.Value
    ws.Cells(Evaluate(matchFormula2), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox2.Value
    ws.Cells(Evaluate(matchFormula3), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox3.Value
    On Error GoTo 0
End Sub

 I hope  explain of  my  idea to be  clearly 

thanks  in  advance 

Answer
Discuss

Discussion

I'm afraid your question doesn't have an answer. First you need to show how the combo boxes are filled.
Variatus (rep: 4889) Mar 23, '21 at 9:39 pm
variatus
sorry   let's  begins  again   I  have 9  comboboxes   they  are linked of  rowsources  A,B,C   and   the  textbox1,2,3  are  linked  with  column     they  calls  price1,2,3...etc     so   what   I'm  trying  to do  it   fill  the  comboboxes  and  put  the  price  in all  textboxes   and  copy   the  values   to  the  empty   column    in  this  case  it  will  copy  to  col D    and  if   I  fill again   then  it  copy  and  move to   the  next  empty  COL  E   because  the  COL D   is  filled and  so  on  every  time  I  fill the  comboboes  and  textboxes   it should  copy   the  values and  move  to  column  is  empty , my  code  does  all   what  I  want  but   I see  there  is  a  problem  , it  takes  more  time    to  do  that  .
leap (rep: 46) Mar 23, '21 at 11:17 pm
Hi Leap, You say nothing new and, anyway, please don't modify your question in the Discussion. Your sample data are too little. Maybe 10-w40 08L / Q8 also exists with "jap". There should be several samples of each origin, type and brand.
Your 3 combo boxes should be interlinked. So, if the user selects "Q8" only matching brands and origins can be selected. You could save yourself hundreds of lines of code if you would introduce unique item numbers.
Why do you want 3 rows of combo boxes? Why not 12? Perhaps you want the 3 rows to be for the same item. Perhaps you want the 3 rows to be for 3 different items. Your intention makes a difference as to how to pre-fill the combo boxes.
And, of course, whatever is in the combo boxes determines what will be in the text boxes.
In your present file there is no guarantee that the user's specification of Brand/Type/Origin will lead to a valid data row. That's why I doubt that your code can work at all. On my PC your code crashes right away because I use Option Explicit. I think your project is much too demanding to get away without giving it basic structure which includes Option Explicit, of course. I think you are trying to decorate your house before painting the walls. The preparation isn't complete for what you are attempting to do now.
Please correct me if I'm wrong, but I think I gave you code to search for a match for 3 criteria. You should use that code. Your Evaluate(matchformula) doesn't work. Test it on a bigger data sample.
Variatus (rep: 4889) Mar 24, '21 at 9:16 pm
Variatus  about  this 
type and brand.Your 3 combo boxes should be interlinked 
you're  right   actually  i  will do  by  another  code  just  I  would  give  you  a sample  simple  and  this   want 3 rows of combo boxes? Why not 12?   jut  I  would  give  you  a sample  simple    to  understand me my  idea  indeed  I  have  many  data    and I want the rest os of  rows to be for 3 different items
leap (rep: 46) Mar 25, '21 at 4:27 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login