insert row code tweak required

[MOB]

Sub InsertRow()
    On Error GoTo InsertRow_Error
    With ActiveSheet
        .Unprotect Password:="password"
        Dim rown As Range
        Dim rname As String
        Set rown = Application.InputBox _
        (Prompt:="Select a cell in column A where you want the new row to be added", Title:="Add a new row", Type:=8)
        rown.Select
        Selection.EntireRow.Insert
        ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
        ActiveCell.Offset(0, 41).FormulaR1C1 = "=COUNTA(RC[-31]:RC[-1])"
        ActiveCell.Offset(0, 42).FormulaR1C1 = "=RC[-38]/7*RC[-1]"
        ActiveCell.Offset(0, 43).FormulaR1C1 = "=RC[+2]/7*RC[-2]"

InsertRow_Error:
        .Protect Password:="password", AllowFormattingCells:=True
    End With
End Sub

I've been using this code for quite a while now, but users being users, they sometimes click on column B, C etc rather than A.

The code replaces the usual insert row function, adding the necessary formulae.

Can the above be tweaked so that it always starts from Column A even if they click elsewhere?

TIA

 

[Norie]

Perhaps something like this.

Sub InsertRow()
Dim rown As Range
Dim rname As String

    On Error GoTo InsertRow_Error
    With ActiveSheet
        .Unprotect Password:="password"
        Set rown = Application.InputBox _
        (Prompt:="Select a cell in column A where you want the new row to be added", Title:="Add a new row", Type:=8)
        
        Set rown = Cells(rown.Row, "A")
        
        With rown.EntireRow.Insert
            .Value = ActiveCell.Offset(-1, 0).Value
            .Offset(0, 41).FormulaR1C1 = "=COUNTA(RC[-31]:RC[-1])"
            .Offset(0, 42).FormulaR1C1 = "=RC[-38]/7*RC[-1]"
            .Offset(0, 43).FormulaR1C1 = "=RC[+2]/7*RC[-2]"
        End With
InsertRow_Error:
        .Protect Password:="password", AllowFormattingCells:=True
    End With
    
End Sub

 

[MOB]

Hi

That code inserted the row but inserted no formulae from this;

.Value = ActiveCell.Offset(-1, 0).Value
.Offset(0, 41).FormulaR1C1 = "=COUNTA(RC[-31]:RC[-1])"
.Offset(0, 42).FormulaR1C1 = "=RC[-38]/7*RC[-1]"
.Offset(0, 43).FormulaR1C1 = "=RC[+2]/7*RC[-2]"

 

[Norie]

Oops, my bad.

Try this.

Sub InsertRow()
Dim rown As Range
Dim rname As String

    On Error GoTo InsertRow_Error
    With ActiveSheet
        .Unprotect Password:="password"
        Set rown = Application.InputBox _
        (Prompt:="Select a cell in column A where you want the new row to be added", Title:="Add a new row", Type:=8)
        
        Set rown = Cells(rown.Row, "A")
        
        With rown
            .EntireRow.Insert
            .Value = ActiveCell.Offset(-1, 0).Value
            .Offset(0, 41).FormulaR1C1 = "=COUNTA(RC[-31]:RC[-1])"
            .Offset(0, 42).FormulaR1C1 = "=RC[-38]/7*RC[-1]"
            .Offset(0, 43).FormulaR1C1 = "=RC[+2]/7*RC[-2]"
        End With
InsertRow_Error:
        .Protect Password:="password", AllowFormattingCells:=True
    End With
    
End Sub

 

[MOB]

Many thanks, I've got it working now