VBA conditional replacement of text

[luv2bounce]

I have a large database that is updated monthly. I am trying to write a macro that looks to see if - say column F - is yes it changes column B to say "not applicable" - or some other wording. But the issue is that the location of column F varies from month to month. I was trying to use index and match to find the right column but am not sure how to incorporate all of this into a single macro. Can anyone help?

Many many thanks in advance.

[HansV]

Let's say you want to look at a column whose heading in row 1 is Status

Code: Select all

Sub ChangeB()
    Dim col As Range
    Dim rng As Range
    Dim adr As String
    ' Search row 1 for heading
    Set col = Range("1:1").Find(What:="Status", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If col Is Nothing Then Exit Sub
    ' Refer to the column
    Set col = col.EntireColumn
    ' Search for "yes"
    Set rng = col.Find(What:="yes", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If rng Is Nothing Then Exit Sub
    ' Don't update the screen for efficiency
    Application.ScreenUpdating = False
    ' Remember address of the first cell where "yes" was found
    adr = rng.Address
    ' Start loop
    Do
        ' Change cell in column B
        Range("B" & rng.Row).Value = "Not Applicable"
        ' Search for next instance of "yes"
        Set rng = col.Find(What:="yes", LookAt:=xlWhole, MatchCase:=False)
        ' If not found, exit the loop
        If rng Is Nothing Then Exit Do
    ' Loop until we're back at the first cell
    Loop Until rng.Address = adr
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

[luv2bounce]

Thank you so much! I think I am understanding a bit more as I struggle to adapt for my case! Greatly appreciated. I seem to learn and understand better with reverse engineering!

I am so grateful! I still don't have it working 100% but will persevere a bit more!

I hope you have a wonderful Christmas!!!!

Cheers

Susan

[luv2bounce]

Oh no! I'm stuck again - the column that it is replacing the information in - also moves (apparently) so it isn't always column B. I was trying to do the find for that column again, but not sure whether I put that up front of the loops?

I guess in summary I need to find the column that has a yes and then find a colum called stage to change that - if that makes sense? Can you help?

Thanks in advance again!

Susan

[HansV]

I'm slightly confused. You mentioned Status earlier, now Stage.
And does the column with Yes have a fixed header? If so, what is it?

[luv2bounce]

Hi Thanks. Sorry for the confusion. The actual heading is Stage. Also, for some reason it only changes the first instance, I'm not sure how the loop is indexing to the instance?

[HansV]

What about the column to enter "Not applicable" in. Does it have a fixed heading? If so, what is that heading?

[luv2bounce]

That column is actually called "Project Started at Risk"

Basically what I need to do is if the project started at risk box says yes - I change stage from whatever is there to 6x. Project started. Just makes it hard because the locations of all the headings changes depending on what people have chosen when they download the report from our CRM system.


Hopefully that makes sense?

Many thanks for your help and for making me look like a hero!

[HansV]

Try this version:

Code: Select all

Sub ChangeStartedAtRisk()
    Dim col As Range
    Dim col2 As Range
    Dim c As Long
    Dim rng As Range
    Dim adr As String
    ' Search row 1 for Status heading
    Set col = Range("1:1").Find(What:="Status", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If col Is Nothing Then Exit Sub
    ' Refer to the column
    Set col = col.EntireColumn
    ' Search row 1 for Stage heading
    Set col2 = Range("1:1").Find(What:="Project Started at Risk", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If col2 Is Nothing Then Exit Sub
    ' Column number
    c = col2.Column
    ' Search for "yes"
    Set rng = col.Find(What:="yes", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If rng Is Nothing Then Exit Sub
    ' Don't update the screen for efficiency
    Application.ScreenUpdating = False
    ' Remember address of the first cell where "yes" was found
    adr = rng.Address
    ' Start loop
    Do
        ' Change cell
        Cells(rng.Row, c).Value = "Not Applicable"
        ' Search for next instance of "yes"
        Set rng = col.Find(What:="yes", LookAt:=xlWhole, MatchCase:=False)
        ' If not found, exit the loop
        If rng Is Nothing Then Exit Do
    ' Loop until we're back at the first cell
    Loop Until rng.Address = adr
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

[luv2bounce]

you're amazing and talented! Thank you. I am in a meeting but will try later this afternoon.

Many, many thanks!

[snb]

Code: Select all

Sub M_snb()
  sn = activesheet.usedrange
  
  for jj=1 to ubound(sn,2)
    if sn(1,jj)="Status" then x = jj
    if sn(1,jj)="Project Started at Risk" then y = jj
  next

  for j=2 to ubound(sn)
    if lcase(sn(j,y))="yes" then sn(j,x) = "Not Applicable"
  next

  activesheet.usedrange = sn
End Sub

[luv2bounce]

the above looks like a neat little solution, but I am afraid I am such a novice with VBA that I am not clear how it works (so that I can adapt for my actual situation). is jj meant to be the row, column address for the heading of the columns? I get lost with both x and y being equal to jj because they aren't the same columns?

Apologies in advance for what is probably a very silly question?

Also, thank you so much for responding!

[HansV]

snb likes to write code that is as compact as possible, without comments and without declaring the variables used.

sn is an array that contains the values of the used range of the sheet (assumed to start in row 1).
The first loop traverses the columns of the first row, and sets x to the column containing "Status" and y to the column containing "Project Started at Risk".
The second loop traverses the rows. If the value in column x (the Status column) is "Yes", it sets the value of column Y (the Project Started at Risk column) to "Not applicable".
Finally, the array is written back to the used range.

[luv2bounce]

Thank you to all of you trying to help me with this. I have the feeling that I have not accurately been able to convey what I am trying to do, so thought that I would try to simplify the spreadsheet I have to illustrate. I have removed most of the columns that I am usually presented with, and I've left the stage and Project Started at Risk? columns in - thpugh they are not at the usual locations. Also note that they often move from month to month, much to my exasperations and requests not to.... shows you how much clout I have!!!!

To summarize my needs. If the project started at risk? column has a Y then the stage column needs to be replaced with "6x. Project Started"

I am hoping this simplifies my request into something that I'm able to implement?

I am apologising in advance for my lack of skills, but truly, truly appreciate your help!

Many thanks in advance!

Susan

[HansV]

The header of your status column is Stage, not Status, and the Project Started at Risk? column contains Y, not Yes.
Here is a working version of my macro:

Code: Select all

Sub ChangeStage()
    Dim col As Range
    Dim col2 As Range
    Dim c As Long
    Dim rng As Range
    Dim adr As String
    ' Search row 1 for Stage heading
    Set col = Range("1:1").Find(What:="Stage", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If col Is Nothing Then Exit Sub
    ' Column number
    c = col.Column
    ' Search row 1 for Stage heading
    Set col2 = Range("1:1").Find(What:="Project Started at Risk?", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If col2 Is Nothing Then Exit Sub
    ' Refer to the column
    Set col2 = col2.EntireColumn
    ' Search for "Y"
    Set rng = col2.Find(What:="Y", LookAt:=xlWhole, MatchCase:=False)
    ' If not found, get out
    If rng Is Nothing Then Exit Sub
    ' Don't update the screen for efficiency
    Application.ScreenUpdating = False
    ' Remember address of the first cell where "yes" was found
    adr = rng.Address
    ' Start loop
    Do
        ' Change cell
        Cells(rng.Row, c).Value = "6x. Project Started"
        ' Search for next instance of "Y"
        Set rng = col2.Find(What:="Y", After:=rng, LookAt:=xlWhole, MatchCase:=False)
        ' If not found, exit the loop
        If rng Is Nothing Then Exit Do
    ' Loop until we're back at the first cell
    Loop Until rng.Address = adr
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

[HansV]

And here is a working version of snb's code:

Code: Select all

Sub M_snb()
  sn = ActiveSheet.UsedRange
  
  For jj = 1 To UBound(sn, 2)
    If sn(1, jj) = "Stage" Then x = jj
    If sn(1, jj) = "Project Started At Risk?" Then y = jj
  Next

  For j = 2 To UBound(sn)
    If sn(j, y) = "Y" Then sn(j, x) = "6x. Project Started"""
  Next

  ActiveSheet.UsedRange = sn
End Sub

[HansV]

Here is the workbook with both macros. I deleted row 1 so that your headers are now in row 1.

replace stage example.xlsm

[luv2bounce]

Thank you for taking the time and effort to explain and consolidate the macros. When I run your macro, it only changes the first instance of there being a Y in Project started at risk. Do I then need to put some sort of loop in to go through the rest of the rows?

[HansV]

My apologies, I attached a previous version of the macro.
Here is the current one.

replace stage example.xlsm

[snb]

Or the use of Excel's builtin options: autofilter & dynamic table ( listobject)

Code: Select all

Sub M_snb()
  With Sheet7.ListObjects(1).DataBodyRange
    .AutoFilter Sheet7.ListObjects(1).HeaderRowRange.Find("Project Started At Risk?").Column, "Y"
    .Columns(Sheet7.ListObjects(1).HeaderRowRange.Find("Stage").Column) = "6x. Project Started"
    .AutoFilter
  End With
End Sub