Hi everyone.
Currently im working on a macro that, in short, will read relevant cells of a worksheet (which is basically an order form) then compare them against another workbook (which is essentially a stock list). To do this it must read the code number for each part (this is stored in cells A, B & C of each row) in the first workbook then try to match this to the code numbers in the Stock workbook, in cells B, C & D.
One big obstacle ive found with this is that these code numbers arent in a neat list starting on the first row. They start anywhere from the 6th row down to the twentieth row down. In my code you'll see ive tried to use the VBA code that figures out what the last cell in a column is and then ive tried to set that as the range but so far to no avail.
If it finds a match i then wish it to check the quantity. In Parts workbook this is in column F and in Stock its in column L. I need the macro to work out which is greater than which then subtract the smaller. Once this is done it must update both of the affected cells in both of the workbooks.
Once completed and the quantity values have been updated the macro must move on to the next part and do all parts in the workbook.
Note: I use the word workbook because once one sheet in Parts has been completed it must move onto the next sheet, then repeat, until all sheeets are done. The Stock workbook only has one sheet.
Below is what little poor code ive come up with already. I do apologise for the quality, im still getting into excel and VBA.
The Code:
Sub StockCheck()
Dim PartSearch As Integer
'On Error GoTo Err_Execute
PartSearch = 1
While Len(Range("A" & CStr(PartSearch)).Value) < Range("A65536").End(xlUp).Select
If Range("F" & CStr(PartSearch)).Value > 0 Then
Cells.Range("A:C" & "F").Select
Selection.Copy
Workbooks("Kiverco Stocking System TEST").Activate
Sheets("Stock Items").Select
Cells.Range("W1:Z1").ActiveSheet.Paste
End If
If Range("B" & CStr(PartSearch)).Value = Range("W1") Then
If Range("C" & CStr(PartSearch)).Value = Range("X1") Then
If Range("D" & CStr(PartSearch)).Value = Range("Y1") Then
If Range("L" & CStr(PartSearch)).Value > Range("Z1").Value Then
'If Range("L").Value > Range("Z1").Value Then
Range("L") = Range("L") - Range("Z1")
Range("Z1") = 0
End If
Else
If Range("L" & CStr(PartSearch)).Value < Range("Z1").Value Then
Range("Z1") = ("Z1") - ("L")
Range("L") = 0
Else
Range("Z1") = 0
Range("L") = 0
End If
End If
End If
End If
'Err_Execute:
' MsgBox "An error occurred."
Cells.Range("Z1").Copy
Workbooks("Kiverco Parts Lists TEST").Activate
Sheets("1025 - Magnet Frames").Select
' Cells.Range("L").ActiveSheet.Paste
PartSearch = PartSearch + 1
Wend
End Sub
If anyone can help this would be genuinely appreciated. Also if the workbooks are needed to help i can create an example.
Much thanks in advance
Chris
Currently im working on a macro that, in short, will read relevant cells of a worksheet (which is basically an order form) then compare them against another workbook (which is essentially a stock list). To do this it must read the code number for each part (this is stored in cells A, B & C of each row) in the first workbook then try to match this to the code numbers in the Stock workbook, in cells B, C & D.
One big obstacle ive found with this is that these code numbers arent in a neat list starting on the first row. They start anywhere from the 6th row down to the twentieth row down. In my code you'll see ive tried to use the VBA code that figures out what the last cell in a column is and then ive tried to set that as the range but so far to no avail.
If it finds a match i then wish it to check the quantity. In Parts workbook this is in column F and in Stock its in column L. I need the macro to work out which is greater than which then subtract the smaller. Once this is done it must update both of the affected cells in both of the workbooks.
Once completed and the quantity values have been updated the macro must move on to the next part and do all parts in the workbook.
Note: I use the word workbook because once one sheet in Parts has been completed it must move onto the next sheet, then repeat, until all sheeets are done. The Stock workbook only has one sheet.
Below is what little poor code ive come up with already. I do apologise for the quality, im still getting into excel and VBA.
The Code:
Sub StockCheck()
Dim PartSearch As Integer
'On Error GoTo Err_Execute
PartSearch = 1
While Len(Range("A" & CStr(PartSearch)).Value) < Range("A65536").End(xlUp).Select
If Range("F" & CStr(PartSearch)).Value > 0 Then
Cells.Range("A:C" & "F").Select
Selection.Copy
Workbooks("Kiverco Stocking System TEST").Activate
Sheets("Stock Items").Select
Cells.Range("W1:Z1").ActiveSheet.Paste
End If
If Range("B" & CStr(PartSearch)).Value = Range("W1") Then
If Range("C" & CStr(PartSearch)).Value = Range("X1") Then
If Range("D" & CStr(PartSearch)).Value = Range("Y1") Then
If Range("L" & CStr(PartSearch)).Value > Range("Z1").Value Then
'If Range("L").Value > Range("Z1").Value Then
Range("L") = Range("L") - Range("Z1")
Range("Z1") = 0
End If
Else
If Range("L" & CStr(PartSearch)).Value < Range("Z1").Value Then
Range("Z1") = ("Z1") - ("L")
Range("L") = 0
Else
Range("Z1") = 0
Range("L") = 0
End If
End If
End If
End If
'Err_Execute:
' MsgBox "An error occurred."
Cells.Range("Z1").Copy
Workbooks("Kiverco Parts Lists TEST").Activate
Sheets("1025 - Magnet Frames").Select
' Cells.Range("L").ActiveSheet.Paste
PartSearch = PartSearch + 1
Wend
End Sub
If anyone can help this would be genuinely appreciated. Also if the workbooks are needed to help i can create an example.
Much thanks in advance
Chris
