Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add non-contiguous cells to range?
A worksheet has one column of invoice numbers. The invoice number is
repeated in contiguous rows for each item in listed on that invoice. The column to the left has text in some cells. Right now I am grabbing the invoice number from another sheet, using Find to set an initial range to the first cell in the column with that number, and then using the loop code below to extend the range to include all the cells with that same number. Do If rngWork.Offset(1, 0).Text = strInvNo Then Set rngWork = Union(rngWork, rngWork.Offset(1, 0)) Else Exit Do End If Loop What I would like to do, but can't figure out how, is to check the cell to the left for text: if text is present, do NOT add the cell to the range. Something like: Do If rngWork.Offset(1, 0).Text = strInvNo And _ rngWork.Offsset(1,1).Text = "" Then ADD rngWork.Offset(1, 0) TO RNGWORK Else Exit Do End If Loop Union and Intersect I know. But how to I add a non-contiguous cell to a range? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add non-contiguous cells to range?
Ed,
Union can be used to create a non-contiguous range, but you will need to change the way you track the data you are testing. Assuming that rngWork starts out as a single starting cell (and you mean the cell to the right has the text), you could use something like the following: Set rngStart = rngWork lRow = 1 Do While rngStart.Text = rngStart.Offset(lRow, 0).Text If rngStart.Offset(lRow, 1).Text = "" Then Set rngWork = Union(rngWork, rngStart.Offset(lRow, 0)) End If lRow = lRow + 1 Loop John Green "Ed" wrote in message ups.com... A worksheet has one column of invoice numbers. The invoice number is repeated in contiguous rows for each item in listed on that invoice. The column to the left has text in some cells. Right now I am grabbing the invoice number from another sheet, using Find to set an initial range to the first cell in the column with that number, and then using the loop code below to extend the range to include all the cells with that same number. Do If rngWork.Offset(1, 0).Text = strInvNo Then Set rngWork = Union(rngWork, rngWork.Offset(1, 0)) Else Exit Do End If Loop What I would like to do, but can't figure out how, is to check the cell to the left for text: if text is present, do NOT add the cell to the range. Something like: Do If rngWork.Offset(1, 0).Text = strInvNo And _ rngWork.Offsset(1,1).Text = "" Then ADD rngWork.Offset(1, 0) TO RNGWORK Else Exit Do End If Loop Union and Intersect I know. But how to I add a non-contiguous cell to a range? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add non-contiguous cells to range?
John: Thanks so much! It worked great! Every time I tried Union
before, I would wind up with everything. This, though, did exclude the cells with text. Thanks for the boost. Ed On Mar 26, 5:56 pm, "John Green" <greenj@nospam wrote: Ed, Union can be used to create a non-contiguous range, but you will need to change the way you track the data you are testing. Assuming that rngWork starts out as a single starting cell (and you mean the cell to the right has the text), you could use something like the following: Set rngStart = rngWork lRow = 1 Do While rngStart.Text = rngStart.Offset(lRow, 0).Text If rngStart.Offset(lRow, 1).Text = "" Then Set rngWork = Union(rngWork, rngStart.Offset(lRow, 0)) End If lRow = lRow + 1 Loop John Green "Ed" wrote in message ups.com... A worksheet has one column of invoice numbers. The invoice number is repeated in contiguous rows for each item in listed on that invoice. The column to the left has text in some cells. Right now I am grabbing the invoice number from another sheet, using Find to set an initial range to the first cell in the column with that number, and then using the loop code below to extend the range to include all the cells with that same number. Do If rngWork.Offset(1, 0).Text = strInvNo Then Set rngWork = Union(rngWork, rngWork.Offset(1, 0)) Else Exit Do End If Loop What I would like to do, but can't figure out how, is to check the cell to the left for text: if text is present, do NOT add the cell to the range. Something like: Do If rngWork.Offset(1, 0).Text = strInvNo And _ rngWork.Offsset(1,1).Text = "" Then ADD rngWork.Offset(1, 0) TO RNGWORK Else Exit Do End If Loop Union and Intersect I know. But how to I add a non-contiguous cell to a range? Ed- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join non-contiguous ranges into one range via named range? | Excel Discussion (Misc queries) | |||
counting cells that are 0 in a range of non-contiguous cells | Excel Worksheet Functions | |||
non-contiguous range | Excel Programming | |||
pasting non-contiguous range of cells to new row, same cell locati | New Users to Excel | |||
Range holding non-contiguous cells | Excel Programming |