Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting values in row and advancing
I have a worksheet with four columns of data. I want to loop through rows,
starting at A5 and get the values (and assign to variables) from column1, 2, 3 and 4 respectively then advance to the next row untile all four cells in the row are blank. Help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting values in row and advancing
Try
Dim Rng As Range Dim V1 As Variant Dim V2 As Variant Dim V3 As Variant Dim V4 As Variant Set Rng = Range("A5") V1 = Rng(1, 1) V2 = Rng(1, 2) V3 = Rng(1, 3) V4 = Rng(1, 4) Do Until V1 = "" And V2 = "" And V3 = "" And V4 = "" Set Rng = Rng(2, 1) V1 = Rng(1, 1) V2 = Rng(1, 2) V3 = Rng(1, 3) V4 = Rng(1, 4) Loop -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Billy B" wrote in message ... I have a worksheet with four columns of data. I want to loop through rows, starting at A5 and get the values (and assign to variables) from column1, 2, 3 and 4 respectively then advance to the next row untile all four cells in the row are blank. Help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting values in row and advancing
To read the values in A-D into four variables to use immediately: for i = 1 to intersect(activesheet.usedrange,[a:d]).rows.count A_variable = cells(i,1).value B_variable = cells(i,2).value C_variable = cells(i,3).value D_variable = cells(i,4).value ' Perform your calculation here next To read them into an array to use later: Dim varrayCellValues as Variant data_count = intersect(activesheet.usedrange,[a:d]).rows.count ReDim varrayCellValues(1 to 4, 1 to data_count) for i = 1 to 4 ' refers to the row for j = 1 to data_count ' refers to the column varrayCellValues(i, j) = cells(j, i).value next: next Hope that helps Col -- colofnature ------------------------------------------------------------------------ colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356 View this thread: http://www.excelforum.com/showthread...hreadid=548618 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting values in row and advancing
To read the values in A-D into four variables to use immediately: for i = 1 to intersect(activesheet.usedrange,[a:d]).rows.count A_variable = cells(i,1).value B_variable = cells(i,2).value C_variable = cells(i,3).value D_variable = cells(i,4).value ' Perform your calculation here next To read them into an array to use later: Dim varrayCellValues as Variant data_count = intersect(activesheet.usedrange,[a:d]).rows.count ReDim varrayCellValues(1 to 4, 1 to data_count) for i = 1 to 4 ' refers to the row for j = 1 to data_count ' refers to the column varrayCellValues(i, j) = cells(j, i).value next: next Hope that helps Col -- colofnature ------------------------------------------------------------------------ colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356 View this thread: http://www.excelforum.com/showthread...hreadid=548618 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing the year | Excel Discussion (Misc queries) | |||
Advancing One row without enter | Excel Discussion (Misc queries) | |||
Advancing formula | Excel Worksheet Functions | |||
Advancing Down A List | Excel Worksheet Functions | |||
Advancing to the next cell | Excel Programming |