Excel VBA places value in cell that contains "Yes" in range

[JasonWilliam]

I have a excel sheet that I need help with and I'm sure it is an easy code. I want to add to my code that I already have a section where if the value in the "P" column is "Yes" then it returns a "Sent" value in the "AA" column of the same row. Any help with this would be greatly appreciated.

This is my code I have right now. I know this is probably an easy add on, but my brain is fried right now and I'm drawing a blank.

Private Sub CommandButton2_Click()
Dim WB As Workbook
Dim WS As Worksheet
Dim AC As String: AC = Sheets("OPS").Range("E1").Value
Dim BC As String: BC = Sheets("OPS").Range("E2").Value
Dim RevValue As Variant

ResetButton CommandButton2

Application.Workbooks.Add xlWBATWorksheet
Set WB = ActiveWorkbook
Set WS = ActiveSheet

With ThisWorkbook.Worksheets("OPS")
.Range("A3:Q1000").Copy
WS.Range("A3").PasteSpecial xlPasteValues
End With

WS.Range("P2").Value = "FilterCol"
WS.Columns.AutoFilter field:=16, Criteria1:="<>Yes", Criteria2:="<>user_str_3"
WS.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
WS.AutoFilterMode = False

WB.SaveAs Filename:="G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.csv", FileFormat:=xlCSV
WB.Close False

FileCopy "G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.csv", _
"G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.syn"

Range("P4:P1000").ClearContents

End Sub

 

[rlv01]

I probably would not add it to your code. Instead I would use the Worksheet Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    If Not Application.Intersect(Target, Me.Columns("P")) Is Nothing Then
        Application.EnableEvents = False
        For Each R In Target
        If R.Value = "Yes" Then
            R.Offset(0, 11).Value = "Sent"
        Else
            R.Offset(0, 11).Value = ""
        End If
        Next R
        Application.EnableEvents = True
    End If
End Sub

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Logit]

In AA4 type :

=IF(P4 = "Yes","Sent","")

Then drag that down column AA as faras required.

 

[JasonWilliam]

Thats how I used to do it. The problem I have with that is I have the "Yes" value erase after I send the part to a csv file. I have been having an issue with not knowing if I really sent the part or not.

 

[JasonWilliam]

Thank you for the reply!

 

[JasonWilliam]

There was an issue with the code, but I got it to work. I just made it do nothing instead of making it return a "" if nothing was there...

Looks like this now

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    If Not Application.Intersect(Target, Me.Columns("P")) Is Nothing Then
        Application.EnableEvents = False
        For Each R In Target
        If R.Value = "Yes" Then
            R.Offset(0, 11).Value = "Sent"
        Else
            'R.Offset(0, 11).Value = ""
        End If
        Next R
        Application.EnableEvents = True
    End If
End Sub

 

[rlv01]

Looks like this now

I don't see a difference. Are you sure you posted your changed code?

 

[JasonWilliam]

I actually changed it again after this...

Updated code.

Private Sub CommandButton2_Click()
Dim WB As Workbook
    Dim WS As Worksheet
    Dim AC As String: AC = Sheets("OPS").Range("E1").Value
    Dim BC As String: BC = Sheets("OPS").Range("E2").Value
    Dim RevValue As Variant
    Dim R As Range
    Dim SEL As String
    
    
    ResetButton CommandButton2
    
    Application.Workbooks.Add xlWBATWorksheet
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet

    With ThisWorkbook.Worksheets("OPS")
        .Range("A3:Q1000").Copy
        WS.Range("A3").PasteSpecial xlPasteValues
    End With
    
    ZN = AC & "-" & BC
            
    WS.Range("P2").Value = "FilterCol"
    WS.Columns.AutoFilter field:=16, Criteria1:="<>Yes", Criteria2:="<>user_str_3"
    WS.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    WS.AutoFilterMode = False
    
    SEL = WS.Range("Q3").Value
    
    WB.SaveAs Filename:="G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.csv", FileFormat:=xlCSV
    WB.SaveAs Filename:="G:\Laser\OPS\Shop Parts\CSV\" & SEL & ".csv", FileFormat:=xlCSV
    WB.Close False
    
    FileCopy "G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.csv", _
    "G:\Laser\ORDERS\ORDER_" & AC & "-" & BC & "_REV.syn"
    
    
      For Each R In Selection
        If R.Value = "Yes" Then
            R.Offset(0, 11).Value = "Sent"
        Else
           'R.Offset(0, 11).Value = ""
        End If
    Next R
    
    
    
  
    Range("P4:P1000").ClearContents
           
    End Sub

With this it only does it when I press the button then erases the "Yes" in the "P" column. It took me a few tries to get this right, but it seems to work now. Also turned the Else into a comment so it does nothing if there is no "Yes". I know that might not be correct, but it works.