How make code dynamic (I think)

[Pookiemeister]

On a spreadsheet I have the names of five columns that contain the title of Product Codes, Product Description, Dozens per case, Cases per pallet and UOM(Unit of Measure). As of now, on my user form, I have a combo box that the user will select the product, a command button, and a text box. The values of that combo box are populated from the spreadsheet with code. The product Code is located in Column B (starts in B3 to be exact), Product Description located in Column C (starts in C3 to be exact), Product Description located in Column C (starts in C3 to be exact), Dozens per case in Column D (starts in D3 to be exact), Cases per pallet in Column E (starts in E3 to be exact), and finally Dozens per case in Column F (starts in F3 to be exact). Once the user selects the product from the combo box and enters a value in the text box(called "txtbxdz") and clicks the command button. A formula will be performed. Currently the values are hard coded into the program as shown in the formula below. I hope my explanation makes sense. Thank You.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

<code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; border: 0px; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit;">[COLOR=#101094][FONT=inherit]Private [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cmdbtnPrint_Click[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] textValUp [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] textValDown [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtUOM [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtCs [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtDz [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Case[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Is[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"4120-5-01 (ALLERGY 180MG 5CT)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        txtDz [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        txtCs [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]200[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        txtUOM [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"DZ"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

textValUp [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit](([/FONT][/COLOR][COLOR=#303336][FONT=inherit]txtbxdz[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtDz [/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtCs[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0.5[/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1E-16[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
textValDown [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit](([/FONT][/COLOR][COLOR=#303336][FONT=inherit]txtbxdz[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtDz [/FONT][/COLOR][COLOR=#303336][FONT=inherit]/[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txtCs[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0.5[/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1E-16[/FONT][/COLOR]</code>

</code>

 

[Keebellah]

I never performed a formula so that's new to me and probaly the meaning got lost in translation.
Can you post a sample workbook with the vba code in it?

 

[Pookiemeister]

In the above code, txtDz (Dozens per case), txtCs(Cases per pallet), and txtUOM(UOM(Unit of Measure))
have values assigned to them in the code. I need to have these values pulled from a spreadsheet that is associated with the product name, I guess similarly to a VLookup in Excel. but I am uncertain if I can do a VLookup in VBA. In this case, if the user selects 4120-5-01 (Allergy 180MG 5CT) from the combo box the associated value will be assigned to each of the variables. Which is located on the spreadsheet. I hope that makes more sense. Thank You.

 

[Keebellah]

Can you please attach a sample workbook with non-private data, makes it easier to explain

 

[Pookiemeister]

I'm new to this site. How do I do this?

 

[Keebellah]

Can't explain it because I'm not allowed to attach posts here yet, don't understand why but that's probably some .... forum rule
You'll have to check the help function

 

[Fluff]

This site does not allow uploading of files. Instead there are some tools here that assist in posting data to the thread. And you can test them on the Test Board

 

[Keebellah]

@Fluff: thanks for the up, don't know if I like that method, will check and test.

 

[Keebellah]

Thanks again but the file has errors, quite a few undermentioned variables and no correct check to see if a file is actually open or active.
I tried and looked at it, and well, I would expect that a 'tool' offered would be tested.

 

[Fluff]

I don't know which of the tools you are referring to, but they are all used by various members & if there was something wrong with one of them I think we would have heard about it.