Paste Value based upon different conditions

[lostballtallweeds]

My spreadsheet is set up this way, with 50+ rows following the same pattern.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID#[/TD]
[TD]TEAM[/TD]
[TD]TEAMLEADER[/TD]
[TD]TEAMMEMBER[/TD]
[TD]PHONE#[/TD]
[TD]COMMENT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BLUE[/TD]
[TD]HAROLD[/TD]
[TD]MARTHA[/TD]
[TD]555-5555[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RED[/TD]
[TD]BILL[/TD]
[TD]TONY[/TD]
[TD]555-5556[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GREEN[/TD]
[TD]HAROLD[/TD]
[TD]CYNTHIA[/TD]
[TD]555-5557[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I want to set up several Macros and corresponding buttons to insert a text string in the "Comment" column. For example, I want a button to insert
"No. Harold" in the "Comment" column in each row where Harold is the TeamLeader. I also want a button that will enter "Yes. Harold" in each row where Harold is the TeamLeader. I would like the same macros for Bill.


If possible, i'd also like macros that insert different text strings based upon the "Team" text.

The comment column won't be populated simultaneously or I think I could find a work-around.


Is there any good way to do this? I think if i could get one macro set up, I could adapt it to any criteria.

 

[Max1616]

Hello,

I would use one template and copy it for each of your different buttons that you want to set up. Here is the template I would use (where you would update the three variables listed):

Sub lostballtallweeds()
Dim lrow As Long
Dim VariableName As String
Dim VariableColumn As Integer
Dim Output As String
Dim i As Long

'Update the three variables below for how you would like the button to work
VariableName = "HAROLD"
VariableColumn = 3
Output = "No. HAROLD"


lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
    If Cells(i, VariableColumn) = VariableName Then Cells(i, 6) = Output
Next i

End Sub

Example 1: No. Harold

Sub NoHarold()
Dim lrow As Long
Dim VariableName As String
Dim VariableColumn As Integer
Dim Output As String
Dim i As Long

'Update the three variables below for how you would like the button to work
VariableName = "HAROLD"
VariableColumn = 3
Output = "No. HAROLD"


lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
    If Cells(i, VariableColumn) = VariableName Then Cells(i, 6) = Output
Next i

End Sub

Example 2 Yes. Bill:

Sub YesBill()
Dim lrow As Long
Dim VariableName As String
Dim VariableColumn As Integer
Dim Output As String
Dim i As Long

'Update the three variables below for how you would like the button to work
VariableName = "BILL"
VariableColumn = 3
Output = "Yes. BILL"


lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
    If Cells(i, VariableColumn) = VariableName Then Cells(i, 6) = Output
Next i

End Sub

Example 3 No. Blue

Sub lostballtallweeds()
Dim lrow As Long
Dim VariableName As String
Dim VariableColumn As Integer
Dim Output As String
Dim i As Long

'Update the three variables below for how you would like the button to work
VariableName = "BLUE"
VariableColumn = 2
Output = "No. BLUE"


lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
    If Cells(i, VariableColumn) = VariableName Then Cells(i, 6) = Output
Next i

End Sub

Notice how in example 3 I had to change the column number to represent the column that your team names lived in.

Let me know if you have any questions!

Sincerely,
Max

 

[lostballtallweeds]

This is exactly what I was looking for.
Thank you very much.