Trying to use cells as a variable to calculate(count) an array
 

I am trying to program a dialog box to ask for the first cell which would be
the upper most left cell. Then I am going to try to get a second reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times the
program would go through each line. Afterwards it would spit out a text file
of information taken from the excel file. I know it's easy to say if a
person from 1-1000, but how would I do this if a person selects from Cell A1
- Cell H1000? How would I be able to have my program read the cell number 1
and cell number 1000 without the A and the H? SO I guess I am asking how
would I have it select Range(1:1000) instead of Range(A1:H1000)?

Trying to use cells as a variable to calculate(count) an array
 

Brett,
Something like this? It will select ROWS from Firstrow to
Lastrow

Sub GetRows()

Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long

Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only", Type:=8)
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE cell
only", Type:=8)
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

End Sub


HTH

"Brett Smith" wrote:

I am trying to program a dialog box to ask for the first cell which would be
the upper most left cell. Then I am going to try to get a second reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times the
program would go through each line. Afterwards it would spit out a text file
of information taken from the excel file. I know it's easy to say if a
person from 1-1000, but how would I do this if a person selects from Cell A1
- Cell H1000? How would I be able to have my program read the cell number 1
and cell number 1000 without the A and the H? SO I guess I am asking how
would I have it select Range(1:1000) instead of Range(A1:H1000)?

Trying to use cells as a variable to calculate(count) an array
 

Thanks Toppers, this is a big help. Now how do I convert a range value to an
integer value so that I could actually use it in an array type formula?

Brett

"Toppers" wrote:

Brett,
Something like this? It will select ROWS from Firstrow to
Lastrow

Sub GetRows()

Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long

Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only", Type:=8)
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE cell
only", Type:=8)
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

End Sub


HTH

"Brett Smith" wrote:

I am trying to program a dialog box to ask for the first cell which would be
the upper most left cell. Then I am going to try to get a second reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times the
program would go through each line. Afterwards it would spit out a text file
of information taken from the excel file. I know it's easy to say if a
person from 1-1000, but how would I do this if a person selects from Cell A1
- Cell H1000? How would I be able to have my program read the cell number 1
and cell number 1000 without the A and the H? SO I guess I am asking how
would I have it select Range(1:1000) instead of Range(A1:H1000)?

Trying to use cells as a variable to calculate(count) an array
 

Brett,
Sorry to be so dumb but I am not sure what you mean by
converting a range value to an integer value. Can you give an example of what
you need (array formula) ?

"Brett Smith" wrote:

Thanks Toppers, this is a big help. Now how do I convert a range value to an
integer value so that I could actually use it in an array type formula?

Brett

"Toppers" wrote:

Brett,
Something like this? It will select ROWS from Firstrow to
Lastrow

Sub GetRows()

Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long

Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only", Type:=8)
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE cell
only", Type:=8)
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

End Sub


HTH

"Brett Smith" wrote:

I am trying to program a dialog box to ask for the first cell which would be
the upper most left cell. Then I am going to try to get a second reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times the
program would go through each line. Afterwards it would spit out a text file
of information taken from the excel file. I know it's easy to say if a
person from 1-1000, but how would I do this if a person selects from Cell A1
- Cell H1000? How would I be able to have my program read the cell number 1
and cell number 1000 without the A and the H? SO I guess I am asking how
would I have it select Range(1:1000) instead of Range(A1:H1000)?

Trying to use cells as a variable to calculate(count) an array
 

Nevermind, I figured it out. It is Int("value") which converts a range to an
int value. Thanks!

Brett

"Brett Smith" wrote:

Thanks Toppers, this is a big help. Now how do I convert a range value to an
integer value so that I could actually use it in an array type formula?

Brett

"Toppers" wrote:

Brett,
Something like this? It will select ROWS from Firstrow to
Lastrow

Sub GetRows()

Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long

Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only", Type:=8)
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE cell
only", Type:=8)
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

End Sub


HTH

"Brett Smith" wrote:

I am trying to program a dialog box to ask for the first cell which would be
the upper most left cell. Then I am going to try to get a second reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times the
program would go through each line. Afterwards it would spit out a text file
of information taken from the excel file. I know it's easy to say if a
person from 1-1000, but how would I do this if a person selects from Cell A1
- Cell H1000? How would I be able to have my program read the cell number 1
and cell number 1000 without the A and the H? SO I guess I am asking how
would I have it select Range(1:1000) instead of Range(A1:H1000)?

Trying to use cells as a variable to calculate(count) an array
 

In your scenario, first is always 1, last would be

rng.count

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brett Smith" wrote in message
...
Thanks Toppers, this is a big help. Now how do I convert a range value to
an
integer value so that I could actually use it in an array type formula?

Brett

"Toppers" wrote:

Brett,
Something like this? It will select ROWS from Firstrow to
Lastrow

Sub GetRows()

Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long

Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only", Type:=8)
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cell
only", Type:=8)
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

End Sub


HTH

"Brett Smith" wrote:

I am trying to program a dialog box to ask for the first cell which
would be
the upper most left cell. Then I am going to try to get a second
reading
from a dialog box which a user would enter the lowest most right cell.
Afterwards I want to be able to calculate in my code how many times
the
program would go through each line. Afterwards it would spit out a
text file
of information taken from the excel file. I know it's easy to say if
a
person from 1-1000, but how would I do this if a person selects from
Cell A1
- Cell H1000? How would I be able to have my program read the cell
number 1
and cell number 1000 without the A and the H? SO I guess I am asking
how
would I have it select Range(1:1000) instead of Range(A1:H1000)?