USFengBULLS
Board Regular
- Joined
- May 7, 2018
- Messages
- 66
- Office Version
- 365
- Platform
- Windows
Hello All,
I have this code very close to what I need it to do but I think I need to add a counter of some sort. I'll try to explain this AS BEST AS POSSIBLE.
I need this code to count how many times there is an occurrence of an Alphanumeric value in Column C and then simply increment the number up + 1. Take a look at the data below:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
</tbody>[/TABLE]
If the user in column K (Not Shown here, These are column A, B, C) selects REVISE/AND RESUBMIT from a in cell Drop down it runs this code:
Public Sub REVISED(trow As Double)
Dim erow As Double
Dim Dn As Range
Dim Rng As Range
'Copy Cells down to next Blank Row
erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Range(Cells(trow, 1), Cells(trow, 3)).Copy Sheets("DRAWING SCHEDULE").Cells(erow, 1)
'Renames deplicate to REV #
Set Rng = Range(Range("A11"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn.Row = Rng(Rng.Count).Row Then
Dn.Offset(, 2).Value = Dn.Offset(, 2).Value & "/ REV " & Rng.Count - 2
End If
Next Dn
End Sub
It makes the copy down to the next blank row and adds "/ REV" just fine but it is the number value I cannot get right for the life of me. For Instance, If I select REVISE/AND RESUBMIT in the row 01 CASEWORK LEVEL 1/ AREA A/ RM 125 the Data table will look like this:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 7[/TD]
[/TR]
</tbody>[/TABLE]
Then If I select REVISE/AND RESUBMIT for 01 CASEWORK LEVEL 1/ AREA A/ RM 125/REV 7 will add it down and go up one to 01 CASEWORK LEVEL 1/ AREA A/ RM 125/REV 8.
Let me give a scenario where it would be working perfectly. In the first data set above, say a user selects REVISE/AND RESUBMIT to the following rows in this order
01 CASEWORK LEVEL 1/AREA A/ RM 125 then 01 CASEWORK LEVEL 1/ AREA A/ RM 126 then again but to the new 01 CASEWORK LEVEL 1/AREA A/ RM 125/ REV 1 (not the original 01 CASEWORK LEVEL 1/AREA A/RM 125 Row, the new row once this code is working properly) the data table should look like this:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 605"]
<tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 1[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126/ REV 1[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 2[/TD]
[/TR]
</tbody>[/TABLE]
How can I write the code to where it is keeping track of the times this string has occurred in the column and just go up + 1 from that? Also, if it the first time being revised it has to add the "/ REV 1" and then each revision after for that particular Room/ Location it needs to go up a revision Number.
Sorry for the long post but I've tried to get help on this before and it seems I am not explaining it clearly, my bad.
If it helps, here is a dropbox link with the data in it. Go to sheet DRAWING SCHEDULE and in Column K you'll find the In cell drop down to select REVISE/AND RESUBMIT to test this code. Thanks!
https://www.dropbox.com/s/y1oqkgfe6ly84pj/MR Drawing Transmittals Master Form 2019 v1.0.xlsm?dl=0
I have this code very close to what I need it to do but I think I need to add a counter of some sort. I'll try to explain this AS BEST AS POSSIBLE.
I need this code to count how many times there is an occurrence of an Alphanumeric value in Column C and then simply increment the number up + 1. Take a look at the data below:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
</tbody>[/TABLE]
If the user in column K (Not Shown here, These are column A, B, C) selects REVISE/AND RESUBMIT from a in cell Drop down it runs this code:
Public Sub REVISED(trow As Double)
Dim erow As Double
Dim Dn As Range
Dim Rng As Range
'Copy Cells down to next Blank Row
erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Range(Cells(trow, 1), Cells(trow, 3)).Copy Sheets("DRAWING SCHEDULE").Cells(erow, 1)
'Renames deplicate to REV #
Set Rng = Range(Range("A11"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn.Row = Rng(Rng.Count).Row Then
Dn.Offset(, 2).Value = Dn.Offset(, 2).Value & "/ REV " & Rng.Count - 2
End If
Next Dn
End Sub
It makes the copy down to the next blank row and adds "/ REV" just fine but it is the number value I cannot get right for the life of me. For Instance, If I select REVISE/AND RESUBMIT in the row 01 CASEWORK LEVEL 1/ AREA A/ RM 125 the Data table will look like this:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 7[/TD]
[/TR]
</tbody>[/TABLE]
Then If I select REVISE/AND RESUBMIT for 01 CASEWORK LEVEL 1/ AREA A/ RM 125/REV 7 will add it down and go up one to 01 CASEWORK LEVEL 1/ AREA A/ RM 125/REV 8.
Let me give a scenario where it would be working perfectly. In the first data set above, say a user selects REVISE/AND RESUBMIT to the following rows in this order
01 CASEWORK LEVEL 1/AREA A/ RM 125 then 01 CASEWORK LEVEL 1/ AREA A/ RM 126 then again but to the new 01 CASEWORK LEVEL 1/AREA A/ RM 125/ REV 1 (not the original 01 CASEWORK LEVEL 1/AREA A/RM 125 Row, the new row once this code is working properly) the data table should look like this:
[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 124[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 134[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 125[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 133[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]WALL PANELS[/TD]
[TD]LEVEL 1/ AREA A/ RM 134
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 605"]
<tbody>[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 1[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 126/ REV 1[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]CASEWORK[/TD]
[TD]LEVEL 1/ AREA A/ RM 125/ REV 2[/TD]
[/TR]
</tbody>[/TABLE]
How can I write the code to where it is keeping track of the times this string has occurred in the column and just go up + 1 from that? Also, if it the first time being revised it has to add the "/ REV 1" and then each revision after for that particular Room/ Location it needs to go up a revision Number.
Sorry for the long post but I've tried to get help on this before and it seems I am not explaining it clearly, my bad.
If it helps, here is a dropbox link with the data in it. Go to sheet DRAWING SCHEDULE and in Column K you'll find the In cell drop down to select REVISE/AND RESUBMIT to test this code. Thanks!
https://www.dropbox.com/s/y1oqkgfe6ly84pj/MR Drawing Transmittals Master Form 2019 v1.0.xlsm?dl=0