Sheet Wise Put Formula

[Abhijeet]

Hi

I have this macro Sheet wise put formula but if add more sheet & in those sheet i do not want to put any formula then this macro not work please tell me how to Add Array Sheet in this code

Sub formula()
Dim myrange As Range, cell As Range
Dim lastrow As Long
Dim ws As Worksheet


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each ws In ThisWorkbook.Worksheets
    With ws
        lastrow = .Range("a" & .Rows.Count).End(xlUp).Row
        'MsgBox lastrow
      Set myrange = .Range("o2:o" & lastrow)
     
        For Each cell In myrange
        Select Case .Name
                          Case "Basic"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]"

                          Case "Enh"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case "OT"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case "On call"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case Else
                                Exit Sub
                    End Select
        'cell.FormulaR1C1 = _
                "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
        cell.Offset(, 1).FormulaR1C1 = _
                "=IF(COUNTIF(C[-1],RC[-1])>1,""Duplicate"",""Not Duplicate"")"
        Next cell
    End With
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Done"

End Sub

 

[Chihiro]

Adjust Worksheets() part as needed.

Sub formula()
Dim myrange As Range, cell As Range
Dim lastrow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set ws = Worksheets(1)
Do Until ws Is Worksheets(4)
    With ws
        lastrow = .Range("a" & .Rows.Count).End(xlUp).Row
        'MsgBox lastrow
      Set myrange = .Range("o2:o" & lastrow)
     
        For Each cell In myrange
        Select Case .Name
                          Case "Basic"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]"

                          Case "Enh"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case "OT"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case "On call"
                                cell.FormulaR1C1 = _
        "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

                          Case Else
                                Exit Sub
                    End Select
        'cell.FormulaR1C1 = _
                "=RC[-14]&""-""&RC[-11]&""-""&RC[-9]&""-""&RC[-8]&""-""&RC[-7]&""-""&RC[-6]&""-""&RC[-5]&""-""&RC[-4]&""-""&RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
        cell.Offset(, 1).FormulaR1C1 = _
                "=IF(COUNTIF(C[-1],RC[-1])>1,""Duplicate"",""Not Duplicate"")"
        Next cell
    End With
    Set ws = ws.Next
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Done"

End Sub

 

[Abhijeet]

Hi

Ok I will try this & check but is their array to do like this
shtnames = Array("Basic", "Enh", "OT", "On call")

 

[Chihiro]

No need for array. If it's fixed sheet names then you can just do IF operation to include only those sheets.

 

[Abhijeet]

Ok but can u pls tell me how to add Array in this type

 

[Abhijeet]

Actually i want to put sheet wise One Formula so if any other way can u pls tell me