Array issue #N/A

Question

I am facing an error while updating my data using array formula.

I am recording a macro to copy below table and pasting as a link in "sheet2" twice, one for actual and other for forecast values according to my macro. According to my macro, i have copied the table cells and headers separately to "sheet2" because their is no option to link one table to another, so i have to break apart and do so.

Name    V1  V3   V3 V4
Wood    10  10  10  10
wood    28  28  28  28
tree    30  45  60  68
plastic 50  50  50  50
tree    50  50  50  50
iron    64  75  75  80
table   20  25  0   30
plastic 54  35  21  0

When i am copying and pasting the table using a macro, it's taking the updated cells as its's selecting entire range according to recorded macro, which is good but the problem/issue is whenever i am copying it 2nd time for the actual values shown in below table where i am applying the formula for concatenation and if conditions using array formula, it's not taking the new range, it's taking the range which is recorded in the macro and throwing errors as #N/A.

Below is the actual values table, where i am only copying cells except headers, so i can create a pivot table out of entire range of actual and forecast tables and get only one headers, so to avoid the pivot table taking the headers of actual table as values, i have to do this way.

Wood - A        25   25   25   25
wood - A        50   50   50   50
tree - A        50   50   100  100
plastic - A     100  100  100  100
tree - A        100  100  100  100
iron - A        100  100  100  100
#N/A            #N/A #N/A #N/A #N/A
#N/A            #N/A #N/A #N/A #N/A

So, how can i make my below vba to edit and ask it to take new range every-time new entries added/deleted.

Recorded VBA: According to below vba, i have selected the range for concatenation formula is "A2:A7" and for if condition the range is "B2:E7" but i don't understand why vba is showing different values, which i can't understand in the range of R & C.

Sub test1a()
    Sheets("Sheet1").Select
    Range("Table4").Select
    Range("C4").Activate
    Selection.copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste link:=True
    Sheets("Sheet1").Select
    Range("Table4[[#Headers],[Name]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste link:=True
    Selection.copy
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.copy
    Range("A20").Select
    ActiveSheet.Paste link:=True
    Range("A20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.FormulaArray = "=CONCATENATE(R[-18]C:R[-13]C,"" - A "")"
    Range("B20").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaArray = _
        "=VALUE(IF(R[-18]C:R[-13]C[3]<25,""25"",IF(R[-18]C:R[-13]C[3]<50,""50"",""100"")))"

End Sub

Thanks

Answer 1

You can do the following, without adding formulas and just run to update when more rows added

Note: You can change destRange = ws.Range("A" & lastRow + 2) to somewhere else if you want the projected data put in a different place.

Option Explicit

Sub GetRows()

Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim sourceData As Range

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet2") 'change
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Set sourceData = ws.Range("A1:E" & lastRow)

Dim outputArray()

outputArray = sourceData.Value2

Dim i As Long

For i = 2 To UBound(outputArray, 1)

    outputArray(i, 1) = outputArray(i, 1) & " - A"

    Dim y As Long

        For y = 2 To UBound(outputArray, 2)

          Select Case outputArray(i, y)

              Case Is < 25
                  outputArray(i, y) = 25
              Case Is < 50
                 outputArray(i, y) = 50
              Case Else
                  outputArray(i, y) = 100
          End Select

        Next y

Next i

Dim destRange As Range

Set destRange = ws.Range("A" & lastRow + 2)

destRange.Resize(UBound(outputArray, 1), UBound(outputArray, 2)) = outputArray

End Sub