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

populate last value into textbox based on combobox

0

Hello

I  try  to  search  for  the  last  value  for  each  item into sheet1,2 together . 

if  I  have  the  same  item  for  sheet1,2 , then should populate  the  value  into  textbox2 for  last  value  is  existed  in last  sheet  based on selected combobox1  and  optionbutton1 or optionbutton2 .

for  example 

CD-01  is  existed in sheet1,2  for   10,11,12,14 then  when populate the  values  in textbox2 after select combobox1,optionbutton1 ,then the  values  in textbox2  should be 14 .as to column G  are  22,23,26,18 .if  I  select  combobox1,optionbutton2 then the  values  in textbox2  should be 18 

so  if  the  item  just  repeat in  the  same  sheet then should  brings  the  last  value  for  duplicates item  in tje  same sheet and  if  repeat  for two sheets  then should  brings  the  last value  from the  last  sheet 

my  code  just  work  for  the  first  sheet  with  ignore  the  second  sheet .

Private Sub ComboBox1_Change()
Dim c       As Range, rng As Range
    Dim search  As String
    Dim ws As Variant
    ws = Array("sheet1", "sheet2")
    Set rng = Range("B2:B12")
    search = Me.ComboBox1.Value
    Set c = rng.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, _
                    SearchFormat:=False)
    For Each ws In rng
    If Not c Is Nothing Then
        'search value found
        Me.ComboBox2.Value = c.Offset(, 1).Value
        Me.ComboBox3.Value = c.Offset(, 2).Value
        Me.ComboBox4.Value = c.Offset(, 3).Value
        If Me.OptionButton1.Value = True Then
            Me.TextBox2.Value = c.Offset(, 4).Value
        ElseIf Me.OptionButton2.Value = True Then
            Me.TextBox2.Value = c.Offset(, 5).Value       
        End If
    End If
 Next

End Sub

any  suggetion would truly  appreciated .

excel
MS Office Version: 2016
posted by: MAKLIL (rep: 34) on Thu Aug 18, 2022 at 5:45 am
Answer
Discuss

Answers

0
Selected Answer

Maklil

Your macro just looks at the active sheet (where the command button is clicked).To look at other sheets, you need to loop through the worksheets listed in your array ws.

In the attached revised file, I've changed your code as below (changes in bold, with comments) to do that and to change the heading of the right-most label in your userform to match the option button clicked: 

Private Sub ComboBox1_Change()
Dim c As Range, rng As Range
Dim search  As String
Dim ws As Variant, n as Long, Cll as Range

ws = Array("sheet1", "sheet2")
'loop through worksheets
For n = LBound(ws) To UBound(ws)
    ' look in worksheet
    Set rng = Worksheets(ws(n)).Range("B2:B12")

    search = Me.ComboBox1.Value
    Set c = rng.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, _
                    SearchFormat:=False)
    For Each Cll In rng
        If Not c Is Nothing Then
            'search value found
            Me.ComboBox2.Value = c.Offset(, 1).Value
            Me.ComboBox3.Value = c.Offset(, 2).Value
            Me.ComboBox4.Value = c.Offset(, 3).Value

            If Me.OptionButton1.Value = True Then
                'rewrite label
                Me.Label15.Caption = "PRICE"
                Me.TextBox2.Value = c.Offset(, 4).Value
            ElseIf Me.OptionButton2.Value = True Then
                'rewrite label
                Me.Label15.Caption = "PRC"
                Me.TextBox2.Value = c.Offset(, 5).Value
            End If
        End If
    Next Cll
 Next n

End Sub

I also changed your option button labels to match that change. (You should consider setting a default button).

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Fri Aug 19, 2022 at 3:43 am
Discuss

Discussion

great ! this  is  what  I need it 
many  thanks for  your  help .
MAKLIL (rep: 34) Aug 19, '22 at 11:59 am
Glad it works for you, thanks for selecting my Answer Maklil.

Forgot to say (and declare) that I'd used a new variable Cll to step through each cell in the matching range.
John_Ru (rep: 6142) Aug 19, '22 at 12:14 pm
thanks  again . also  I  forgot  to say   there  is  typo in  this  line  when  I  download  your  file 
Dim ws As Variant. n as Long
should  be "," after  variant  .I  hope  to  fix  it , maybe  some  member  is too beginner can't  deal  with  this  error,  if  they look for  like  my  subject  and  face  this  simple  problem .
MAKLIL (rep: 34) Aug 19, '22 at 12:38 pm
Thanks Maklil, I should have noticed that. I have now modified both the Answer text and the attached file, now reads:
Dim ws As Variant, n as Long, Cll as Range
John_Ru (rep: 6142) Aug 19, '22 at 1:05 pm
Add to Discussion


Answer the Question

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