Assigning an unknown variable

[ShaunD30]

Not sure how to go about doing this. Lets say I have a sheet and this is column A

1234
1234
1234
1235
1235
1236
1236

I need the code to loop through the data in column A and assign a variable for the range of 1234..and then as soon as it finds 1235, assign a variable for the range which it finds 1235..and so on..so each unique set of values has a variable..

Is this possible?

Shaun

 

[schielrn]

Not sure what you mean by has a unique variable? Do you mean set it as a named range? Sorry I am leaving work now, but will check back later tonight or tomorrow if someone hasn't given you an answer, but I'm not completely sure what you are asking for?

 

[mikerickson]

Perhaps a collection will work

Dim collectionOfRanges As New Collection
Dim keyStr As String, newRange As Range
Dim oneCell As Range
With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust
    For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    
        keyStr = CStr(oneCell.Value)
        Set newRange = oneCell
        
        On Error Resume Next
            Set newRange = Application.Union(collectionOfRanges(keyStr), oneCell)
            collectionOfRanges.Remove keyStr
        On Error GoTo 0
        collectionOfRanges.Add Item:=newRange, key:=keyStr
    Next
End With
MsgBox collectionOfRanges("1234").Address

 

[ShaunD30]

I get this error when it gets to the MsgBox

Run-Time Error '5':

Invalid procedure call or argument

 

[mikerickson]

Is 1234 one of the cells in column A? If so, is it text with a leading/trailing space?

Try

MsgBox collectionOfRanges(CStr(Range("A1").Value)).Address