If values in Column L or Column Q or Column V = Y, insert specific text into Column W and Column i or Column N or Column S date in X.

Hi All,
Below Macro updates the column W with a text "Closed" if Column L is =  'Y' .  I also like to add additional logic.

if Column L is Y, Update Column W with a text Closed, Also copy Column I date(Data present in this column)  into X.
if Column Q is Y, Update Column W with a text Closed, Also copy Column N date(Data present in this column)  into X.
If Column V is Y, Update column W with a text Closed, Also Copy Column S date (Data present in this column)  into X.

Also, Once the macro is run it should clear the previous data instead of placing Closed Closed twice next to each other if the macro ran twice.

Any help would be wonderful.

Private Sub UpdateColumnW()
    Dim x As Long
    For x = 1 To 65536
        If InStr(1, Sheet1.Range("$L$" & x), "Y") > 0 Then
            Sheet1.Range("$W$" & x) = Sheet1.Range("$W$" & x) & "Closed"
        End If
    Next
End Sub

Thanks.
Milind AgarwalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DougCommented:
Can all of the cells in the same row be "Y" at the same time? In other words, could cells L2, Q2, and V2 all be "Y", or could any two columns be "Y"? If so, then what is the rule? If not, what prevents that from happening?
0
Saqib Husain, SyedEngineerCommented:
Try this. I have not tested it. You may provide a file for testing.


    For x = 1 To 65536
    for j=0 to 10 step 5
        If InStr(1, Sheet1.Range("$L$" & x), "Y").offset(,j) > 0 Then
            Sheet1.Range("$W$" & x) =  "Closed"
            Sheet1.Range("$i$" & x).offset(,j).copy =  Sheet1.Range("$X$" & x)
        End If
    Next
    Next
0
Milind AgarwalAuthor Commented:
Hi Bugdrown,
No, Only one of the cells can be 'Y '.  Please refer to the sample attached.

Hi Saqib,
I have attached a sample file.

Thanks
Sample-Log.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DougCommented:
Milind,

The attached is a solution using formulas rather than vba code. The formulas will do what you're asking. The formulas are in columns W (Status) and X (Date Closed). I've tested it but you should certainly test it as well.
Sample-Log_bugdrown.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this to see if this is what you are trying to achieve....
Sub UpdateColumnsWAndX()
Dim ws As Worksheet
Dim lr As Long
Dim cell As Range
Set ws = Sheet1
lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws.AutoFilterMode = 0
With ws.Rows(2)
   .AutoFilter field:=12, Criteria1:="y"
   If ws.Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      ws.Range("X3:X" & lr).SpecialCells(xlCellTypeVisible).Value = ws.Range("I3:I" & lr).SpecialCells(xlCellTypeVisible).Value
      For Each cell In ws.Range("W3:W" & lr).SpecialCells(xlCellTypeVisible)
         If Right(cell, 6) <> "Closed" Then
            cell = cell & " Closed"
            ws.Cells(cell.Row, "X") = ws.Cells(cell.Row, "I")
         End If
      Next cell
   End If
   .AutoFilter field:=12
   .AutoFilter field:=17, Criteria1:="y"
   If ws.Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      ws.Range("X3:X" & lr).SpecialCells(xlCellTypeVisible).Value = ws.Range("I3:I" & lr).SpecialCells(xlCellTypeVisible).Value
      For Each cell In ws.Range("W3:W" & lr).SpecialCells(xlCellTypeVisible)
         If Right(cell, 6) <> "Closed" Then
            cell = cell & " Closed"
            ws.Cells(cell.Row, "X") = ws.Cells(cell.Row, "N")
         End If
      Next cell
   End If
   .AutoFilter field:=17
   .AutoFilter field:=22, Criteria1:="y"
   If ws.Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      ws.Range("X3:X" & lr).SpecialCells(xlCellTypeVisible).Value = ws.Range("I3:I" & lr).SpecialCells(xlCellTypeVisible).Value
      For Each cell In ws.Range("W3:W" & lr).SpecialCells(xlCellTypeVisible)
         If Right(cell, 6) <> "Closed" Then
            cell = cell & " Closed"
            ws.Cells(cell.Row, "X") = ws.Cells(cell.Row, "S")
         End If
      Next cell
   End If
   .AutoFilter
End With
MsgBox "Done!", vbInformation
End Sub

Open in new window

1
Milind AgarwalAuthor Commented:
Thanks Bugdrown and sktneer. Appreciate it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.