How to put this formula in every sheet

[Abhijeet]

I have this macro but i have 4 sheets & i want to put this formula in each sheet but Range is different in each sheet so please tell me how to do this.How different range of this formula put in particular sheet.Please tell me

Sub formula()
Dim myrange As Range, cell As Range
Dim lastrow As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lastrow = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Row
MsgBox lastrow
Set myrange = ActiveSheet.Range("o2:o" & lastrow)

For Each cell In myrange
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
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Done"

End Sub

 

[Abhijeet]

Sheets Names are Basic,Enh,OT,On call

 

[Luke M]

but Range is different in each sheet so please tell me how to do this.How different range of this formula put in particular sheet

You need to tell us how the ranges are different. Different columns, different rows, different lengths, different heights...do we just need to find the last row in col O?

Nevertheless, taking a wild guess

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
        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

 

[Abhijeet]

For concatenate Sheet Wise is Basic A2&"-"&D2
Enh Sheet A2&"-"&D2&"-"&E2&"-"&F2&"-"&G2
OT Sheet A2&"-"&D2&"-"&E2&"-"&F2&"-"&G2&"-"&H2&"-"&I2
On call Sheet A2&"-"&D2&"-"&E2&"-"&F2&"-"&G2&"-"&H2&"-"&I2&"-"&J2&"-"&K2
I want this in Column O
next column i want this Formula=IF(COUNTIF(C[-1],RC[-1])>1,""Duplicate"",""Not Duplicate"")

 

[Luke M]

That...doesn't answer my question at all.
I'm afraid I'm tired of trying to guess what problem you are wanting solve. I will not be watching this thread for further replies.

 

[NARAYANK991]

Hi Abhijeet ,

You need to incorporate this segment somewhere in the code Luke has already posted ; you need to figure out where. Make use of the variable Formula1 somewhere else in the code ; again figure out where.

                    Select Case .Name
                           Case "Basic"
                                Formula1 = "=RC[-14] & ""-"" & RC[-11]"
                           Case "Enh"
                                Formula1 = "=RC[-14] & ""-"" & RC[-11] & ""-"" & RC[-10] & ""-"" & RC[-9] & ""-"" & RC[-8]"
                           Case "OT"
                                Formula1 = "=RC[-14] & ""-"" & RC[-11] & ""-"" & RC[-10] & ""-"" & RC[-9] & ""-"" & RC[-8] & ""-"" & RC[-7] & ""-"" & RC[-6]"
                           Case "On call"
                                Formula1 = "=RC[-14] & ""-"" & RC[-11] & ""-"" & RC[-10] & ""-"" & RC[-9] & ""-"" & RC[-8] & ""-"" & RC[-7] & ""-"" & RC[-6] & ""-"" & RC[-5] & ""-"" & RC[-4]"
                           Case Else
                                Exit Sub
                    End Select

Narayan

 

[Abhijeet]

That...doesn't answer my question at all.
I'm afraid I'm tired of trying to guess what problem you are wanting solve. I will not be watching this thread for further replies.

u ask me different Column & different row then i told in Formula