VBA For Loop and Referencing

Question

Basically I created an xlxs with three sheets, one with data, one with formulas and one with the output.

Data are represented in a table with 28 features for each registration number, I am trying to perform a For ... Next loop to copy one row of data from the sheet "Data" to the sheet "Calculations", then copy the output from the "Output" sheet to an other Excel file and then to skip to the next registration number.

The code I wrote should work this way

Sub CopyPaste()
    registration_numbers = Sheets("Data").Range("b1").Value
    Workbooks.Add
    wkb = Morkbooks.Application.ActiveWorkbook.Name
    Sheets.Application.ActiveSheet.Name="Results"
    Sheets.Application.ActiveSheet.Range("a1:i1").Value = ThisWorkbook.Sheets("Output_prg").Range("a1:i1").Value
    For i = 1 To registration_numbers
            x = 1 + i
            If ThisWorkbook.Sheets("Data").Cells(x, 1).Value <> "" Then
                [ThisWorkbook.Sheets("Calculations").Range(Cells(3, 1), Cells(3, 29)).Value = ThisWorkbook.Sheets("Data").Range(Cells(2 + i, 1), Cells(2 + i, 29)).Value]
                Workbooks("wkb").Sheets("Results").Range(Cells(1 + i, 1), Cells(1 + i, 9)).Value = ThisWorkbook.Sheets("Output").Range("a2:i2").Value
            End If
        Next i
End Sub

However when I try to run this simple code I receive "1004" run-time error, and the line of code under square brackets gets highlighted in the debug.

What am I doing wrong? How can I solve this issue?

All your `cells` need to be qualified with a workbook and worksheet. `ThisWorkbook.Sheets("Calculations").cells(...)` same for the next line. — Warcupine, Jul 27 '22 at 14:40
Thank you for your attention and for your time, unfortunately I haven't understood the meaning of your comment. I am trying to copy and paste a range of cells and referring to that through the loop. You are suggesting that I should use this 'ThisWorkbook.Sheets("Calculations").Cells(3, 1).Cells(3, 29)' — Marco Scaglione, Jul 27 '22 at 14:49
you have `ThisWorkbook.Sheets("Calculations").Range(Cells...` you need to add the workbook and worksheet to `cells` you're telling it you have a range on one sheet consisting of cells on another and it has no idea what to do with that. So: `ThisWorkbook.Sheets("Calculations").Range(ThisWorkbook.Sheets("Calculations").Cells(3,1)...` — Warcupine, Jul 27 '22 at 14:51
Ok thank you for your advise, is this what were you referring to `Workbooks("wkb").Sheets("Results").Range(Workbooks("wkb").Sheets("Results").Cells(1 + i, 1), Workbooks("Results").Sheets("Output").Cells(1 + i, 9)).Value = ThisWorkbook.Sheets("Output").Range("A2:I2").Value` Or am I missing something? — Marco Scaglione, Jul 27 '22 at 15:05

curious · Answer 1 · 2022-08-28T15:08:31.687

TL;DR: Simply set variable for the needed worksheets.

Quote from Assignment Issues:

"Your error occurred because you didn't qualify Cells (e.g. src.Cells, dest.Cells...), so the code tried to 'get' a range (object, not address) on the destination sheet 'using' cells(object, not address) from the source sheet which obviously is not possible."

To resolve this issue, the code may look like this:

Sub CopyPaste()
    registration_numbers = Sheets("Data").Range("b1").Value
    Set wsCalc = Sheets("Calculations")
    Set wsData = Sheets("Data")
    Set wsOutput = Sheets("Output")
    Workbooks.Add
    ActiveSheet.Name = "Results"
    Range("a1:i1").Value = ThisWorkbook.Sheets("Output_prg").Range("a1:i1").Value
    For i = 1 To registration_numbers
        x = 1 + i
        If ThisWorkbook.Sheets("Data").Cells(x, 1).Value <> "" Then
            wsCalc.Range(wsCalc.Cells(i, 1), wsCalc.Cells(i, 29)).Value = wsData.Range(wsData.Cells(2 + i, 1), wsData.Cells(2 + i, 29)).Value 'not sure why row 3 was used. replaced it with "i"
            Range(Cells(1 + i, 1), Cells(1 + i, 9)).Value = wsOutput.Range("a2:i2").Value
        End If
    Next i
End Sub

Hope this helps.

VBA For Loop and Referencing

1 Answers1