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

get the last balance into textbox based on selected combobox

0

Hello !

I  want  populate  the  last  balance  is  existed  in  column I  into  textbox 1  based  on  selected item from combobox after  matching   with column D .

should  give  me  the  last  value  is  existed in column I  into textbox   based on matching with  column D   where selected item from combobox1

this  is  what  I  have 

Private Sub combobox1_Change()
Dim f As Range
  With Worksheets("bmn")
    Set f = .Columns(4).Find(combobox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      .Cells(f.Row, "I") = Me.TextBox1.Value
    End If
  End With
End Sub

thanks 

excel
MS Office Version: 2016
posted by: leap (rep: 46) on Sat Jun 18, 2022 at 7:18 am
Answer
Discuss

Discussion

Hi John,
I attached  the  file 
leap (rep: 46) Jun 18, '22 at 8:05 am
Add to Discussion

Answers

0
Selected Answer

Here is  the  solution

Private Sub ComboBox1_Change()
Dim f As Range
  With Worksheets("bmn")
    Set f = .Columns(4).Find(Combobox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
 
    If Not f Is Nothing Then
      Me.TextBox1.Value = .Cells(f.Row, "I")
 
    End If
  End With
End Sub 
posted by: leap (rep: 46) on Sat Jun 18, 2022 at 3:57 pm
Discuss

Discussion

That's good Leap. I could not quite recall the search direction argument (I thought it included "reverse" not "previous") so suggested the loop method instead 
John_Ru (rep: 6142) Jun 18, '22 at 5:34 pm
Add to Discussion
0

Hi Leap

You just need to correct the line in bold below:

Private Sub Combobox1_Change()
Dim f As Range
  With Worksheets("bmn")
    Set f = .Columns(4).Find(Combobox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Me.TextBox1.Value = .Cells(f.Row, "I")

    End If
  End With
End Sub

Hope this helps.

posted by: John_Ru (rep: 6142) on Sat Jun 18, 2022 at 9:21 am
Discuss

Discussion

thanks, unfortunately  doesn't  show  any  thing in textbox1
leap (rep: 46) Jun 18, '22 at 9:32 am
Are you sure there's a value in column I? If not (your macro made cell I4 = "") , then nothing will appear. Type a value in I4 and the revised macro should work. 

I made column D read CL1, CL2, CL3 etc. then picked CL3 (e.g) to check that the correct value appeared in the textbox.
John_Ru (rep: 6142) Jun 18, '22 at 10:14 am
that's  because   is  duplicated  CL1   in column D . in  first  row  is  empty  but  I  have  duplicated  CL1 in  last  row    ( should  populate the  last  value  for  duplicate name if  there is  any  duplicate  name  in  column D  )  and  this  is what  I look for  it .
based on  the  code  it  will populate  the  first  value and  ignore  the  last  value  if  the  name is  duplicate .
leap (rep: 46) Jun 18, '22 at 10:27 am
Leap. I'm miles away from my PC for the next 20 hours or or more (so can't check any macros) .

. Find will always get just the first item. You need to use a Do Loop with .FindNext to find the other occurrences (and the last, if that's what you want- I don't understand your data!)
John_Ru (rep: 6142) Jun 18, '22 at 10:43 am
I  appreciate  for  your  time .  the  good  news I got  the  solution after search  in the  internet  by  getting  some  ideas  and  adapting  my  code(no loop) , bad  news  I  want to post  the  solution  by  press answer ( to  take advantage the  others members) but  moves  me  to  discuss  I  can't  post  it, how  can  I  post it,please?
leap (rep: 46) Jun 18, '22 at 12:19 pm
Glad you sorted it, Leap. To answer your own question, I think you just select it and press Answer (siehartley just Answered his own recent question). I've deleted my discussion point (that might be forcing the discussion)
John_Ru (rep: 6142) Jun 18, '22 at 2:46 pm
thanks  John !   I understand  how  do  that  . at the  bottom    guide  me  if  I click discuss  or  answer . seem  the  button  answer  is  not  relation of  it .
the  select  answer  should  be  after 24 hours. I  hope  don't  forget   after  this  time  to  select .
anyway  you  can  see  the  answering .
leap (rep: 46) Jun 18, '22 at 3:48 pm
Add to Discussion


Answer the Question

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