Forum Discussion
LearningFurther
Mar 20, 2023Copper Contributor
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. ...
LearningFurther
Mar 29, 2023Copper Contributor
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
OliverScheurich
Mar 29, 2023Gold Contributor
The code returns the expected result in my file. Can you attach your file without sensitive data?
- LearningFurtherApr 03, 2023Copper Contributor
I am able to get the formula you stated prior to work, but only if it is in a worksheet with just those two files and if I change the code for J and For I to 1 to 3000 and 2 to 1000.
Is there a way to integrate this into my workbook that has multiple sheets, but only to still calculates the two sheets together?
Also, can we choose where the displayed field is returned? it always comes into column C, but when other columns are filled in, it does not return any dates.
I would like to integrate this into a worksheet that also has other information on it but "A" remains the item "B" remains stock level and then "C" can be the returned run-out date but then I have "D" on filled with other information. "This is on sheet 2 and sheet 1 remains unchanged.
Does the name of the worksheets matter as well, I noticed there is a "true worksheet number from when the worksheet is created, and then you can alter the tab, does this make a difference?
I sincerely appreciate all of your help!!!
- OliverScheurichMar 29, 2023Gold Contributor
You can click the button in cell N3 of your file to run the macro. The code dynamically determines the number of inventory in column A of sheet1 and the number of entries of items in column D of sheet2. I've entered additional inventory and the code returns the expected results. For inventory "banana" no date is returned because the "on hand" quantity is always bigger than the quantity in sheet2. 5000 line items in sheet2 could mean that the code runs for a few seconds.
- LearningFurtherMar 29, 2023Copper Contributor
This formula seems to "semi-work"
It is not returning all the products even though they are on both sheets
Once again I appreciate all your help.
Maybe other tweaks need to be made?
Private Sub CommandButton1_Click()
End Sub
Sub stock()Dim i, j As Long
Dim sum As LongFor j = 2 To 3000
For i = 2 To 1000sum = 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 ForElse
End IfNext i
Next jEnd Sub
- LearningFurtherMar 29, 2023Copper Contributor
Not sure if this matter also, but the range will go down to about 5,000 line items
Thank you for all your help!!