Problem in Excel...String of Ranges?

[stephentebaldi]

Is it possible to have a user select multiple cells associated with a
sample? My vba is below, but it keeps giving me an error on having the
InputBox = type 8 (range). Is this not possible? I figured it would
be fine to have the layout like this...but if someone knows of a better
way that would work it would be fine.

Sub Cmpds()

Dim Compounds() As String
Dim numCompounds As Long
Dim cnt As Long
numCompounds = InputBox("Enter the Number of Compounds")
ReDim Compounds(1 To numCompounds)
For cnt = 1 To numCompounds
Compounds(cnt) = InputBox("Please Enter Compound " & cnt)
Next

Dim CmpdRng() As String
Dim count As Long
ReDim CmpdRng(1 To numCompounds)
For count = 1 To numCompounds
CmpdRng(cnt) = InputBox("Please Select Data for Compound " &
count, Type:=8)
Next

End Sub

 

[Die_Another_Day]

Stephen, if you want to specify the type of entry in a InputBox then
you have to use Application.InputBox instead of just inputbox. See this
article from Joseph Rubin's Site:
http://www.exceltip.com/st/Decide_w..._return_using_VBA_in_Microsoft_Excel/408.html

Charles

 

[Guest]

Try:
Dim numCompounds As Long
Dim cnt As Long
Dim CmpdRng() As Range
Dim count As Long

numCompounds = InputBox("Enter the Number of Compounds")

ReDim CmpdRng(numCompounds)
For count = 1 To numCompounds
Set CmpdRng(count) = Application.InputBox(prompt:="Please Select Data
for Compound ", Type:=8)
Next

 

[stephentebaldi]

Charles,

Again, thanks, you have been a lifesaver here...I figured that out a
little bit ago, but it still it not liking the rest of my code, like it
doesn't want to loop through the compounds to select more data. I am
not sure if it's my code (most likely) but it should be able to store
multiple ranges of cells right?

 

[Guest]

Stefen,

Use this modified macro :

Sub Cmpds()
Dim Compounds() As String
Dim numCompounds As Long
Dim cnt As Long
numCompounds = InputBox("Enter the Number of Compounds")
ReDim Compounds(1 To numCompounds)
For cnt = 1 To numCompounds
Compounds(cnt) = InputBox("Please Enter Compound " & cnt)
Next
Dim rTempRange As Range
Dim sCmpdRng() As String
Dim count As Long
ReDim sCmpdRng(1 To numCompounds)
For count = 1 To numCompounds
Set rTempRange = Application.InputBox("Please Select Data for
Compound " & count, Type:=8)
sCmpdRng(count) = rTempRange.Address
Next
End Sub

 

[stephentebaldi]

I have gotten it to where it will let me select the cells, but then it
tells me "Object variable or With block variable not set". I don't
know why I would need to have a with block here, it seems as though the
way the macro is written it should be fine as is.

 

[Tom Ogilvy]

code posted by ccroche worked fine for me as long as I selected a range.
What is your problem?