combine Right function with IF function by vba

Omaran

I had a problem running your sheet but to ignore any entries starting with "FIRST BALANCE" (13 characters), you need to the Left function (not Right). Therefore you should replace your line:

       .Range("F2:F" & R) = .Evaluate("IF(B2:B" & R & "=""FIRST BALANCE"","""",1)") 

with (changes in bold):

    R = .[A1].CurrentRegion.Rows.Count - 1
    If F > 1 And R > 0 Then
       .Range("F2:F" & R) = Application.Evaluate("IF(Left(B2:B" & R & ", 13)=" _
            & Chr(34) & "FIRST BALANCE" & Chr(34) & ", " & Chr(34) & Chr(34) & ",1)")

Note that is really a single line of code but split with " _" (return) to make it easier to read. Chr(34) returns the double quotation mark ("), which is ASCII (decimal) character 34.

Where R is 14 say, that equates to the string for evaluation:

IF(Left(B2:B14, 13)="FIRST BALANCE", "",1)

BUT that will evaluate the formula based on the active sheet (not the sheet picked in the loop).

Revision 27 August 2023

I'd suggest that you delete your lines:

    For F = 1 To Index - 1
   With Worksheets(F)
        S(F) = .Name
     If F > 1 Then
        R = .[A1].CurrentRegion.Rows.Count - 1
       .Range("F2:F" & R) = .Evaluate("IF(B2:B" & R & "=""FIRST BALANCE"","""",1)")
     Else
       .UsedRange.Columns(6).Clear
     End If
   End With
    Next

and use a simple loop (and use an alternative method to determine the last row in A) instead:

    For F = 1 To Index - 1
        With Worksheets(F)
        S(F) = .Name
        'get last used row in A
        R = .Range("A" & Rows.Count).End(xlUp).Row
        '
        If F > 1 And R > 1 Then
            'R = .[A1].CurrentRegion.Rows.Count - 1
            For n = 2 To R
                If Left(.Range("B" & n), 13) = "FIRST BALANCE" Then
                    .Range("F" & n) = ""
                    Else
                    .Range("F" & n) = 1
                End If
            Next n

            Else
            .UsedRange.Columns(6).Clear
        End If
        End With
    Next

I got a compliation error when I tried to run the corrected code but didn't try to fix your code. I think the sheet numbers are a bit confused (in my non-Arabic version of Excel) but suspect at the least you have to first define what Index is, something like:

Sub Demo1()

    Dim S$(), F&, R&, W, Rf As Range, Index As Long



    Index = Sheets.Count + 1





  ReDim S(1 To Index - 1)

and also probably (following that) define which sheet is to be cleared (unless it's always run from a given sheet):

Worksheets("REPORT FOR MISSED").UsedRange.Offset(1).Clear

or whatever is the right sheet name.

Hope this helps. If so, please remember to mark this Answer as Selected.