I have to add a new function for inventory tracking but the code i came up with only works with some codes not all

S

svis89

Board Regular
Joined
Oct 19, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
VBA Code: 
    If Range("B2") = "" Then
Exit Sub
End If
  
    mycol = Sheets("Count sheet").Rows(1).Find("Defect", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
        
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A8000")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("A2:F2").Select
    Selection.Copy
    Sheets("Added to inv").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    Sheets("Inventory management").Range("E2").ClearContents
    Sheets("Inventory management").Range("B2").ClearContents
    
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
VBA Code: 
Range("A2:F2").Select

    Selection.Copy

    Sheets("Added to inv").Select

    Range("A5").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Rows("4:4").Select

    Application.CutCopyMode = False

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("A5").Select

This is the part i added Sorry, for some reason it just skips this step
 
Upvote 0
svis89 said:
VBA Code: 
Range("A2:F2").Select

    Selection.Copy

    Sheets("Added to inv").Select

    Range("A5").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Rows("4:4").Select

    Application.CutCopyMode = False

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("A5").Select

This is the part i added Sorry, for some reason it just skips this step
Click to expand...
The code besides the part i added someone here came up with
 
Upvote 0
Without seeing some sample data, and your code in its entirety (instead of bits and pieces in different posts), and an explanation of exactly what it is supposed to be doing (and what it is actually doing), it is difficult for us to help you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
VBA Code: 
[RANGE=rs:7|cs:6|w:Inventory List revised.xlsm|cls:xl2bb-200|s:Inventory management|tw:565][XR][XH][/XH][XH=w:72]A[/XH][XH=w:122]B[/XH][XH=w:97]C[/XH][XH=w:110]D[/XH][XH=w:110]E[/XH][XH=w:54]F[/XH][/XR][XR][XH]1[/XH][XD=h:c|v:m|fw:b|bc:83992A|c:FFFFFF|ch:28.5|cls:bl bt bb ww]Date[/XD][XD=h:c|v:m|fw:b|bc:83992A|c:FFFFFF|cls:bt bb ww]Item Number[/XD][XD=h:c|v:m|fw:b|bc:83992A|c:FFFFFF|cls:bt bb ww]Add to inventory[/XD][XD=h:c|v:m|fw:b|bc:83992A|c:FFFFFF|cls:bt bb ww]Remove from inventory[/XD][XD=h:l|v:m|fw:b|bc:83992A|c:FFFFFF|cls:bt bb ww]Scrapped[/XD][XD=h:c|v:m|fw:b|bc:83992A|c:FFFFFF|cls:bt br bb ww]Initials[/XD][/XR][XR][XH]2[/XH][XD=h:c|v:m|bc:EAF1CD|ch:49.5|cls:fx bl bt bb ww][FORMULA==TODAY()]12/30/2021[/FORMULA][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=h:c|v:m|bc:EAF1CD|cls:bt br bb ww]GSD[/XD][/XR][XR][XH]3[/XH][XD=ch:14.25|cls:bl bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt br bb][/XD][/XR][XR][XH]4[/XH][XD=bc:EAF1CD|ch:14.25|cls:bl bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt bb][/XD][XD=bc:EAF1CD|cls:bt br bb][/XD][/XR][XR][XH]5[/XH][XD=ch:14.25|cls:bl bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt bb][/XD][XD=cls:bt br bb][/XD][/XR][XR][XH]6[/XH][XD=ch:14.25|cls:bt][/XD][XD=cls:bt][/XD][XD=cls:bt][/XD][XD=cls:bt][/XD][XD=cls:bt][/XD][XD=cls:bt][/XD][/XR][XR][XH]7[/XH][XD=ch:14.25][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE][RANGE=cls:xl2bb-extra-200|t:cf][XR][XD]A2[/XD][XD=fw:b]A2[/XD][XD]=TODAY()[/XD][/XR][/RANGE]
 
Upvote 0
Inventory List revised.xlsm
ABCDEF
1DateItem NumberAdd to inventoryRemove from inventoryScrappedInitials
212/30/2021GSD
3
4
5
6
7
Inventory management
Cell Formulas
RangeFormula
A2A2=TODAY()
 
Upvote 0
Joe4 said:
Without seeing some sample data, and your code in its entirety (instead of bits and pieces in different posts), and an explanation of exactly what it is supposed to be doing (and what it is actually doing), it is difficult for us to help you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
the first post is the entire code for my sold macro this is a data entry tool that adjust the inventory based on what i enter it is working correctly in that regard but when it comes to copying it to a new sheet its not working right
if you can tell me what you need to see i will use the xl2bb to create a minisheet
 
Upvote 0
svis89 said:
the first post is the entire code for my sold macro
Click to expand...
It cannot be, as the first line of your code should contain "Sub...", and your first line does not.
So something has been cut-off.

Basically, I want to be able to recreate your exact situation on my side so I can test it out for myself.
So, I need you to provide:
1. The entire code
2. The data you are running it against
3. The expected result
4. The result you are getting

In looking at the code you have posted, it is referencing at least 3 sheets, and it appears to be looking for the word "Defect".
The single image you posted doesn't cover all those things.

The key to getting good help is to make it easy for people to help you, by asking good, complete questions, giving them all the details they need to help you.
There is a thread out there with some guidelines to posting: Guidelines
 
Upvote 0
Joe4 said:
It cannot be, as the first line of your code should contain "Sub...", and your first line does not.
So something has been cut-off.

Basically, I want to be able to recreate your exact situation on my side so I can test it out for myself.
So, I need you to provide:
1. The entire code
2. The data you are running it against
3. The expected result
4. The result you are getting

In looking at the code you have posted, it is referencing at least 3 sheets, and it appears to be looking for the word "Defect".
The single image you posted doesn't cover all those things.

The key to getting good help is to make it easy for people to help you, by asking good, complete questions, giving them all the details they need to help you.
There is a thread out there with some guidelines to posting: Guidelines
Click to expand...
VBA Code: 
Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
    
    If Range("B2") = "" Then
Exit Sub
End If
   
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
        
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A8000")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("D2")
    Next
    
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
        Range("A2:F2").Select
    Selection.Copy
    Sheets("Added to inv").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    
    Sheets("Inventory management").Range("D2").ClearContents
    Sheets("Inventory management").Range("B2").ClearContents


End Sub

VBA Code: 
Sub Defect_2()
'
' Defect Macro
'

'
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
    
    If Range("B2") = "" Then
Exit Sub
End If

    mycol = Sheets("Count sheet").Rows(1).Find("Defect", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
        
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A8000")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("A2:F2").Select
    Selection.Copy
    Sheets("Added to inv").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    
    Sheets("Inventory management").Range("E2").ClearContents
    Sheets("Inventory management").Range("B2").ClearContents
    
End Sub
both sets of full code for what im currently working on
 
Upvote 0
svis89 said:
VBA Code: 
Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
   
    If Range("B2") = "" Then
Exit Sub
End If
  
    mycol = Sheets("Count sheet").Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
       
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A8000")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("D2")
    Next
   
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
        Range("A2:F2").Select
    Selection.Copy
    Sheets("Added to inv").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
   
    Sheets("Inventory management").Range("D2").ClearContents
    Sheets("Inventory management").Range("B2").ClearContents


End Sub

VBA Code: 
Sub Defect_2()
'
' Defect Macro
'

'
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
   
    If Range("B2") = "" Then
Exit Sub
End If

    mycol = Sheets("Count sheet").Rows(1).Find("Defect", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
       
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A8000")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
   
    Sheets("Count sheet").Columns(mycol + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Range("A2:F2").Select
    Selection.Copy
    Sheets("Added to inv").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("4:4").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
   
    Sheets("Inventory management").Range("E2").ClearContents
    Sheets("Inventory management").Range("B2").ClearContents
   
End Sub
both sets of full code for what im currently working on
Click to expand...
Inventory List revised.xlsm
B
7
Inventory management

that is the data entry part
Inventory List revised.xlsm
ABCGHIJKLMKIKJKKKLKMLHNGNHNINJ
1Item numberPart numberIn stockBought12/30/2112/17/2112/10/2112/10/21SoldDefect
20000
30
Count sheet
Cell Formulas
RangeFormula
C2C2=[@[Added to INV]]-[@[Removed from INV]]-[@[Scrapped ]]
H2H2=SUM(I2:KC2)
NH2NH2=SUM(NI5140:NR5140)
KI2:KI3KI2=SUM(KJ2:NG2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A7848Cell ValueduplicatestextNO

that is the Inventroy counting part
Inventory List revised.xlsm
ABCDEFGHIJ
1
2DateItem NumberAdd to inventoryRemove from inventoryScrappedInitialsAdd, Remove or ScrapColumn1Column2Column3
3
4    
5    
612/30/202110211RRBAA  
712/30/202110211DDTAA  
8    
Inv Changes
Cell Formulas
RangeFormula
G4:G8G4=CONCAT(H4:J4)
H4:H8H4=IFS(C4="","",C4<="","A")
I4:I8I4=IFS(D4="","",D4<="","R")
J4:J8J4=IFS(E4="","",E4<="","S")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2Cell ValueduplicatestextNO
F2:G2Cell ValueduplicatestextNO
C2Cell ValueduplicatestextNO
A2Cell ValueduplicatestextNO
C2Cell ValueduplicatestextNO

That sheet is where it records what is added removed or scrapped that i can create my weekly reports from
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
209
Coco1
C
S
  • Question
Date stamp in Mycol row 1
Replies
0
Views
252
svis89
S
C
  • Solved
How to not set a range in VBA
Replies
7
Views
389
Coco1
C
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
333
RoryA
RoryA

Forum statistics

Threads
1,215,022
Messages
6,122,716
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top