I want to create a subroutine that accepts a range as an argument. Something like this:
Sub Test(dataRange As Range)
Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)
End Sub
However when I try to select the range on Excel and run it, I get a "Reference is not valid" error.
Note: I am trying to run this by going into the Developer tab and clicking on the "Macros" button. In there I type in Test($A$2:$B$4), and then I get the error.
Is there a way to call a custom VBA subroutine from an Excel spreadsheet and pass arguments to it?
Thanks
Although this is not a generalized answer, it should work for your purposes:
Sub Test(Optional dataRange As Range = Nothing)
If dataRange is Nothing then Set dataRange = Range(Selection.Address)
Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)
End Sub
From the macro menu, simply type in Test and hit the "Run" button.
This will use the current Selection as the data range.
Alternatively, use an InputBox to capture the range argument:
Sub Test2()
Dim dataRange as Range
Set dataRange = Application.InputBox("Select range", Type:=8)
If dataRange is Nothing then Exit Sub
Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments