Look for last column that has a specific value

[alisoncleverly]

Hi everyone,

I tried the following code to do: If any Sales cells have "Title Transfer" then the 51st column would have an "x".

I have 8 columns named "Sales" in total. The 5st column is named "Title Transfer".

Option Explicit
Public Const colTTransfer As Long = 51

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lastColumn As Long
Dim counter As Long

lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column

    If Me.Cells(1, Target.Column).Value = "Sales" Then

        For counter = 1 To lastColumn

            If Me.Cells(Target.Row, counter).Value = "Title Transfer" Then

            Me.Cells(Target.Row, colTTransfer).Value = "x"

            End If

        Next counter

    End If

End Sub

However, I realised there was more to my original purpose and the above codes weren't flexible.
I'm wondering if there is a way to only return "x" for the last Sales column that has "Title Transfer"?

For example, assuming these events happen in the same row where:

1st Sales column has Green and 51st column remain blank
2nd Sales column has Title Transfer and 51st column has x
3rd Sales column has Rollup and 51st column turns blank
4th Sales column has Red and 51st column remains the same
5th Sales column has Title Transfer and 51st column now has x and so on

FYI:
1st Sales column is column N
2nd Sales column is column R
3rd Sales column is column V
4th Sales column is column Z
5th Sales column is column AD
6th Sales column is column AH
7th Sales column is column AL
8th Sales column is column AP

Really sorry that I couldn't include a sample here since my work laptop doesn't allow me to download any add-in, including XL2BB.

Please advise how I can make it work that way. Any help is highly appreciated! Thanks a lot!

 

[DanteAmor]

Do you have formulas in the cells?

Or maybe you have cells filled with blanks.

 

[alisoncleverly]

Do you have formulas in the cells?

Or maybe you have cells filled with blanks.

Hi Dante, within the range N : AY, the following columns have formula: Q, U, Y, AC, AG, AK, AO, AS, AT. Would that be the cause here?

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, r1 As Range, lastColumn As Long, 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
  
  ' Get last column based on first row
  lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
  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
   
  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, 51).Value = "x"
      Else
        Cells(Target.Row, 51).Value = ""
      End If
    End If
  End If
End Sub

 

[alisoncleverly]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, r1 As Range, lastColumn As Long, 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
 
  ' Get last column based on first row
  lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
  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
  
  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, 51).Value = "x"
      Else
        Cells(Target.Row, 51).Value = ""
      End If
    End If
  End If
End Sub

Good morning Dante, thank you so much for your tremendous help so far. The codes work perfectly now!!!! Thanks a lot!!!!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[alisoncleverly]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, r1 As Range, lastColumn As Long, 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

  ' Get last column based on first row
  lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
  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
 
  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, 51).Value = "x"
      Else
        Cells(Target.Row, 51).Value = ""
      End If
    End If
  End If
End Sub

Hi Dante, I came back hoping you can help me out with another favour.

Is there a way to incorporate the following conditions to the codes you wrote?

Currently I have this bit of codes (can be seen from the codes you provided)

'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

What it does is to automatically return "Rollup" for all empty Sales and Production cells if "Shipped" in Column AV (48th column), however, I found it not the most effective way to go around.

I'm also thinking of adding another condition to that bit of codes which is:

If in Day n, a cell in Column AY (51st column) has "x", and if in Day (n+1), that cell has "Shipped" in column AV (48th column), then all Sales and Production cells from Day(n+1) onwards will become "Shipped".

See below print screen of the desired output

I'm also having an issue where if I put "x" in column AY first, then later on put "Shipped" in column AV, the "x" is wiped out and is replaced by the condition written by the codes mentioned above (If "Shipped" in AV then "Rollup" for all empty Sales and Production cells).

There'd never be a case where "Shipped" in column AV first then "x" in column AY later. It has to be the other way around which is "x" in column AY first then can be "Shipped" in column AV later.

Now thinking back, what I wrote contradicts what you provided, hmmm...

Just a quick note:

1. I have 8 Days in total, each Day has Sales, Production, Day #, Status columns
2. 1st Sales column is column N
3. 1st Production column is column O
4. All "Status" columns have formula in them

Can you please kindly advise? I really really appreciate your help so far! Thank you thank you thank you!