SumIf/IfStatements: Referencing more them one attribute

[gaznev2000]

Hi all, just wondering if any one can help with my If statement dilemma. I'm creating a stock tracking excel for my company and have run into a issues when writing an if statement which is reference more then on attribute on the worksheet.

So far I have

• Created a input form in which the user can add jobs with how much stock was used on that job. The form allows the user to input a job name and 6 different piles (products) which can vary in length and can be in 2 different designs.
• Created a stock worksheet (Main Page) which will allow the user to change manufactured stock levels and show how much stock has been used form the jobs that have been added. Thus allowing the subtraction of used stock from manufactured stock totalling current stock levels. This worksheet also includes buttons for adding a new job, Saving and Printing the Form.
• Created a Jobs Worksheet which will list all the jobs that have been added, which could include up to 6 piles with different lengths and design.

Confused yet? My dilemma is I have to compile all the piles (Products) from the jobs added and filter them by the different lengths and design then place the Quantity used for each length and design into the relevant cell on a table.

I hope I have not confused anyone to much and would really appreciate any help as I am lost.

If you are still puzzled to what the hell i am talking about please look at the excel workbook attached and make your own conclusions.

Kind Regards
Gary

My Excel File so far - stocktracker11.xlsm
http://www.excelbanter.com/attachment.php?attachmentid=180&d=1282703426

 

[sijpie]

I'm assuming you are talking about the two tables in column U,V,W in the Job sheet.

Can you not add the numbers as you enter the new row when the user has clicked on the OK button? You are handling the data then so add it immediately to the relevant cell of your two tables.

 

[gaznev2000]

Hi Sijpie,thanks for your reply. Yes the tables in U,V,W is where i want the data to be imported, and yes I could put the data in the relevant cells when the user presses ok but there are 3 problems

1. I never even thought about doing that and i am used to working with ranges(Stupid I know)
2. I don't know how to write an if statements over multiple attributes
3. If there was a mistake made when filling in the input form the whole job would have to be deleted and re-entered instead of just changing the one wrong cell.

Thanks for your help much appreciated

Gary

 

[sijpie]

This should be pretty easy. Your current form isn't set up for corrections anyway, more about that below.

