Replacing a cell value using VBA code

[Apol865]

Hi Guys,

I would really appreciate if someone can help me with my problem, I tried recording a macro but when I run it with the new data it doesn't work properly as it refers to a specific cell.
I am looking to replace 8 names in column "G" with multiple conditions/criteria. 1st 3 names will be changed based on dates in column "J" between Jan '24-Jul '24 and Jan '24-Jun '24 and column "I" if it's "Cultivate". And the other 5 names to be changed based on certain account numbers in column "D" and again column "I" if it's "Cultivate".

My data will keep on changing so the names in column "G" will also keep on changing. Thank you in advance!

 

[RobP]

Hi,
can you add some sample data for all to see a little clearer what you need ?
thanks
Rob

 

[Apol865]

Hi,
can you add some sample data for all to see a little clearer what you need ?
thanks
Rob

Here's the sample data. IN column G i need to constantly change the names of 8 owners

 

[Apol865]

Here's the sample data. IN column G i need to constantly change the names of 8 owners

View attachment 118139

BTW i tried using below code but i can't go any further i can only use the date as a condition

Sub replacevalueincolumnG()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim checkmonthstart As Date
Dim checkmonthend As Date
Dim targetvalue As String

Set ws = ThisWorkbook.Sheets("Sheet1") 'change
checkmonthstart = DateSerial(2024, 1, 1)
checkmonthend = DateSerial(2024, 6, 30)
targetvalue = "Sarah Lee"
lastrow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row

For i = 2 To lastrow
If ws.Cells(i, "I").Value = "Cultivate" Then
If IsDate(ws.Cells(i, "J").Value) Then
If ws.Cells(i, "J").Value >= checkmonthstart And ws.Cells(i, "J").Value <= checkmonthend Then
ws.Cells(i, "G").Value = "Sarah Lee_SMB"

End If
End If
End If
Next i

 

[RobP]

OK, thanks for the data. I'm afraid that reading your original request time and time again, and looking at your data brings *me* no further forward in terms of my understanding of what you expect VBA code to achieve for you. So lets start to try and decipher your problem a little ... as I don't have your luxury of understanding the current process and why its an issue for you ....

"I am looking to replace 8 names in column "G" with multiple conditions/criteria. 1st 3 names will be changed based on dates in column "J" between Jan '24-Jul '24 and Jan '24-Jun '24 and column "I" if it's "Cultivate"" : I guess we need to somehow know which 8 names you want to replace, and what you want to replace them with ... So I will assume at this stage you want to identify 8 names (why will there be only 8 names ? ..only you know - are these the first 8 names it finds ? last three it finds ? criteria dictates there are ONLY 8 to find, ALWAYS ?). Your VBA code is assuming you are looking for someone called "Sarah Lee" each time. Do you know the names of the persons you want to change ? (assuming not, you'll need some accurate criteria to fish out the names of the 8.)

Looks like some can be identified by "Cultivate" in Col I. In the quoted line above, you mention dates in Col J between Jan'24 and July'24 and Jan'24 and June'24. I'm not clear on what that means, as I don't think a date can be between both of those spreads accurately ?

How do the names get updated ? Are you going to free type the names or do they come from somewhere else in your data ? Is there a unique identifier on each row ?

Not sure what else to ask right now, but assume we have nothing when you try to explain a little more about what it is you need code to do .. that would be most helpful for the people to try and help you.

Maybe start with how "you" would be able to identify the first three names in your data that need changing, and how you would change them ? Pushing the boat out, we can move onto the other 5 later...

Kind Regards
Rob