userform alternate value

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Greetings, I have learned a lot from all of you but I still struggle with multiple "if/then's"... Below is a snippet of code from a userform that I use to enter chemical usage data into a spreadsheet. The form works fine, but now I need to make a tweak to it. I currently have it set to take the value that is on the last filled cell in column "E", divide it by 51 plus multiply it by 24, and then place it in the next blank cell in column "F". This tells me how many gallons an hour I am using.

But now I need to use this form to account for barrels that have different amounts in them, instead of them all being the same 51 gallons. Some have 47, some have 60.75. We can call them chemicals "A", "B" or "C".

In the syntax for "chemicalbox.value" I have it set to see the name of the chemical, so my thinking that in .Range "F" I need to put an if/then statement so that if it sees one chemical then it will use the specific value for the division. I keep getting the formatting wrong and I am sure that it is with my structuring, could someone give me an idea on how you would structure the if/then statements?

Thank you for any assistance.

VBA Code:
'transfer the data
    With ActiveSheet

    nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("D" & Rows.Count).End(xlUp).Row - 0

     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = Now() - (TimeLog.Value * oneMin) 'this tells me when the barrel changed over
     .Range("G" & nextrow) = CommentBox.Value
     .Range("E" & nextrow) = Now() - (TimeLog.Value * oneMin) - .Range("D" & lastrow) 'this tells me how long the barrel was online
     .Range("F" & nextrow) = 51 / (.Range("E" & nextrow) * 24) 'this tells me how many galllons per hour were used.
     
    End With
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
'transfer the data
    With ActiveSheet

    nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("D" & Rows.Count).End(xlUp).Row - 0

     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = Now() - (TimeLog.Value * oneMin) 'this tells me when the barrel changed over
     .Range("G" & nextrow) = CommentBox.Value
     .Range("E" & nextrow) = Now() - (TimeLog.Value * oneMin) - .Range("D" & lastrow) 'this tells me how long the barrel was online
     Select Case ChemicalBox.Value
         Case "A": Gal = 51
         Case "B": Gal = 47
         Case "C": Gal = 60.75
      End Select
     .Range("F" & nextrow) = Gal / (.Range("E" & nextrow) * 24) 'this tells me how many galllons per hour were used.
     
    End With
 
Solution

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested but another way maybe

Rich (BB code):
    Dim m           As Variant
    Dim BarrelVol   As Double
  
    'get match
    m = Application.Match(ChemicalBox.Value, Array("chemicalA", "chemicalB", "chemicalC"), 0)
    'select division value (default 51 if no match)
    BarrelVol = IIf(Not IsError(m), Choose(m, 51, 47, 60.75), 51)
  
'transfer the data
    With ActiveSheet

    nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("D" & Rows.Count).End(xlUp).Row - 0

     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = Now() - (TimeLog.Value * oneMin) 'this tells me when the barrel changed over
     .Range("G" & nextrow) = CommentBox.Value
     .Range("E" & nextrow) = Now() - (TimeLog.Value * oneMin) - .Range("D" & lastrow) 'this tells me how long the barrel was online
     .Range("F" & nextrow) = BarrelVol / (.Range("E" & nextrow) * 24) 'this tells me how many galllons per hour were used.
   
    End With

In no match default value = 51

Change values shown in bold as required.

Dave
 

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
Cool Fluff and dmt32, that is so much cleaner (and easy to understand) then what I was writing :) Thank you for helping me again.

It always amazes me how clean you gurus' can make the coding look ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,160
Messages
5,745,123
Members
423,927
Latest member
Pra56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top