VBA, Overflow error, For each loop wont take variant

Question

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

i think your Intersect of Selection and ActiveSheet.UsedRange is null, there is no data in it — usmanhaq
– usmanhaq, Commented Nov 24, 2018 at 14:09
This range("D83:D114") could refer to a different (wrong) sheet, or the ActiveSheet is empty. — VBasic2008
– VBasic2008, Commented Nov 24, 2018 at 15:15
@JohnyL , not sure, do I need it? Trying to trim cells..to get rid of the extra spaces. — excelguy
– excelguy, Commented Nov 24, 2018 at 21:34
What is the value of cellAFS.Offset(0, 3).Value when it fails? Also, you should ask a new question if a new problem arises after people have given you a solution to the original problem statement. — QHarr
– QHarr, Commented Nov 25, 2018 at 5:01

DisplayName · Accepted Answer · 2018-11-24 14:36:52Z

1

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

edited Nov 24, 2018 at 14:36

answered Nov 24, 2018 at 14:04

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

excelguy Over a year ago

your code worked, but now im getting an overflow later on in my code. Must be a memory issue?

Mesut Akcan · Accepted Answer · 2020-02-29 21:46:57Z

1

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

edited Feb 29, 2020 at 21:46

Mesut Akcan

9247 silver badges19 bronze badges

answered Nov 24, 2018 at 13:56

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

3 Comments

DisplayName Over a year ago

VBA Trim() function acts differently than Excel built in one

Harun24hr Over a year ago

Then simply use worksheetfunction like j = Application.WorksheetFunction.Trim(j.Value)

excelguy Over a year ago

Thanks. I updated my question as now the overflow error is on my next For Each Loop

Collectives™ on Stack Overflow

VBA, Overflow error, For each loop wont take variant

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related