asked on

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.

Doug

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?

Saqib Husain

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

Milind Agarwal

ASKER

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

ASKER CERTIFIED SOLUTION

Doug

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

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

Milind Agarwal

ASKER

Thanks Bugdrown and sktneer. Appreciate it