Stock Control

**beseda2004** · 03-16-2012, 04:05 PM

Hello.
I am having troubles (due to lack of programming skills) with following situation.

My question is how to subtract the uniform from the "uniform table" when it is issued from the "new cadet" sheet or "issue extra uniform"?
Also, what would be the best way to add data into "Loan" sheet from the "New Cadet" sheet?

Regards, Vlad

**DGagnon** · 03-16-2012, 04:34 PM

The attached workbook is blank, could you please try attaching again?

**JieJenn** · 03-16-2012, 04:35 PM

For a second I thought I was blind and I didn't want to admit it.

**beseda2004** · 03-17-2012, 04:30 AM

Soz, something went wrong yesterday

**Fotis1991** · 03-17-2012, 04:48 AM

Hi

I am not sure if i understand corect your requietment..., but give a try to this, in G13.

=INDEX('New Cadet'!G12:G21,MATCH('Issue Uniform'!D13,'New Cadet'!D12:D21,0))

**beseda2004** · 03-17-2012, 09:20 AM

Sorry, it doesn't work in my spreadsheet. What it suppose to do?
I thought that it going to be a macro to solve that kind of problem.

**Fotis1991** · 03-17-2012, 11:08 AM

Formula works, OK, but really,i don't know if this is what are you looking for.

**beseda2004** · 03-17-2012, 11:34 AM

I am looking for a macro which will subtract qty of uniform from Uniform table, when that uniform is issued from the New Cadet worksheet (and Issue Equipment Sheet)

**DGagnon** · 03-18-2012, 08:33 PM

Have a look at the attached and see if that is what you are looking for, code bellow

Sub New_Cadet()
Application.ScreenUpdating = False
Dim Item As String
Dim Qty As Integer
Dim r As Integer

r = 12
ActiveWorkbook.Sheets("New Cadet").Select

    Do Until Range("D" & r).Value = ""
        Item = Range("D" & r).Value
        Qty = Range("E" & r).Value
        
        ActiveWorkbook.Sheets("uniform").Select
        For i = 3 To Range("A10000").End(xlUp).Row
            If Range("B" & i).Value = Item Then
                Range("C" & i).Value = Range("C" & i).Value - Qty
                Exit For
            End If
        Next
        
        ActiveWorkbook.Sheets("New Cadet").Select
        r = r + 1
    Loop
Application.ScreenUpdating = True
End Sub

Sub Extra_Item()
Application.ScreenUpdating = False
Dim Item, cdtNumber, cdtName As String
Dim Qty As Integer
Dim issueDate, returnDate As Date

ActiveWorkbook.Sheets("Issue Uniform").Select

cdtNumber = Range("B13").Value
cdtName = Range("C13").Value
Item = Range("D13").Value
Qty = Range("E13").Value
issueDate = Range("F13").Value
returnDate = Range("G13").Value

ActiveWorkbook.Sheets("uniform").Select
For i = 3 To Range("A10000").End(xlUp).Row
    If Range("B" & i).Value = Item Then
        Range("C" & i).Value = Range("C" & i).Value - Qty
        Exit For
    End If
Next
ActiveWorkbook.Sheets("Loan").Select

r = Range("A10000").End(xlUp).Row + 1

Range("A" & r).Value = cdtNumber
Range("B" & r).Value = cdtName
Range("C" & r).Value = Item
Range("D" & r).Value = Qty
Range("E" & r).Value = issueDate
Range("F" & r).Value = returnDate

ActiveWorkbook.Sheets("Issue Uniform").Select

Application.ScreenUpdating = True
End Sub

Sub Clear_Contents()
    Range("B11:B15").ClearContents
    Range("D12:E22").ClearContents
End Sub