Hello
Title of thread is clear. Attaching the image
With the below code at a point I am able to get range address of Each Red Thick Border Mark on combobox ie address of Formulated Serial Nos
Following Results
After the above corrections somewhere I would like to display another range after the above each formulated SR.Nos range in combobox like below
Excuse me if this too lengthy.
Am really struggling to get Blue marked range address after Each Red marked border Range Address for last couple of days.
Shall be really grateful to you
SamD
110
Title of thread is clear. Attaching the image
With the below code at a point I am able to get range address of Each Red Thick Border Mark on combobox ie address of Formulated Serial Nos
VBA Code:
Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
Call GetRangeFormualtedSrNo
End Sub
Public Sub GetRangeFormualtedSrNo()
Dim wks1 As Worksheet
Set wks1 = Worksheets("Sheet1")
Dim rngAddString As String
Dim Ray() As String
Dim C As Range, LastA As Range, rng As Range
Dim rws As Long, k As Long, idx As Long
wks1.Activate
Set LastA = wks1.Range("A" & Range("C" & Rows.Count).End(xlUp).Row)
For Each C In wks1.Range("A4", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas)
rws = 1
If IsEmpty(C.Offset(1).Value) And C.Address <> LastA.Address Then rws = rws + Range(C, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
k = k + 1
ReDim Preserve Ray(1 To k)
Ray(k) = C.Resize(rws, 3).Address(0, 0)
Next C
ComboBox1.List = Ray
ComboBox1.Text = Ray(1)
End Sub
Serial Nos | Formulated Range in Combobox List | Remarks |
1 | A4:C7 | |
2 | A8:C10 | |
3 | A11:C12 | |
4 | A13:C13 | |
5 | A14:C22 | Actually this should display as A14:C14. I don’t know why this is happening. Will be glad if this range is not displayed in combobox |
After the above corrections somewhere I would like to display another range after the above each formulated SR.Nos range in combobox like below
Serial Nos | Formulated Range | Combobox to display as Marked in Bold |
1 | A4:C7 | A4:C7 A16: C22 where in the range A16:C22 column C includes words Optional Offers |
2 | A8:C10 | A8:C10 A16: C22 where in the range A16:C22 column C includes words Optional Offers |
3 | A11:C12 | A11:C12 A16: C22 where in the range A16:C22 column C includes words Optional Offers |
4 | A13:C13 | A13:C13 A16: C22 where in the range A16:C22 column C includes words Optional Offers |
5 | A14:C23 corrections to A14:C14 | A14:C14 A16: C22 where in the range A16:C22 column C includes words Optional Offers |
Excuse me if this too lengthy.
Am really struggling to get Blue marked range address after Each Red marked border Range Address for last couple of days.
Shall be really grateful to you
SamD
110