alisoncleverly
New Member
 Joined
 Feb 20, 2020
 Messages
 28
 Office Version

 2013
 Platform

 Windows
Hi,
Here is my sample file.
Can you please advise how I can fix the following codes so that they do not class with each anymore?
A snapshot on what the code should have done (but it couldn't):
1) If "Shipped" in column AU and no "x" in column AX, then return "Rollup" for all empty Sales and Production cells
2) If "Title Transfer" in the last Sales column and no "Shipped" in column AU, return "x" in column AX
3) If "Title Transfer" in the last Sales column (has to happen first) and "Shipped" in column AU (happens later), return "x" in column AX and "Shipped" in all empty Sales and Production cells (this, I haven't figured out how to)
So, I have this part to return "Rollup" for any empty Sales and Production cells if "Shipped" in Column AU (47th column).
Then I have this one to return "x" in column AX (50th column) if the last Sales column has "Title Transfer".
Can you please advise how I can fix the following codes so that they do not class with each anymore?
FYI, I have 8 Days in total, each Day is a combination of 4 columns with the exact same order: Sales, Production, Day, Status. The range is from column N to column AS.
As seen from the the Master Worksheet tab in my sample file:
1) the 1st row did exactly what I wanted. It did evaluate correctly the "Title Transfer" in column Sales/column R (of Day 2) to be the last Sales column that has "Title Transfer" and return an "x" in column AX
2) the 2nd row, the codes returned both correct and wrong results. I had put "Title Transfer" in a Sales column first, which the Macro then returned an "x" in column AX. That's correct.
However, when I put "Shipped" in column AU after having put "Title Transfer" first in column AX, the "x" was replaced by the Shipped codes I posted above. It did return "Rollup" for all empty Sales and Production cells when I put "Shipped" in column AU (47th column). But the "x" to indicate Title Transfer was gone.
So here comes the problem I've been struggling with for the past week. Can you please advise how I can fix this problem? I suspect that when the empty Sales and Production cells are set to "Rollup" then "Title Transfer" is no longer the last Sales column (MaxCol) and the else condition Cells(Target.Row, 50).Value = "" removes the "x". Still, not sure how to fix that problem.
3) the 3rd row is what I wanted my codes did if both "Shipped" in column AU and "x" in column AX (please also help me with this one)
Can you please help how to get it to work that way? Thanks a lot and please let me know if you need more info.
Here is my sample file.
Can you please advise how I can fix the following codes so that they do not class with each anymore?
A snapshot on what the code should have done (but it couldn't):
1) If "Shipped" in column AU and no "x" in column AX, then return "Rollup" for all empty Sales and Production cells
2) If "Title Transfer" in the last Sales column and no "Shipped" in column AU, return "x" in column AX
3) If "Title Transfer" in the last Sales column (has to happen first) and "Shipped" in column AU (happens later), return "x" in column AX and "Shipped" in all empty Sales and Production cells (this, I haven't figured out how to)
So, I have this part to return "Rollup" for any empty Sales and Production cells if "Shipped" in Column AU (47th column).
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim counter As Long
Dim lastcolumn As Long
lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
'Shipped without Title Transfer
If Me.Cells(1, Target.Column).Value = "MB51 Shipped" Then
For counter = 1 To lastColumn
If (Me.Cells(1, counter).Value = "Sales" Or Me.Cells(1, counter).Value = "Production") _
And IsEmpty(Me.Cells(Target.Row, counter).Value) Then
Me.Cells(Target.Row, counter).Value = "Rollup"
End If
Next counter
End If
Then I have this one to return "x" in column AX (50th column) if the last Sales column has "Title Transfer".
VBA Code:
Dim r As Range, r1 As Range, counter As Long
Dim MaxCol As Variant, rg As Range, j As Long
If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(Target.Column).Resize(, 3))
Call DoCells(r)
End If
'Automatically put "x" if Title Transfer in any Sales columns
If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
If Target.CountLarge > 1 Then Exit Sub
Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
MaxCol = 0
For j = Columns("AP").Column To Columns("N").Column Step 4
If Cells(Target.Row, j) <> "" Then
If j > MaxCol Then MaxCol = j
End If
Next
If MaxCol Mod 4 = 2 Then
If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
Cells(Target.Row, 50).Value = "x"
Else
Cells(Target.Row, 50).Value = ""
End If
End If
End If
'This I have 8 Sales Column, however, I only put 1 line down for demonstration
Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales1).Resize(, 3))
If Not r Is Nothing Then Call DoCells(r)
If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
If Target.CountLarge > 1 Then Exit Sub
Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
MaxCol = Evaluate("=MAX(IF(" & rg.Address & "<>"""",COLUMN(" & rg.Address & ")))")
If MaxCol Mod 4 = 2 Then
If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
Cells(Target.Row, 50).Value = "x"
Else
Cells(Target.Row, 50).Value = ""
End If
End If
End If
End Sub
Can you please advise how I can fix the following codes so that they do not class with each anymore?
FYI, I have 8 Days in total, each Day is a combination of 4 columns with the exact same order: Sales, Production, Day, Status. The range is from column N to column AS.
As seen from the the Master Worksheet tab in my sample file:
1) the 1st row did exactly what I wanted. It did evaluate correctly the "Title Transfer" in column Sales/column R (of Day 2) to be the last Sales column that has "Title Transfer" and return an "x" in column AX
2) the 2nd row, the codes returned both correct and wrong results. I had put "Title Transfer" in a Sales column first, which the Macro then returned an "x" in column AX. That's correct.
However, when I put "Shipped" in column AU after having put "Title Transfer" first in column AX, the "x" was replaced by the Shipped codes I posted above. It did return "Rollup" for all empty Sales and Production cells when I put "Shipped" in column AU (47th column). But the "x" to indicate Title Transfer was gone.
So here comes the problem I've been struggling with for the past week. Can you please advise how I can fix this problem? I suspect that when the empty Sales and Production cells are set to "Rollup" then "Title Transfer" is no longer the last Sales column (MaxCol) and the else condition Cells(Target.Row, 50).Value = "" removes the "x". Still, not sure how to fix that problem.
3) the 3rd row is what I wanted my codes did if both "Shipped" in column AU and "x" in column AX (please also help me with this one)
Can you please help how to get it to work that way? Thanks a lot and please let me know if you need more info.