Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,616
- Office Version
- 365
- 2016
- Platform
- Windows
I am hoping someone can help me with some support in what I am planning to do ...
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.
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.