Set Statements in VBA


Posted by Barry Ward on August 13, 2001 2:49 AM

I'm using an input box to select data for a macro to use but don't know how to select that data once it is set.

If the above sounds cobnfusing it's because I have zero knowledge of VBA and am trying to scratch something together. The relevant section of code is below:

selects header names

Set headers = _
Application.InputBox _
("Select range of cells containing headers:", Type:=8)

If IsObject(headers) = False Then Exit Sub

MsgBox headers.Rows.count

'copies headers new sheet

Selection.Copy
Sheets("Results").Select
ActiveSheet.paste

'selects input data

Set Data = _
Application.InputBox _
("Select range of cells containing the data to be unbinned:", Type:=8)

If IsObject(Data) = False Then Exit Sub

MsgBox Data.Rows.count

'copies data to new sheet

Selection.Copy
Sheets("Results").Select
ActiveSheet.paste

Posted by Ivan F Moala on August 13, 2001 3:23 AM

Change Selection to your relavent data object
= headers (1st) and Data (2nd)

so you have

hearders.copy
data.copy

As you have assigned the selection to the object
headers and data.....

couple of other points

1) You know it is a range object you are after
as you have already explicitly set this in the
input Type:=8 so declare the headers as a range
2) No need to select the sheet to paste to
quicker to copy direct
3) In your code if the user cancels you will
halt the macro due to error. handle this by
setting the headers as a range object (pt1)
and using error handling to handle the cancel

eg

headers.Copy Destination:=Sheets("Results").[A1]

Where [A1] is the cell to copy to

On Error Resume Next
Set data = _
Application.InputBox _
("Select range of cells containing the data to be unbinned:", Type:=8)
If data Is Nothing Then Exit Sub
On Error GoTo 0

HTH


Ivan Set headers = _ Application.InputBox _ ("Select range of cells containing headers:", Type:=8) If IsObject(headers) = False Then Exit Sub



Posted by Barry Ward on August 13, 2001 4:27 AM

Thanks Ivan


Just one thing about the error handling.

if no headers are selected I want a message box that checks that the user doesn't want headers and if so allows the macro to skip to the input box to select data

Thanks

Barry