vba replace the first 3 characters - (NBA indicators)

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Looking to replace the first 3 characters (A3:A11).
A3 should always be "IF"
A4:A11 should always be ",IF", assuming there is text in the cell. the blank cells have possibilities to have text.
thank you.

NBA.xlsm
A
3,IF(AND($A4=$AH4,$B4<0,ISNA(MATCH($A4,$G$52:$G$66,0)),$J4<$J5,$N4>=.55,$M4>0,$O4>0,$Q4>0,$S4>0,$AI4=TRUE,$AN4<=$AN$1),"Navy"
4,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$Q4>$Q5,$S4>$S5),"Q & S"
5IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$X4>$X5),"Purple - B,J,N:P,R,X,AI,AN"
6,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$P4>$P5,$R4>$R5),"Green - N:P,R"
7IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5),"Aqua - B,J,K,N:P,R,AI,AN"
8,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$T4>$T5,$X4>$X5,$Z4>$Z5),"Orange-B,J,N:P,R,T,X,Z,AI,AN"
9,IF(AND($A4=$AH4,$N4>=.55,$O4>0,$O4>=$O5,$P4>$P5,$R4>$R5,$T4>$T5,$U4>$U5,$V4>$V5,$W4>$W5,$X4>$X5,$Y4>$Y5,$Z4>$Z5,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5),"N:O,P,R,T:V,W:X,Y:AA,AB:AC",""))))))
10
11
Indicators
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
gtd526 said:
Hello,
Looking to replace the first 3 characters (A3:A11).
A3 should always be "IF"
A4:A11 should always be ",IF", assuming there is text in the cell. the blank cells have possibilities to have text.
thank you.

NBA.xlsm
A
3,IF(AND($A4=$AH4,$B4<0,ISNA(MATCH($A4,$G$52:$G$66,0)),$J4<$J5,$N4>=.55,$M4>0,$O4>0,$Q4>0,$S4>0,$AI4=TRUE,$AN4<=$AN$1),"Navy"
4,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$Q4>$Q5,$S4>$S5),"Q & S"
5IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$X4>$X5),"Purple - B,J,N:P,R,X,AI,AN"
6,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$P4>$P5,$R4>$R5),"Green - N:P,R"
7IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5),"Aqua - B,J,K,N:P,R,AI,AN"
8,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$T4>$T5,$X4>$X5,$Z4>$Z5),"Orange-B,J,N:P,R,T,X,Z,AI,AN"
9,IF(AND($A4=$AH4,$N4>=.55,$O4>0,$O4>=$O5,$P4>$P5,$R4>$R5,$T4>$T5,$U4>$U5,$V4>$V5,$W4>$W5,$X4>$X5,$Y4>$Y5,$Z4>$Z5,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5),"N:O,P,R,T:V,W:X,Y:AA,AB:AC",""))))))
10
11
Indicators
Click to expand...
I found the VBA for the replacement. First I do a Sort, then the Replacement (the Sort can change the order, resulting in "," will be where it should be or not be).
I do the Replacement twice, because if it finds "IF" it will add the ",". Even if there's already a ","
Here is the Sheet and the vba Code.

NBA.xlsm
ABCDE
1Row 4Row 5
2As FAV As FAVRankCriteria2
3IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$M4>0,$O4>0,$Q4>0,$S4>0),"Navy"IF(AND($A5=$AH5,$B5<0,$AI5=TRUE,$AN5<=$AN$1,$N5>=.55,$O5>0,$O5>=$O4,$J5<$J4,ISNA(MATCH($A5,$G$52:$G$66,0)),$M5>0,$O5>0,$Q5>0,$S5>0),"Navy"1Navy80.0%
4,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$Q4>$Q5,$S4>$S5),"MOV2 & MOV > ATS (Q & S)",IF(AND($A5=$AH5,$B5<0,$AI5=TRUE,$AN5<=$AN$1,$N5>=.55,$O5>0,$O5>=$O4,$J5<$J4,ISNA(MATCH($A5,$G$52:$G$66,0)),$Q5>$Q4,$S5>$S4),"Q & S"2MOV2 & MOV > ATS (Q & S)61.1%
5,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$X4>$X5),"Purple - B,J,N:P,R,X,AI,AN",IF(AND($A5=$AH5,$B5<0,$AI5=TRUE,$AN5<=$AN$1,$N5>=.55,$O4>0,$O5>=$O4,$J5<$J4,ISNA(MATCH($A5,$G$52:$G$66,0)),$P5>$P4,$R5>$R4,$X5>$X4),"Purple - B,J,N:P,R,X,AI,AN"3Purple - B,J,N:P,R,X,AI,AN54.6%
6,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$P4>$P5,$R4>$R5),"Green - N:P,R",IF(AND($A5=$AH5,$B5<0,$AI5=TRUE,$AN5<=$AN$1,$N5>=.55,$O5>0,$O5>=$O4,$J5<$J4,ISNA(MATCH($A5,$G$52:$G$66,0)),$P5>$P4,$R5>$R4),"Green - N:P,R"4Green - N:P,R52.9%
7,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5),"Aqua - B,J,K,N:P,R,AI,AN",IF(AND($A5=$AH5,$B5<0,$AI5=TRUE,$AN5<=$AN$1,$N5>=.55,$O5>0,$O5>=$O4,$J5<$J4,ISNA(MATCH($A5,$G$52:$G$66,0))),$K5>$K4,$P5>$P4,$R5>$R4),"Aqua - B,J,K,N:P,R,AI,AN"5Aqua - B,J,K,N:P,R,AI,AN52.1%
8,IF(AND($A4=$AH4,$B4<0,$AI4=TRUE,$AN4<=$AN$1,$N4>=.55,$O4>0,$O4>=$O5,$J4<$J5,ISNA(MATCH($A4,$G$52:$G$66,0)),$K4>$K5,$P4>$P5,$R4>$R5,$T4>$T5,$X4>$X5,$Z4>$Z5),"Orange-B,J,N:P,R,T,X,Z,AI,AN",IF(AND($A5=$AH5,$B5<0,ISNA(MATCH($A5,$G$52:$G$66,0)),$J5<$J4,$K5>$K4,$N5>=.55,$O5>0,$O5>=$O4,$P5>$P4,$R5>$R4,$T5>$T4,$X5>$X4,$Z5>$Z4,$AI5=TRUE,$AN5<=$AN$1),"Orange - B,J,N:P,R,T,X,Z,AI,AN"6Orange - B,J,N:P,R,T,X,Z,AI,AN46.3%
9,IF(AND($A4=$AH4,$N4>=.55,$O4>0,$O4>=$O5,$P4>$P5,$R4>$R5,$T4>$T5,$U4>$U5,$V4>$V5,$W4>$W5,$X4>$X5,$Y4>$Y5,$Z4>$Z5,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5),"N:O,P,R,T:V,W:X,Y:AA,AB:AC",""))))))),IF(AND($A5=$AH5,$N5>=.55,$O5>0,$O5>=$O4,$P5>$P4,$R5>$R4,$T5>$T4,$U5>$U4,$V5>$V4,$W5>$W4,$X5>$X4,$Y5>$Y4,$Z5>$Z4,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4),"N:O,P,R,T:V,W:X,Y:AA,AB:AC","")))))))
10
11
Indicators
Cell Formulas
RangeFormula
C3:C8C3=RANK(E3,Rank)
E3E3=Favs!L43
E4E4=Favs!L55
E5E5=Favs!U$43
E6E6=Favs!O$43
E7E7=Favs!R$43
E8E8=Favs!X$43


VBA Code: 
Sub Indicators_Sort_and_Change()
'Indicators - sort by % and change IF statements
    
    Dim Rng As Range
    
    Workbooks("NBA.xlsm").Worksheets("Indicators").Select
    
    Range("A3:E8").Select
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Indicators").Sort.SortFields.Add2 Key:=Range("C3:C8") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Indicators").Sort
        .SetRange Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'change IF statements

    Set Rng = Sheets("Indicators").Range("A3:B3")  'ActiveSheet.UsedRange

        Rng.Select
        fnd = ",IF"
        rplc = "IF"
        Set sht = Sheets("Indicators").Range("A3:B3")
        sht.Cells.Replace What:=fnd, Replacement:=rplc, _
          LookAt:=xlPart, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    
    Set Rng = Sheets("Indicators").Range("A4:B11")  'ActiveSheet.UsedRange

        Rng.Select
        fnd = ",IF"
        rplc = "IF"
        Set sht = Sheets("Indicators").Range("A4:B11")
        sht.Cells.Replace What:=fnd, Replacement:=rplc, _
          LookAt:=xlPart, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
        
        fnd = "IF"
        rplc = ",IF"
        Set sht = Sheets("Indicators").Range("A4:B11")
        sht.Cells.Replace What:=fnd, Replacement:=rplc, _
          LookAt:=xlPart, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False

    Range("A1").Select

    
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

gtd526
  • Question
convert cell value to formula
Replies
8
Views
177
Fluff
Fluff
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
129
gtd526
gtd526
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
161
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
132
gtd526
gtd526
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
139
gtd526
gtd526

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top