I need some guidance to get the attatched code to automatically delete the corresponding materialnumber in sheet 1 when Location ID is deleted from sheet 2 - Also the date for the change must be displayed in sheet 1. Then the code is complete..
See attachment and point 2 for detailes.
Anyone ?
Do you want to delete all values of a.1.1 or each item individually?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Each corresponding item individually.
---------------------------------------------------------
I need some guidance to get the attatched code to automatically delete the corresponding materialnumber (20345, 20232 ....)in sheet 1 when Location ID (A.1.1) is deleted from sheet 2 - Also the date (19.02.08) for the change must be displayed in sheet 1. Then the code is complete..
The workbook contains less than 1.000 materialnumbers and Location ID numbers.
See attachment and point 2 for more detailes.
Sheet 1
A B C D E F
ROM 315
LOC A.1.1 A.2.1 A.3.1 A.4.1 A.5.1
MAT1 20345 20345
MAT2 20232 99999
MAT3 20452
MAT4 30458
DATE 19.02.08 20.02.08
LOC A.1.2 A.2.2 A.3.2 A.4.2 A.5.2
MAT1 30360 20345
MAT2 30390
MAT3 33392
MAT4 44444
DATE 20.02.08 20.02.08
Sheet 2
Item and Location
20345 a.1.1 a.2.1 a.5.2
20232 a.1.1
20452 a.1.1
30458 a.1.1
30360 a.1.2
30390 a.1.2
33392 a.1.2
44444 a.1.2
99999 a.2.1
Last edited by tomjoe; 02-21-2008 at 10:33 AM.
As you already have worksheet change code, you'll have to incorporate this into it, or call it from your code.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tgt As Long, c As Range, d As Range
If Target = "" And cellValueOnEntry Like "[a-z][.][1-9][.][1-9]" Then
Tgt = Target.Offset(, -1)
Set c = Sheets("Lokasjon").Cells.Find(what:=cellValueOnEntry, lookat:=xlWhole, MatchCase:=False)
Set d = c.Resize(5).Find(what:=Tgt, lookat:=xlWhole)
If Not d is Nothing then
d.ClearContents
c.Offset(5) = Date
End If
End If
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Where exactly in the attached code should I incorporate your code for it to work ?
[vba]
Private Sub Update(Target As Range)
Dim Tgt As Long, c As Range, d As Range
If Target = "" And cellValueOnEntry Like "[a-z][.][1-9][.][1-9]" Then
Tgt = Target.Offset(, -1)
Set c = Sheets("Lokasjon").Cells.Find(what:=cellValueOnEntry, lookat:=xlWhole, MatchCase:=False)
Set d = c.Resize(5).Find(what:=Tgt, lookat:=xlWhole)
If Not d Is Nothing Then
d.ClearContents
c.Offset(5) = Date
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Const dateRowID = "DATE"
Dim matchedLocIDAddress As String
Dim destSheet As Worksheet
Dim baseCell As Range
Dim lastRow As Long
Dim startRow As Long
Dim endRow As Long
Dim rOffset As Long
Dim processedFlag As Boolean
Dim materialIDCode As Long ' to store 442, 485, etc codes
Call Update(Target)
'etc.
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thank you mdmackillopp
This worked fine when I deleted Location ID's in sheet 2 Column B, the corresponding location number in sheet 1 was deleted and the date for the change was updated. But I need to work with Location ID's (create and delete) in all the other columns in sheet 2 as well, and get the same function.
Is it just a small adjustment in the code you posted for it to work in the other columns also ?
Does my code not do this? If not, please post a more representitive sample.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Still something wrong (Updated 02-24-2008)
Hello again
I still cannot get the workbook to behave as intended.
I also see that I have been to inaccurate in describing what I want the code to perform. I appologize and will now try to explain the scenario more accurate. Some changes have been done as to my former description.
THIS IS THE PROJECT:
The Location ID's is to be the same all the time (A.1.1, A.2.1, etc) as set up in Sheet 1 (Lokasjon). These Location ID's is also listed in the last column of Sheet 2, in order to use a list Function in this sheet.
The Item numbers in column A Sheet 2 ( 40032, 40033, 40045 etc) will be changed (old ones deleted and new ones adding). This the users will do by deleting or inserting the entire row in Sheet 2.
------------------------------------------------
What I want the code to do:
1.
New Location ID registrations in Sheet 2 shall look in the same row, column A and register this unik Item number in sheet 1 in the first free cell MAT1, MAT2, MAT3 or MAT4 under the Location ID. Also the date of the last registration in these cells must register in the DATE cell.
2.
Also when the Location ID entries in sheet 2 is deleted, the system must behave as described over but now delete the unike Item number in Sheet 1, without changing the DATE. I guess in order to handle this that when an Item number and the entire row is to be deleted (as described in "THIS IS THE PROJECT") the system must not accept this deletion except the entire row to the right of the Itemnumber is empty.
3.
When a Location ID registration in a cell in sheet 2 is changed to a new Location ID, the system must delete the former Item number allocated to the Location ID in Sheet 1 without changing the DATE, and register the Item number under the new Location ID with a change in the allocated DATE cell.
So no deletions is to be registered in the DATE cell, only the DATE of the new Item number registrations.
Also I do not want the text: "You have changed the value..." to pop up When I change a Location Id (A.1.1, A.1.2 etc.) in Sheet 2. I know that this text comes from the code, but I am actually so new to this that I cannot find out of this on my own.
Can I ask you to take a look in the attachment and make the appropriate changes directly in the code ? Hope it is not to much to ask for...
Last edited by tomjoe; 02-24-2008 at 01:52 AM. Reason: For a more distinct description of the scenario
I have changed my previous post and made it more accurate..
Originally Posted by tomjoe
Hello again
I still cannot get the workbook to behave as intended.
I also see that I have been to inaccurate in describing what I want the code to perform. I appologize and will now try to explain the scenario more accurate. Some changes have been done as to my former description.
THIS IS THE PROJECT:
The Location ID's is to be the same all the time (A.1.1, A.2.1, etc) as set up in Sheet 1 (Lokasjon). These Location ID's is also listed in the last column of Sheet 2, in order to use a list Function in this sheet.
The Item numbers in column A Sheet 2 ( 40032, 40033, 40045 etc) will be changed (old ones deleted and new ones adding). This the users will do by deleting or inserting the entire row in Sheet 2.
------------------------------------------------
What I want the code to do:
1.
New Location ID registrations in Sheet 2 shall look in the same row, column A and register this unik Item number in sheet 1 in the first free cell MAT1, MAT2, MAT3 or MAT4 under the Location ID. Also the date of the last registration in these cells must register in the DATE cell.
2.
Also when the Location ID entries in sheet 2 is deleted, the system must behave as described over but now delete the unike Item number in Sheet 1, without changing the DATE. I guess in order to handle this that when an Item number and the entire row is to be deleted (as described in "THIS IS THE PROJECT") the system must not accept this deletion except the entire row to the right of the Itemnumber is empty.
3.
When a Location ID registration in a cell in sheet 2 is changed to a new Location ID, the system must delete the former Item number allocated to the Location ID in Sheet 1 without changing the DATE, and register the Item number under the new Location ID with a change in the allocated DATE cell.
So no deletions is to be registered in the DATE cell, only the DATE of the new Item number registrations.
Also I do not want the text: "You have changed the value..." to pop up When I change a Location Id (A.1.1, A.1.2 etc.) in Sheet 2. I know that this text comes from the code, but I am actually so new to this that I cannot find out of this on my own.
Can I ask you to take a look in the attachment and make the appropriate changes directly in the code ? Hope it is not to much to ask for...
The overall concept is that sheet 1 gives an overview of the LOCATIONS in an increasing order and then is to be used as some sort of map looking for free space when incoming goods are to be placed and also visualizing the physical location of spesific Items when needed in the production line.
Sheet 2 gives an overview of the ITEMNUMBERS in increasing order down column A and is to be used as a quick referance to see if an Item is available at the warehouse and at what Location ID it is to be found.
So far this is the status of the code:
To enter the Location ID's (A.1.1, A.2.1 etc..) in Sheet 2 and get the corresponding Itemnumbers (40032, 40033 etc.) in the same row from Column A Sheet 2, to register under the right Location ID number in Sheet 1 consecutively in the first none occupied row MAT1, MAT2, MAT3 and MAT4, works fine.
Also the Date for the last Item registration is automatically noted in the DATE row. At last, when all 4 Itemnumbers (MAT1, MAT2 etc) is occupied the code correctly gives the message "No empty location found to place the new entry into the group." So thats all OK.
The problem is when I try to change an occupied cell in Sheet 2 by first deleting the previous entered Location ID number or by directly entering a new Location ID number in the cell I get the message "You have changed the value of a previously processed entry".
I do not want the code to give this message, but rather to not accept a direct change of the Location ID registration in a cell in sheet 2. I want the system to prompt me to first delete the former Location ID. This for safety.
The deletion of the Item number must occur without changing the DATE in Sheet 1, but the corresponding Itemnumber must be erased.
Then the user can register the Itemnumber under the new Location ID with a change in the corresponding DATE cell in Sheet 1.
That's all.
Any idea of what to change in the code ?
Posting Permissions
Reply With Quote