VBA to add text to one column when another column is no longer empty

[BlakeVanderMeer]

Hi everyone, New here and new to VBA macros. most of my manipulations are done in python before working with the excel sheets... anyways,

I am hoping we someone can help me with what is probably pretty simple. I have a spreadsheet with a list of names in column A, starting in A2 and will run infinitely. I am looking to have (D) added to the end of the name when a date is entered into column C. Column C will be empty until someone manually adds a date to that position.

Here is the current code I have, Though it causes a debug error and isn't working. Thanks for any help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target <> "" Then
Cells(lngRow, "B") = Cells(lngRow, "A") & "-Removed"
Else
Range("A" & Target.Row) = ""
End If
End Sub

 

[JLGWhiz]

Is lngRow declared somewhere as a global variable? If not, the code will fail because the compiler does not know what value lngRow is supposed to represent. You need to declare lngRow as a variable and then initialize it by giving it a value. If it is the same as the Target.Row, then just use Target.Row.

 

[BlakeVanderMeer]

Is lngRow declared somewhere as a global variable? If not, the code will fail because the compiler does not know what value lngRow is supposed to represent. You need to declare lngRow as a variable and then initialize it by giving it a value. If it is the same as the Target.Row, then just use Target.Row.

no, I was trying to learn by looking at code from other peoples scripts, but haven't had any luck. I will try and replace it with Target.Row

 

[BlakeVanderMeer]

This worked..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target <> "" Then
Cells(Target.Row, "A") = Cells(Target.Row, "A") & "-D"
Else
Range("A" & Target.Row) = ""
End If
End Sub


Thanks for helping me figure out where I was wrong

 

[JLGWhiz]

lYou're welcome. Just remember that with VBA variables need to be declared and given values before they can be used in an executable statement. Otherwise, you get an error message telling you that something is wrong with the statement.
Regards, JLG

 

[BlakeVanderMeer]

Is there a way with this script to ignore the first row? Also, If I delete the information in column B, it also Deletes the information in column A. Any ideas how It will just delete the (D) that is added instead of deleting the information already there?

 

[JLGWhiz]

Are you working one cell at a time or multiple cells? to remove the D from the value in cell A2

Range("A2") = Left(Range("A2").Value, Len(Range("A2").Value) - 1)

This uses the length of the cell value to create the number of characters to return with the Left function. Since you only want to remove the D at the end, subtract 1 from the lenght and it returns the original value.

 

[BlakeVanderMeer]

I have changed a handful of things in my excel sheet. so also modified VBA slighty. Current Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target <> "" Then
Cells(Target.Row, "A") = Cells(Target.Row, "A") & "(D)"
Else
Range("A" & Target.Row) = ""
End If
End Sub

I've also attached an image of the spreadsheet testing. Adding date to Column F, adds (D) to end of patient name. But, If I have to delete the Date from Column F, It also deletes the entire name from Column A. The date in column F will be manually added one at a time if the patient has a discharge date. The list will end up with hundreds and into the thousands of rows, but only applying the (D) based on the same row, if that makes sense?