in the form code (addJob_click you currently have:

' Write data to worksheet
    RowCount = Worksheets("Jobs").Range("Input").CurrentRegion.Rows.Count
    With Worksheets("Jobs").Range("Input")
        .Offset(RowCount, 0).Value = Me.JobName.Value
        .Offset(RowCount, 1).Value = Me.PileLength1.Value
        .Offset(RowCount, 2).Value = Me.HelixSize1.Value
        .Offset(RowCount, 3).Value = Me.PileQuantity1.Value
        .Offset(RowCount, 4).Value = Me.PileLength2.Value
        .Offset(RowCount, 5).Value = Me.HelixSize2.Value
        .Offset(RowCount, 6).Value = Me.PileQuantity2.Value
....        
End With

You just need to add some simple code to check the values and add them to the correct table:

...
        .Offset(RowCount, 17).Value = Me.HelixSize6.Value
        .Offset(RowCount, 18).Value = Me.PileQuantity6.Value

        
        Add_PileQuantities .Offset(RowCount, 1)
        Add_PileQuantities .Offset(RowCount, 4)
        Add_PileQuantities .Offset(RowCount, 7)
        Add_PileQuantities .Offset(RowCount, 10)
        Add_PileQuantities .Offset(RowCount, 13)
        Add_PileQuantities .Offset(RowCount, 16)
               
End With

....
end sub
 
Sub Add_PileQuantities(Length As Range)
        If Length.Value Then
            Select Case Length.Offset(0, 1).Value   'Helix for this pilelength
            Case 250
                Select Case Length.Offset(0, 0).Value   ' this length itself, offset is really superfluous
                    Case "1.0 m"
                        .Range("W3").Value = .Range("W3").Value + Length.Offset(0, 2).Value ' add the quantity to the summary
                    Case "1.5 m"
                        .Range("W4").Value = .Range("W4").Value + Length.Offset(0, 2).Value
                    Case "2.0 m"
                        .Range("W5").Value = .Range("W5").Value + Length.Offset(0, 2).Value
                    Case "2.5 m"
                        .Range("W6").Value = .Range("W6").Value + Length.Offset(0, 2).Value
                    Case "3.0 m"
                        .Range("W7").Value = .Range("W7").Value + Length.Offset(0, 2).Value
                    Case "3.5 m"
                        .Range("W8").Value = .Range("W8").Value + Length.Offset(0, 2).Value
                    Case "4.0 m"
                        .Range("W9").Value = .Range("W9").Value + Length.Offset(0, 2).Value
                End Select
             Case 300
                Select Case Length.Offset(0, 0).Value   ' this length itself, offset is really superfluous
                    Case "1.0 m"
                        .Range("W13").Value = .Range("W13").Value + Length.Offset(0, 2).Value ' add the quantity to the summary
                    Case "1.5 m"
                        .Range("W14").Value = .Range("W14").Value + Length.Offset(0, 2).Value
                    Case "2.0 m"
                        .Range("W15").Value = .Range("W15").Value + Length.Offset(0, 2).Value
                    Case "2.5 m"
                        .Range("W16").Value = .Range("W16").Value + Length.Offset(0, 2).Value
                    Case "3.0 m"
                        .Range("W17").Value = .Range("W17").Value + Length.Offset(0, 2).Value
                    Case "3.5 m"
                        .Range("W18").Value = .Range("W18").Value + Length.Offset(0, 2).Value
                    Case "4.0 m"
                        .Range("W19").Value = .Range("W19").Value + Length.Offset(0, 2).Value
                End Select
           End Select
        End If
End Sub

this will after having filled in the sheet go through each of the 6 Piles and if a value has been added check to see what Helx length and then add the required quantities

 

[sijpie]

Oops, errors discovered. Replace the new sub with:

Sub Add_PileQuantities(Length As Range)
        If Length.Value <> vbNullString Then
        With Worksheets("Jobs")
            Select Case Length.Offset(0, 1).Value   'Helix for this pilelength
            Case 250
                Select Case Length.Offset(0, 0).Value   ' this length itself, offset is really superfluous
                    Case "1.0 m"
                        .Range("W3").Value = .Range("W3").Value + Length.Offset(0, 2).Value ' add the quantity to the summary
                    Case "1.5 m"
                        .Range("W4").Value = .Range("W4").Value + Length.Offset(0, 2).Value
                    Case "2.0 m"
                        .Range("W5").Value = .Range("W5").Value + Length.Offset(0, 2).Value
                    Case "2.5 m"
                        .Range("W6").Value = .Range("W6").Value + Length.Offset(0, 2).Value
                    Case "3.0 m"
                        .Range("W7").Value = .Range("W7").Value + Length.Offset(0, 2).Value
                    Case "3.5 m"
                        .Range("W8").Value = .Range("W8").Value + Length.Offset(0, 2).Value
                    Case "4.0 m"
                        .Range("W9").Value = .Range("W9").Value + Length.Offset(0, 2).Value
                End Select
             Case 300
                Select Case Length.Offset(0, 0).Value   ' this length itself, offset is really superfluous
                    Case "1.0 m"
                        .Range("W13").Value = .Range("W13").Value + Length.Offset(0, 2).Value ' add the quantity to the summary
                    Case "1.5 m"
                        .Range("W14").Value = .Range("W14").Value + Length.Offset(0, 2).Value
                    Case "2.0 m"
                        .Range("W15").Value = .Range("W15").Value + Length.Offset(0, 2).Value
                    Case "2.5 m"
                        .Range("W16").Value = .Range("W16").Value + Length.Offset(0, 2).Value
                    Case "3.0 m"
                        .Range("W17").Value = .Range("W17").Value + Length.Offset(0, 2).Value
                    Case "3.5 m"
                        .Range("W18").Value = .Range("W18").Value + Length.Offset(0, 2).Value
                    Case "4.0 m"
                        .Range("W19").Value = .Range("W19").Value + Length.Offset(0, 2).Value
                End Select
           End Select
           End With
        End If
End Sub

 

[gaznev2000]

Sijpie, You are a legend! I wasn't expecting you to write the statement out, but thanks a lot, it works perfectly. I would of never thought to do it that way so again many thanks. I have never used the case Cmd, I take it from your code that you can nest these commands?

Kind Regards
Gary

 

[sijpie]

Sure can - as long as you remember to 'end select' them properly....

And it gets even better because you can construct things such as:

Select True
   Case a > b
      ....
   Case a = b
      .....
   Case else
      ....
End Select

which could be useful also for instance to evaluate which OptionButton has been clicked in a form:

Select True
   Case OptionButton1.value
      ...
   Case OptionButton2.value
      ...
 
etc

beats the hell out of endless If statements