Forum Discussion

LearningFurther's avatar
LearningFurther
Copper Contributor
Mar 20, 2023

Formula Help

Excel Sheet 2: Column D are the different part numbers we offer. Column E is the quantity ordered on a specific Purchase order. Column H is the quantity shipped from that specific purchase order. ...
Formulas and Functions
LearningFurther's avatar
LearningFurther
Copper Contributor
Mar 21, 2023

NikolinoDE  I have attached a sample Excel sheet very simple with just a couple of items
I still can not get the formula to work, not sure if I am missing something. 

 

I really do appreciate the help!!

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 21, 2023

LearningFurther 

Sub stock()

Dim i, j As Long
Dim sum As Long

For j = 1 To 3
For i = 2 To 100

sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))

If sum > Sheets("Sheet1").Cells(j, 2).Value Then
Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
Exit For

Else
End If

Next i
Next j

End Sub

You can try these lines of code. In the attached file you can click the button in cell B4 of Sheet2 to run the macro.

 

Which version of Excel do you work with? Do you have Office 365?

  • LearningFurther's avatar
    LearningFurther
    Copper Contributor
    Mar 21, 2023
    OliverScheurich

    Would this look over Sheet 1 and Sheet 2?
    Also, I am unable to use this is states it's from an "untrusted" source and will not allow me to open it.
    I have never worked with Macros before, but am willing to learn.
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Mar 23, 2023

      LearningFurther 

      The results are returned in range C1:C3 of Sheet1. The code refers to Sheet1 and Sheet2.

       

      Sheet1:

       

      Sheet2:

       

      You can copy the code from my last reply and paste it into a module of your VBA editor. Then you can run the macro in your file as well.

      • LearningFurther's avatar
        LearningFurther
        Copper Contributor
        Mar 29, 2023

        OliverScheurich 

         

        I keep getting a 

        Compile Error:

        Expected end sub

         

        I was able to create some simple Macros but was not able to use this code

         

        I created a commandbutton

         

        Private Sub CommandButton4_Click()

        Sub stock()

        Dim i, j As Long
        Dim sum As Long

        For j = 1 To 3
        For i = 2 To 100

        sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))

        If sum > Sheets("Sheet1").Cells(j, 2).Value Then
        Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
        Exit For

        Else
        End If

        Next i
        Next j

        End Sub

Resources