.offset() function

[owl527]

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_table")
Set rgTarget = ThisWorkbook.Sheets("WarrantDetails").Range("ric_list")
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.

[K Dales]

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_table")
> Set rgTarget =
> ThisWorkbook.Sheets("WarrantDetails").Range("ric_list")
> 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
>
>