Forum Discussion

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

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

huskereurocat

Copper Contributor

Jun 22, 2023

Detlef_Lewin

Thanks for the research and answer. There seems to be only one problem with this formula. If you look at the snapshot below, you can see that what gets returned is either a Balanced - QB_Scrambler or a Scrambling - QB_Improviser. Nowhere in the formula is there a possibility of a Balanced - QB_Improviser, which there needs to be.

Somehow we need to have the formula check if there is a Scrambling - QB_Scrambler (highlighted in yellow below) in which case that would need to be a Scrambling - QB_Improviser.

So we're left with the three possibilties highlighted in green below.

Is that even possible with an IF statement? Somehow, I would think, the formula would need to see there is a Scrambling - QB_Scrambler and change it to Scrambling - QB_Improviser. Can that be done?

Thanks,

Kelly

Detlef_Lewin

Silver Contributor

Jun 22, 2023

huskereurocat

Somehow, I would think, the formula would need to see there is a Scrambling - QB_Scrambler and change it to Scrambling - QB_Improviser. Can that be done?

Now we are getting there.

This is not possible with a formula but with VBA.

However you can use conditional formatting to highlight the cells.

Column A is TRAIT_OBSTYLE and column B is PlayerType.

=COUNTIFS($A2,"Scrambling",$B2,"QB_Scrambler")

huskereurocat
Copper Contributor
Jun 22, 2023
Thanks for helping! Please forgive me, but I'm clueless about VBA coding. I can record a macro, but when it comes to editing that Macro in Visual Basic, I don't know what I'm looking at. I'm not sure where to go from here. My goal here is to automate the change of the information in the cells in the Player Type column. Help, because I'm lost now!
- Detlef_Lewin
  Silver Contributor
  Jun 22, 2023
  huskereurocat
  Sorry, can't help you with VBA. Someone else must takeover.
  - huskereurocat
    Copper Contributor
    Jun 22, 2023
    Thanks for your help though!