CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

VBA, Overflow error, For each loop wont take variant

I tried to change my j variable from Range to Variant but I get the

overflow error

If I change to Integer or long I get a

compile error.

Dim j As Variant
Range("D83:D114").Select
With Application.WorksheetFunction
    For Each j In Intersect(Selection, ActiveSheet.UsedRange)
        j.Value = .Trim(j.Value)
    Next j
End With

How can I make my variable not have the overflow error? Is there a way to reset my memory?

Edit.

I made the adjustment recommended, removing selection from code and putting in the range.

Now the overflow is on my next for each loop. All these variables are used multiple times and stored as Range .

How can I not run into these issues? 

For Each cellAFS In AFS.Cells
    For Each cellFV In FVOCI.Cells
        If cellFV.Value = cellAFS.Value Then
            cellFV.Offset(0, 6).Value = cellAFS.Offset(0, 3).Value / 1000
        End If
    Next
Next

Upvotes: 1

Views: 76

Answers (2)

Harun24hr
Harun24hr

Reputation: 36945

What about below Sub :

Sub ChangeValue()
    Dim j As Range
    For Each j In Range("D83:D114")
        j = Trim(j.Value)
    Next j
End Sub

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

your code works for me

you could avoid selections

Sub ChangeValue()
    Dim j As Range
    With Application.WorksheetFunction
        For Each j In Intersect(Range("D83:D114"), ActiveSheet.UsedRange)
            j.Value = .Trim(j.Value)
        Next j
    End With
End Sub

And to check for void intersection:

Sub ChangeValue()
    Dim rng As Range, j As Range
    Set rng = Intersect(Range("D83:D114"), ActiveSheet.UsedRange)
    If rng Is Nothing Then Exit Sub

    With Application.WorksheetFunction
        For Each j In rng
            j.Value = .Trim(j.Value)
        Next j
    End With
End Sub

Upvotes: 1

Related Questions