Copy content to a selected row

[vabtroni]

Hi!

First of all, a happy 2023 to everyone!

I wonder if some of you could cast some light on a problem I have:

The workspace is D9:W98 in a PROTECTED worksheet.

On rows 10, 13, 16, 19, 22 (...) 94, and 97 -each 3 rows- I have a sequence of VLOOKUP formulas on D:W wich require horizontal continuity (meaning, the sequence on each row is
independent from other rows), that the user can override by absolute values. However, there is a chance that the user may need to reset to the original formulas on a given
row.

The original formulas are stored on D101:X101 and hidden from the user's view (black font and black background), and PROTECTED (user can't select these cells).

I need a macro that works this way:

1) Unprotect worksheet
2) Identify the row currently selected;
3) If the row selected is NOT one of those rows (10,13,16,19, etc, 94 and 97), the macro will stop with a message "Wrong row selected";
4) Copy the content of D101:X101 and paste it -FORMULAS ONLY- to the D cell of the selected row;
5) Protect worksheet

The PASTE must be -FORMULAS ONLY- , as the workspace is heavily implemented with conditional formatting (wich is essencial for the workspace to be fully functional), and also
because I do not want to paste the black background of the original formulas to the destination row.

Best regards everyone!
V.

 

[aaewalsh]

Try this:

Sub ResetFormulas()
    Dim selectedRow As Integer
    Dim allowedRows As Variant

    allowedRows = Array(10, 13, 16, 19, 22, 94, 97)
    selectedRow = ActiveCell.Row
    
    ' Check if selected row is not one of the allowed rows
    If Not IsInArray(selectedRow, allowedRows) Then
        MsgBox "Wrong row selected"
        Exit Sub
    End If

    ' Copy the formulas only from D101:X101
    Range("D101:X101").Copy
    
    ' Select the D column of the selected row and paste the formulas only
    Range("D" & selectedRow).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                           SkipBlanks:=False, Transpose:=False
End Sub

' Function to check if an element is in an array
Function IsInArray(val As Integer, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, val)) > -1)
End Function

 

[vabtroni]

Function and Sub need to be placed in any specific VBA module?

 

[aaewalsh]

yea

 

[vabtroni]

It dows the job quite well but with 1 flaw: it allows the macro to run when the row selected is below 10 (the first value of the array).

 

[aaewalsh]

Try This:

Sub ResetFormulas()
    Dim selectedRow As Integer

    selectedRow = ActiveCell.Row
    
    ' Check if selected row is not in the allowed rows
    If Not IsInRange(selectedRow, 10, 22) Then
        MsgBox "Wrong row selected"
        Exit Sub
    End If

    ' Copy the formulas only from D101:X101
    Range("D101:X101").Copy
    
    ' Select the D column of the selected row and paste the formulas only
    Range("D" & selectedRow).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                           SkipBlanks:=False, Transpose:=False
End Sub

' Function to check if an element is in the allowed range
Function IsInRange(val As Integer, lowerBound As Integer, upperBound As Integer) As Boolean
    If val >= lowerBound And val <= upperBound Then
        IsInRange = True
    Else
        IsInRange = False
    End If
End Function

 

[vabtroni]

Error displayed:

 

[vabtroni]

The macro must run ONLY on these specific rows:

10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 40, 43, 46, 49, 52, 55, 58, 61, 64, 67, 70, 73, 76, 79, 82, 85, 88, 91, 94, 97

 

[vabtroni]

Regarding the first solution, I found another flaw while testing: the selected range to be copied remains "highlighted", meaning that after the macro ends, if the user presses "enter", it will paste the previously selected content to whatever cell he has selected. It probably has to do with the paste special, because it happens the same when I manualy paste only values or formulas, the selected range remains highlighted ready for another paste.

 

[aaewalsh]

is it possible to send your file so i can test