VB Loop for each Unique Value in a Range

[JoshuaD]

Good Afternoon,

I am trying to get my code to loop through a column and stop when the active cells value is blank. For instance I am trying to print a list of strings next to dates that I use in another loop. The list of strings is in column "H" starting at H2. However when I try and offset my UniqueVal variable I get an error. Any ideas?

Sub GenDates_Values()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim UniqueVal As String

UniqueVal = Range("H2")

Do Until UniqueVal.Value=""

    NextDate = FirstDate
    FirstDate = Range("startdate").Value
    LastDate = Range("enddate").Value

    'Cell to begin entering dates
    Range("B3").Select
    'selection of columns within one row
    Do Until NextDate > LastDate

     ActiveCell.Value = NextDate
     'Move one column over
     ActiveCell.Offset(0, -1).Select
     'Set name as the unique character
     ActiveCell.Value = UniqueVal
     'Return to date cell
     ActiveCell.Offset(0, 1).Select
     'Move one row down
     ActiveCell.Offset(1, 0).Select
     NextDate = NextDate + 1

    Loop
    
    
 UniqueVal = UniqueVal.Offset(1, 0).Select
 
 Loop

End Sub

 

[V_Malkoti]

Declare UniqueVal as Range.

 

[JoshuaD]

Declare UniqueVal as Range.

Well I cannot believe I overlooked that one! I have made some progress in my code. The only issue is that I am trying to add a condition that once the range UniqueVal hits a blank cell it stops. However my If statement causes it to do nothing. I have tried playing around with Do Until or Do While loops, but I must say that I am not as versed in those.

Sub GenerateDates()
Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim UniqueVal As Range
Dim Drange As Range
Dim Rng As Range
Dim Stng As Variant
Set UniqueVal = Range("H2:H20")
Set Rng = Range("B3")

FirstDate = Range("startdate").Value
LastDate = Range("enddate").Value
                'Cell to begin entering dates
Rng.Select
For Each Cell In UniqueVal
If UniqueVal.Cells.Text <> "" Then
                NextDate = FirstDate
                
 
                'selection of columns within one row
                Do Until NextDate > LastDate
                    Rng.Select
                    ActiveCell.Value = NextDate
                    'Move one column over
                    ActiveCell.Offset(0, -1).Select
                    'Set name as the unique character
                    ActiveCell.Value = UniqueVal.Value
                    'Return to date cell
                    ActiveCell.Offset(0, 1).Select
                    'Move one row down
                    ActiveCell.Offset(1, 0).Select
                    Set Rng = ActiveCell
                    NextDate = NextDate + 1
                
                Loop
                
                
 UniqueVal.Offset(1, 0).Select
 Set UniqueVal = ActiveCell
     
                
End If
     
Next
    

End Sub

 

[V_Malkoti]

Thats' because the code doesn't have a statement to break out of the For-Next loop. You need to place an Exit For statement to exit loop if the cell is blank.