I am trying to make a macro that will allow me to specify the number of
compounds that will be used in an experiment and then display that many
input boxes (or a list box if that will work) for the user to input the
names of the compounds. I am having a hard time figuring out if this
is possible because the loop doesn't seem to want to keep the names of
each compound separate. Any help would be much appreciated.

TIA
Stephen

Try something like this:
Dim numCompounds as long
Dim cnt as long
numCompounds = InputBox("Enter the Number of Compounds")
For cnt = 1 to numCompounds
Range("A" & cnt) = InputBox("Please Enter Compound " & cnt)
next

Charles
wrote:
I am trying to make a macro that will allow me to specify the number of
compounds that will be used in an experiment and then display that many
input boxes (or a list box if that will work) for the user to input the
names of the compounds. I am having a hard time figuring out if this
is possible because the loop doesn't seem to want to keep the names of
each compound separate. Any help would be much appreciated.

TIA
Stephen

So would there be a way to call on these later on? If I was to specify
what compounds were there and then input a range of data is there a way
to enter what cells contain what compounds to make calculations easier?
Most compounds will have multiple cells which will require an average.
Die_Another_Day wrote:
Try something like this:
Dim numCompounds as long
Dim cnt as long
numCompounds = InputBox("Enter the Number of Compounds")
For cnt = 1 to numCompounds
Range("A" & cnt) = InputBox("Please Enter Compound " & cnt)
next

Charles
wrote:
I am trying to make a macro that will allow me to specify the number of
compounds that will be used in an experiment and then display that many
input boxes (or a list box if that will work) for the user to input the
names of the compounds. I am having a hard time figuring out if this
is possible because the loop doesn't seem to want to keep the names of
each compound separate. Any help would be much appreciated.

TIA
Stephen

Use an array variable:
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

Then Reference like this:
Variable1 = Compounds(1)

Charles

wrote:
So would there be a way to call on these later on? If I was to specify
what compounds were there and then input a range of data is there a way
to enter what cells contain what compounds to make calculations easier?
Most compounds will have multiple cells which will require an average.
Die_Another_Day wrote:
Try something like this:
Dim numCompounds as long
Dim cnt as long
numCompounds = InputBox("Enter the Number of Compounds")
For cnt = 1 to numCompounds
Range("A" & cnt) = InputBox("Please Enter Compound " & cnt)
next

Charles
wrote:
I am trying to make a macro that will allow me to specify the number of
compounds that will be used in an experiment and then display that many
input boxes (or a list box if that will work) for the user to input the
names of the compounds. I am having a hard time figuring out if this
is possible because the loop doesn't seem to want to keep the names of
each compound separate. Any help would be much appreciated.

TIA
Stephen

Thanks so much for your help, I have been trying to work this out to
make my research easier for a week now. I figured I would be able to
get it all done myself, apparently not though.

Thanks again
Die_Another_Day wrote:
Use an array variable:
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

Then Reference like this:
Variable1 = Compounds(1)

Charles

wrote:
So would there be a way to call on these later on? If I was to specify
what compounds were there and then input a range of data is there a way
to enter what cells contain what compounds to make calculations easier?
Most compounds will have multiple cells which will require an average.
Die_Another_Day wrote:
Try something like this:
Dim numCompounds as long
Dim cnt as long
numCompounds = InputBox("Enter the Number of Compounds")
For cnt = 1 to numCompounds
Range("A" & cnt) = InputBox("Please Enter Compound " & cnt)
next

Charles
wrote:
I am trying to make a macro that will allow me to specify the number of
compounds that will be used in an experiment and then display that many
input boxes (or a list box if that will work) for the user to input the
names of the compounds. I am having a hard time figuring out if this
is possible because the loop doesn't seem to want to keep the names of
each compound separate. Any help would be much appreciated.

TIA
Stephen

I had one more quick question, so if I were to put in the data in an
array format (8x8, 9x9, etc.) is there any way that I would be able to
highlight the cells that correspond to compound 1 (say cells 1-4),
compound 2 (5-8), etc. and have the macro calculate averages based on
this? Sometimes my data will get up to 12x12 and writing a repetitive
if...then statement covering sample numbers ranging from 1-48 would be
really tedious. Not sure if there is a way that the you can denote
cells on a template lets say and have excel use the corresponding cells
for calculations.