combobox return numerical value for further calculations with other userform functions

[luihp329]

Hi,

I am fairly new to VBA and I need to quickly construct a simple userform for work. To put it simply, I have a list of items each with corresponding price (2 groups, one group is a subset of the other group of major items), I would like to construct a userform where the user can use a combobox to select from the major items, in which the corresponding price of the selected item would be multiplied by a value which is inputted via a textbox on the userform.

I had a good start where I figured out how to input all the major items into the combobox and it works when I press F5. However, I am stuck now when I want to return the corresponding value for formula operations with a textbox input, as I believe this requires some coding. The items and their prices are already listed in a worksheet in adjacent columns, in a way such that a vlookup function can be used normally to return the price values based on specific conditions. Appreciate it if I can be pointed to the right directions, thank you.

 

[Akuini]

Hi, @luihp329 Welcome to the Forum
Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.

 

[luihp329]

Hi, @luihp329 Welcome to the Forum
Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.

Please see below, thanks.

Basically I have inputted everything in Column B into the combobox, I just want it to return the value in column C and do operations with a textbox input that i will make in the userform (Area) and return the final value with a command button. Please let me know if you need more details, thanks for your help!!

 

[Akuini]

Sorry, I don't want to recreate the userform & retype the sample data, that's why I asked you for a sample workbook.
So I hope another member will step in to help you.

 

[luihp329]

Sorry, I don't want to recreate the userform & retype the sample data, that's why I asked you for a sample workbook.
So I hope another member will step in to help you.

Ok sorry I was trying to get rid of sensitive data and save as a new file but some error came up, I think I know what is wrong now give me a second I will attach it, is it ok for me to attach it here or you prefer a link? Thanks.

 

[Akuini]

You can't attach a file in this forum so upload it to a free site such as dropbox.com or google drive & then share the link here

 

[luihp329]

You can't attach a file in this forum so upload it to a free site such as dropbox.com or google drive & then share the link here

Cost Benchmarking V1 (Sample).xlsm

Shared with Dropbox

www.dropbox.com

Here you go, thank you.

 

[Akuini]

Try this:
1. I change the sheet name with unicode char to "sheet2", because it doesn’t work with my local settings.
2. Variable x is where I put the value of the corresponding price (col H)

Dim va, vb

Private Sub CommandButton1_Click()
Dim x, fm

With Sheets("sheet2")
fm = Application.Match(ComboBox1.Value, .Range("B:B"), 0)
    If IsNumeric(fm) Then
        x = Cells(fm, "H").Value 'get the value in col H
    End If
End With
y = Cells(fm, "H").Address 'the cell where you 'get the value in col H

MsgBox x

End Sub

Private Sub UserForm_Initialize()
    
    With Sheets("sheet2")
        ComboBox1.List = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)).Value
    End With

End Sub

the workbook

luihp329 = Cost Benchmarking V1 (Sample)

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[luihp329]

Try this:
1. I change the sheet name with unicode char to "sheet2", because it doesn’t work with my local settings.
2. Variable x is where I put the value of the corresponding price (col H)

Dim va, vb

Private Sub CommandButton1_Click()
Dim x, fm

With Sheets("sheet2")
fm = Application.Match(ComboBox1.Value, .Range("B:B"), 0)
    If IsNumeric(fm) Then
        x = Cells(fm, "H").Value 'get the value in col H
    End If
End With
y = Cells(fm, "H").Address 'the cell where you 'get the value in col H

MsgBox x

End Sub

Private Sub UserForm_Initialize()
  
    With Sheets("sheet2")
        ComboBox1.List = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)).Value
    End With

End Sub

the workbook

luihp329 = Cost Benchmarking V1 (Sample)

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[luihp329]

Thanks very much.

However I am getting a type mismatch error 13 with debugging showing something wrong with this line:
y = Cells(fm, "H").Address

thanks