Issue with Basic If statement code to change data in a column

[MikeSta4ord]

Hello I'm trying to just make a simple macro which changes the data in column Q from "Yes" to "Reminder sent" I want to use this is unison with my email macro that sends reminders to employees to send a follow up email but I can't seem to get the simple code figured out correctlly.:banghead:


Thankyou and here's the code I'm attempting and getting a mismatch error:





Sub Updatedata()
Dim Follow_up_column As String


Follow_up_column = Sheet3.Range("Q" And Row_number)


If Follow_up_column = "Yes" Then Follow_up_column = "Reminder Sent"
End Sub

[MikeSta4ord]

Can anyone please identify the issue? No sites on the internet are getting me anywhere

[Paul_Hossler]

Many issues

1. Follow_up_column is a String, i.e. "ABCDEF"

2. Sheet3.Range is a Range Object

3. To assign an Object to a variable you use "Set oVar = SomeObject"

4. To concatenate string to use "&", i.e. ("Q" & Row_number), Excel will convert for you

5. While "If Follow_up_column = "Yes" Then Follow_up_column = "Reminder Sent" might be correct VBA since it's a String, it probably isn't going to do what you think you want it to do




Sub Updatedata()
Dim Follow_up_column As String


Follow_up_column = Sheet3.Range("Q" And Row_number)


If Follow_up_column = "Yes" Then Follow_up_column = "Reminder Sent"
End Sub



6. I'm taking a BIG guess here, but it looks like your Column Q has rows of data and you want to check each row?????

Some operations don't work on columns, but some do -- all depends



Option Explicit
Sub Updatedata()
Dim Follow_up_column As Range
Dim i As Long

Set Follow_up_column = Sheet3.Range("Q1:Q100") ' <<<<< Change as needed

For i = 2 To 100
If Follow_up_column.Cells(i).Value = "Yes" Then
Follow_up_column.Cells(i).Value = "Reminder Sent"
End If
Next i
End Sub









Sub Updatedata1()
Dim Follow_up_column As Range

Set Follow_up_column = Sheet3.Range("Q1:Q100") ' <<<<< Change as needed


Follow_up_column.Replace What:="Yes", Replacement:="Reminder Sent", LookAt:=xlWhole
End Sub

[MikeSta4ord]

Thank you very much paul, that does exactly as I'd like it to. Thanks for the explanation as well, I'm still learning code so I appreciate the tips of where I'm making mistakes.

I'm very grateful for your time.

[Paul_Hossler]

NP

Feel free to come back and ask any questions that you have