Looping Through A Range and Transferring Values To 2 Offset Sections of One List

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am hoping someone can help me with some support in what I am planning to do ...

Code:
         For L2 = 2 To lrw
            shift = ws_thold.Cells(L2, 1)
            crw_st = ws_thold.Cells(L2, 4)
            crw_ed = bkg_date + ws_thold.Cells(L2, 5)
            If crw_ed < crw_st Then crw_ed = bkg_date + 1 + crw_ed
            If Round(bkg_dst, 3) >= Round(crw_st, 3) And Round(bkg_dst, 3) <= Round(crw_ed, 3) Then
                ws_thold.Cells(irw, 9) = crw_st
                irw = irw + 1
            Else
                'start new section 2nd cell below the last cell above
                
            End If
        Next L2

This code loops through a range of cells A2 to A(lrw) - the last row of the dyanmic range in column A.
What I need to do is create a broken list of values in column I of ws_thold. The list will be comprised of two sections separated by a cell with a value of "----". The top section will be built on values that meet the criteria in the "IF" statement. The second section will hold the values that don't. The values of "NA" and "NR" are added to the end of the first section. The entire list of the combined sections will be the source of a validation list.

So, if the data A2:A11 is: (sorry, I have the addon XL2BB installed, checked, and customized in my ribbon, but I cannot find an Addin Menu anywhere to select it, otherwise I would have used it) . The text in italic is not data ... just my way of showing you which values would meet the IF criteria.

HPE1 true
HPL1
RPE1 true
RPL1 true
WPE1 true
WPL1
CUE1 true
EVE1
EVE2
CUL1

The result (in column I) I would like to acquire is ...
HPE1
RPE1
RPL1
WPE1
CUE1
NA
NR
----
HPL1
WPL1
EVE1
EVE2
CUL1


With my code, I can make the first section of the list, but I don't know how to deal with the ELSE. I need to maintain a gap of one cell from the last value in section one, while still adding values to the end of section one as they are encountered in the list, while building section 2 accordingly.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top