[SOLVED] Referencing Cells without active cell

[mforner@gci.net]

New to Excel/Macros but not programming. What I am looking to do is to
create a macro that will compare entries of a row for certain
charateristics. My problem is that the number of rows will constantly
be growing at an inconstant rate AND I'd like to not have to make each
cell i'm working with the active cell as this macro runs. I'd also
like the macro to stop when the cells of reference nolonger contain
data.

The last criteria is easy, my biggest problem is referencing the cells
I would like while looping through the data one row at a time until an
empty cell is reached. I'm certain there must be a way to do it and
that the information is already out there but, the keywords I have been
trying to search with are not producing results.

rough outline of the process I'm running (appologies in advance, I'm
more of a C++ programmer): 3 columns(will refer to as A, B, and C for
the time being and A,rowindex refers to Column A row index number
because i don't know the correct way without making the cells active)

int rowindex = 1;
Do
{
If(A,rowindex < B,rowindex && A,rowindex - B,rowindex > 1/288)
C,rowindex = "Early";
else If(A,rowindex > B,rowindex && B,rowindex - A,rowindex > 1/288)
C,rowindex = "Late";
else If(A,rowindex == B,rowindex || (A,rowindex > B,rowindex &&
A,rowindex - B,rowindex <=1/288) || (A,rowindex < B,rowindex &&
B,rowindex - A,rowindex <=1/288)
C,rowindex = "On Time";
rowindex++;
} while(A,rowindex != NULL);

thats very rough of what I'm trying to do (more steps involved but the
real question for me is how can I reference cells A,rowindex etc
without using Select)

I was also wondering if there is a way to copy/assign a range of
values. Again I'd like to be able to do this without having to select
the cells.

Thanks

[Bob Phillips]

'find last row used in column A
iLastRow = Cells(Rows.Co7unt,"A").End(xlUp),Row
'then loop throiugh them all
For i = 1 To iLastRow
'get a cell 3 columns on
MsgBox Cells(i,"A").Ofdfset(0,30.Value
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<mforner@gci.net> wrote in message
news:1153175838.390965.206310@35g2000cwc.googlegroups.com...
> New to Excel/Macros but not programming. What I am looking to do is to
> create a macro that will compare entries of a row for certain
> charateristics. My problem is that the number of rows will constantly
> be growing at an inconstant rate AND I'd like to not have to make each
> cell i'm working with the active cell as this macro runs. I'd also
> like the macro to stop when the cells of reference nolonger contain
> data.
>
> The last criteria is easy, my biggest problem is referencing the cells
> I would like while looping through the data one row at a time until an
> empty cell is reached. I'm certain there must be a way to do it and
> that the information is already out there but, the keywords I have been
> trying to search with are not producing results.
>
> rough outline of the process I'm running (appologies in advance, I'm
> more of a C++ programmer): 3 columns(will refer to as A, B, and C for
> the time being and A,rowindex refers to Column A row index number
> because i don't know the correct way without making the cells active)
>
> int rowindex = 1;
> Do
> {
> If(A,rowindex < B,rowindex && A,rowindex - B,rowindex > 1/288)
> C,rowindex = "Early";
> else If(A,rowindex > B,rowindex && B,rowindex - A,rowindex > 1/288)
> C,rowindex = "Late";
> else If(A,rowindex == B,rowindex || (A,rowindex > B,rowindex &&
> A,rowindex - B,rowindex <=1/288) || (A,rowindex < B,rowindex &&
> B,rowindex - A,rowindex <=1/288)
> C,rowindex = "On Time";
> rowindex++;
> } while(A,rowindex != NULL);
>
> thats very rough of what I'm trying to do (more steps involved but the
> real question for me is how can I reference cells A,rowindex etc
> without using Select)
>
> I was also wondering if there is a way to copy/assign a range of
> values. Again I'd like to be able to do this without having to select
> the cells.
>
> Thanks
>

[Dove]

To translate your C++ code to VBA:

Dim i as Integer

For i = 1 to ActiveSheet.UsedRange.Rows.Count ' Change 1 to 2 if there is a
header row
If (Cells(i, 1).Value < Cells(i, 2).Value) And (Cells(i, 1).Value -
Cells(i, 2).Value > (1/288)) Then
Cells(i, 3).Value = "Early"
ElseIf ...... Then
Cells(i, 3).Value = "On Time"
End If
Next i


Replace the ........ for the logic of the elseif code using a similar format
to the if code but using Or as the boolean operator. (VB only needs one =
sign, <= works the same, and unfortunatley does not support ++, -- etc...)

I use the numeric equilivant for the columns instead of "A", "B" etc as an
easy reminder that they can be used with integer or long variables as well
to loop through the columns.

David

<mforner@gci.net> wrote in message
news:1153175838.390965.206310@35g2000cwc.googlegroups.com...
> New to Excel/Macros but not programming. What I am looking to do is to
> create a macro that will compare entries of a row for certain
> charateristics. My problem is that the number of rows will constantly
> be growing at an inconstant rate AND I'd like to not have to make each
> cell i'm working with the active cell as this macro runs. I'd also
> like the macro to stop when the cells of reference nolonger contain
> data.
>
> The last criteria is easy, my biggest problem is referencing the cells
> I would like while looping through the data one row at a time until an
> empty cell is reached. I'm certain there must be a way to do it and
> that the information is already out there but, the keywords I have been
> trying to search with are not producing results.
>
> rough outline of the process I'm running (appologies in advance, I'm
> more of a C++ programmer): 3 columns(will refer to as A, B, and C for
> the time being and A,rowindex refers to Column A row index number
> because i don't know the correct way without making the cells active)
>
> int rowindex = 1;
> Do
> {
> If(A,rowindex < B,rowindex && A,rowindex - B,rowindex > 1/288)
> C,rowindex = "Early";
> else If(A,rowindex > B,rowindex && B,rowindex - A,rowindex > 1/288)
> C,rowindex = "Late";
> else If(A,rowindex == B,rowindex || (A,rowindex > B,rowindex &&
> A,rowindex - B,rowindex <=1/288) || (A,rowindex < B,rowindex &&
> B,rowindex - A,rowindex <=1/288)
> C,rowindex = "On Time";
> rowindex++;
> } while(A,rowindex != NULL);
>
> thats very rough of what I'm trying to do (more steps involved but the
> real question for me is how can I reference cells A,rowindex etc
> without using Select)
>
> I was also wondering if there is a way to copy/assign a range of
> values. Again I'd like to be able to do this without having to select
> the cells.
>
> Thanks
>

[Matt]

Thanks for the help, i knew there would be a simpler way to do this