CODEWITHSUNDEEP
vbaexcel
Harish jayakumar
Harish jayakumar

Reputation: 1

For each fails when there is only one cell selected

I am selecting a range into variant in VBA

Dim source as variant
source = Range("A4:A" & rowcount)

and then I am having a for each

For Each element in source 
.....
.....

This works when there are 2-3 values selected, but when rowcount is 4,Range("A4:A" & rowcount) will select only a cell and for each is not working

How can I make it work even when only one value is there

I tried

If (rowcount=4) Then
    redim preserve source(1)
    source(1,1) = source

But it didn't work

Upvotes: 0

Views: 361

Answers (2)

Paresh J
Paresh J

Reputation: 2419

Try this and check if this works for you:

Where, ThisWorkbook.Sheets(1) refers to Sheet1 of your workbook.

Sub try()
Dim source As Range
RowCount = 4
Set source = ThisWorkbook.Sheets(1).Range("A4:A" & RowCount)

For Each element In source
MsgBox "hi"

Next

End Sub

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23550

You need to make it 2 dimensional

Dim var As Variant
Dim rng As Range
Dim thing As Variant
Set rng = Range("a1")
If rng.CountLarge = 1 Then
    ReDim var(1 To 1, 1 To 1)
    var(1, 1) = rng.Value2
Else
    var = rng.Value2
End If
For Each thing In var
    MsgBox thing
Next thing

Upvotes: 1

Related Questions