Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells without active cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells without active cell
'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) wrote in message ups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells without active cell
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 wrote in message ups.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells without active cell
Thanks for the help, i knew there would be a simpler way to do this
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing cell in active row | Excel Worksheet Functions | |||
referencing active cell works in a sub but not in a custom function | Excel Worksheet Functions | |||
referencing active cell works in a sub but not in a custom function | Excel Worksheet Functions | |||
referencing the active cell | Excel Programming | |||
Referencing cells on non-active sheets | Excel Programming |