Writing Excel VBA Code

[Wetaka]

Hullo,

I am Wetaka of Uganda, East Africa.

I Have started learning and using Excel VBA and I find your videos very Educative.

In my Data entry sheet invetory management i have been using the following formula and I would like write it in Excel VBA

How do we write the VBA code for the Formula: =MIN(SUMIF(I:I,I3,L:L)-SUMIF(I$1:I2,I3,O$1:O2),K3)

It finds the first items out of Stock.

Your help will be of great use

Thank you

Wetaka

 

[JackDanIce]

Welcome to the board. Try:

Sub M1()

    Dim s   As String
    Dim x   As Long
    
    x = Cells(Rows.Count, 9).End(xlUp).Row - 2
    s = "=MIN(SUMIF($I$1:$I$LR,I3,$L$1:$L$LR)-SUMIF(I$1:I2,I4,O$1:O2),K3)"
    s = Replace(s, "LR", x)
    
   Cells(3, Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column).Resize(x).Formula = s
   
End Sub

 

[Wetaka]

Hullo JackDanIce,

Thank you for your help.

I tried to use the code exactly as I received it ie,
Sub M1()

Dim s As String
Dim x As Long

x = Cells(Rows.Count, 9).End(xlUp).Row - 2
s = "=MIN(SUMIF($I$1:$I$LR,I3,$L$1:$L$LR)-SUMIF(I$1:I2,I4,O$1:O2),K3)"
s = Replace(s, "LR", x)

Cells(3, Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column).Resize(x).Formula = s

End Sub

The results was the following formula in column K(Value Out)as in the table below




=MIN(SUMIF($I$1:$I$24,I3,$L$1:$L$24)-SUMIF(I$1:I2,I4,O$1:O2),K3)
every time I make an entry and submit the formula is inserted in the next column ie K, L M etc
When I made a few adjustments of the line
s = "=MIN(SUMIF($I$1:$I$LR,I3,$L$1:$L$LR)-SUMIF(I$1:I2,I4,O$1:O2),K3)" to s = "=MIN(SUMIF($D$1:$D$LR,D3,$F$1:$F$LR)-SUMIF(D$1:D2,D4,I$1:I2),E3)"
The following formula resulted on running the code in excel column K
=MIN(SUMIF($I$1:$I$24,I3,$L$1:$L$24)-SUMIF(I$1:I2,I4,O$1:O2),K3)

I expected to see the result similar to the fomula =MIN(SUMIF(D:D,D3,F:F)-SUMIF(D$1:D2,D3,I$1:I2),E3) when inserted in column I named FIFO Qty Out in the Table below.

Date	Request_Invoice Number	From_To	Item_Name	Qty_In	Qty_Out	Unit_Cost	Value_Received	FIFOQtyOut	Balance InStore	ValueOut
01/09/2018	7889		Books	10				10	0	0
02/09/2018	H788727		Pens	0				0	0	0
02/09/2018	H83737	abc Ltd	Pencils	400				315	85	0
09/02/2018	76662	XYZ Ltd	Books	100	0		0	100	0	0
09/02/2018	76662	Abc Ltd	Ruled Papers	100	0	30	3000	15	85	0
09/04/2018	gyuu766	Admin	Books	0	90	0	0	0	0	0
09/04/2018	gyuu766	Sales	Pencils	0	90	0	0	0	0	0
			pens	100	0			4	96	0
09/09/2018	3029	abc Ltd	Chalk	200	0			30	170	0
08/10/2018	4399	Dpt QW	Books	0	4	0	0	0	0	0
09/09/2018	3029	dpt ed	Paracetamol 500mg Tabs	200	0			0	200	0
08/10/2018	4399	sales	pens	0	4	0	0	0	0	0
09/03/2018	32443r	Admin	Pencils	0	40	0	0	0	0	0
31/08/2018	fdg4521	Admin	Pencils	0	20	0	0	0	0	0
30/08/2018	46732	Admin	Pencils	0	50	0	0	0	0	0
25/08/2018	2443w	Admin	Pencils	0	20	0	0	0	0	0
23/08/2018	F6557	Admin	Pencils	0	10	0	0	0	0	0
24/08/2018	wtt56788	Admin	Pencils	0	25	0	0	0	0	0
29/08/2018	7766712	XYZ Ltd	Books	1000	0	50	50000	154	846	0
16/08/2018	88y77h	Admin	Books	0	150	0	0	0	0	0
15/08/2018	783h	Admin	Books	0	20	0	0	0	0	0
17/08/2018	34	Sales	Pencils	0	60	0	0	0	0	0
25/08/2018	43321	XYZ Ltd	Ruled Papers	890	0	50	44500	0	890	0
14/08/2018	245ew	Admin	Chalk	0	30	0	0	0	0	0
20/08/2018	4533	Admin	Ruled Papers	0	15	0	0	0	0

<colgroup><col style="mso-width-source:userset;mso-width-alt:4681;width:96pt" width="128"> <col style="width:48pt" span="10" width="64"> </colgroup><tbody>
</tbody>

I Appreciate every effort to help me.

Thank you