Forum Discussion

huskereurocat's avatar
huskereurocat
Copper Contributor
Jun 21, 2023
Solved

Is an IF statment the best for this situation?

I've been trying to think of a way to write an IF statement, but am having a hard time understanding how to make this work. I have two columns of information so I hope that makes this easier. In the ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 22, 2023

    huskereurocat 

    I think I switched the terms.

    Sub Convert()
    Dim rng As Range
    Dim typ As Range
    Dim adr As String
    Application.ScreenUpdating = False
    Set rng = Range("IO:IO").Find(What:="Scrambling", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            Set typ = Range("KA" & rng.Row)
            If typ.Value = "QB_Scrambler" Then
                typ.Value = "QB_Improviser"
            End If
            Set rng = Range("IO:IO").Find(What:="Scrambling", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
    Application.ScreenUpdating = True
End Sub
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jun 22, 2023

huskereurocat 

Sorry, can't help you with VBA. Someone else must takeover.

 

huskereurocat's avatar
huskereurocat
Copper Contributor
Jun 22, 2023
Thanks for your help though!
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 22, 2023

    huskereurocat 

    Try this macro. Please test on a copy of the worksheet.

    Sub Convert()
    Dim rng As Range
    Dim typ As Range
    Dim adr As String
    Application.ScreenUpdating = False
    Set rng = Range("IO:IO").Find(What:="Scrambling", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            Set typ = Range("KA" & rng.Row)
            If typ.Value = "QB_Improviser" Then
                typ.Value = "QB_Scrambler"
            End If
            Set rng = Range("IO:IO").Find(What:="Scrambling", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
    Application.ScreenUpdating = True
End Sub
    • huskereurocat's avatar
      huskereurocat
      Copper Contributor
      Jun 22, 2023

      HansVogelaar 

       

      Thanks for your help!
      It would seem that this Macro inserts a lot of QB_Scrambler's into Player Type column. Can it be revised to just look for Scrambling - QB_Scrambler and change that to Scrambling - QB_Improviser? Just need to change the Player Type in this case to QB_Improviser.

      This what the unedited version looks like:

      This is what the current version of the Macro returns:

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 22, 2023

        huskereurocat 

        I think I switched the terms.

        Sub Convert()
    Dim rng As Range
    Dim typ As Range
    Dim adr As String
    Application.ScreenUpdating = False
    Set rng = Range("IO:IO").Find(What:="Scrambling", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            Set typ = Range("KA" & rng.Row)
            If typ.Value = "QB_Scrambler" Then
                typ.Value = "QB_Improviser"
            End If
            Set rng = Range("IO:IO").Find(What:="Scrambling", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
    Application.ScreenUpdating = True
End Sub