Excel macro - using offset (?)?

Question

I have a formula that calculates an answer for a number of input variables. I want to copy the result of the calculation to a cell lower down in my spreadsheet, and then, when I redo the calculation with different variables, copy and paste the new answer to the next cell underneath the first one, and so forth. I do not wish to use a listbox to display the collection of answers. The following code almost does what I want, but pastes to a different worksheet and I can't figure out how to specify the cell I want it to begin pasting to:

Set CopyRange = Sheet1.Range("A1")

Set NextCell = Sheet2.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)

CopyRange.Copy NextCell

I would appreciate any help. Thanks

Answer 1

Here's what I came up with and tried out.  You can substitute B5 with whatever cell you want it to begin pasting to.

Sub MyMacro2()

Dim x As Range

Dim y As Long

Dim MyAnswer As Double

Set x = Range("B5")

If Application.WorksheetFunction. _

CountBlank(x) = 1 Then

    x.Value = MyAnswer

Else

    y = x.CurrentRegion.Row

    y = y + x.CurrentRegion.Rows.Count

    Cells(y, x.Column).Value = MyAnswer

End If

End Sub

I thought about using a static variable, but if you ever deleted out a cell or row that would give you problems.  This macro will go to the cell you specify and see if it is blank.  If it is blank, it will enter it in there.  If it isn't it will go to the first empty cell below that cell in the same column and enter the number in there.

Answer 2

Your code appears to function appropriately for me.  I opened a blank workbook, created a macro with your code, placed text in Sheet1A1 and pulled the trigger.

The text in Sheet1A1 was copied to Sheet2A2, and subsequently Sheet2A3, Sheet2A4, etc.

Seems good to go.  Are you getting any compile errors or anything out of the ordinary when your run it?