Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.offset() function
Hi, can someone please let me know what the Dowhile loop is doing please? Set ws = ThisWorkbook.Sheets("WarrantDetails") Set rgSource ThisWorkbook.Sheets("SG_WarrantRic").Range("ric_ta ble") Set rgTarget ThisWorkbook.Sheets("WarrantDetails").Range("ric_l ist") Set rgUnder ThisWorkbook.Sheets("WarrantDetails").Range("under _list") nRow = 0 ntRow = 0 ws.Cells.ClearContents Do While Not Left(rgSource.Offset(nRow, 0).Value, 4) = "#N/A" For nCol = 1 To 14 If Trim(rgSource.Offset(nRow, nCol - 1).Value) < "" Then rgTarget.Offset(ntRow, 0).Value = rgSource.Offset(nRow nCol - 1).Value ntRow = ntRow + 1 End If Next nCol nRow = nRow + 1 Loop Thanks -- owl52 ----------------------------------------------------------------------- owl527's Profile: http://www.excelforum.com/member.php...fo&userid=2091 View this thread: http://www.excelforum.com/showthread.php?threadid=47619 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.offset() function
It is looping through the rows beginning at the upper left of rgSource. As
it steps through the rows it is looking at the first column of each row: rgSource.Offset(nRow,0). Offset(n,m) finds the cell that is n rows and m columns from the range it is used on. So starting at Offset(0,0) it is referring to the upper left cell of rgSource. Each time it loops it is adding one to the row offset (nRow = NRow +1, just before the Loop statement) while the column offset remains zero, so it is stepping down the rows and looking in the first column. It is testing the value it finds there to see if it begins with "#N/A"; when it finds this it stops checking the rows. In the meantime (i.e. before it reaches #N/A) the code will loop through 14 columns of the row (Offset(nRow,NCol-1)) and check for a blank cell (or only spaces); if there is any value other than blank it copies the value to the target range (rgTarget). By adding 1 to ntRow each time it copies something it is stepping down 1 row in the target range; so it is converting the rows of values across the source range into a column in the target range. -- - K Dales "owl527" wrote: Hi, can someone please let me know what the Dowhile loop is doing please? Set ws = ThisWorkbook.Sheets("WarrantDetails") Set rgSource = ThisWorkbook.Sheets("SG_WarrantRic").Range("ric_ta ble") Set rgTarget = ThisWorkbook.Sheets("WarrantDetails").Range("ric_l ist") Set rgUnder = ThisWorkbook.Sheets("WarrantDetails").Range("under _list") nRow = 0 ntRow = 0 ws.Cells.ClearContents Do While Not Left(rgSource.Offset(nRow, 0).Value, 4) = "#N/A" For nCol = 1 To 14 If Trim(rgSource.Offset(nRow, nCol - 1).Value) < "" Then rgTarget.Offset(ntRow, 0).Value = rgSource.Offset(nRow, nCol - 1).Value ntRow = ntRow + 1 End If Next nCol nRow = nRow + 1 Loop Thanks. -- owl527 ------------------------------------------------------------------------ owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=476190 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
large function result as reference for offset function | Excel Discussion (Misc queries) | |||
offset() function #VALUE! | Excel Discussion (Misc queries) | |||
XL2002 - OFFSET function and LARGE function | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
help with offset function | Excel Worksheet Functions |