Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run time error 6: Overflow

0

Public r As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

If Not Intersect(Target, Range("BM5:BO80000")) Is Nothing Then

If Target.Value = "" Then

If r = "" Then Exit Sub

x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0))

If x >= 1 Then Cells(Target.Row, x + 49).ClearContents

ActiveCell.Offset(1, 0).Select

ActiveCell.Offset(-1, 0).Select

Exit Sub

End If

x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(Target.Value, Sheets("Key").Range("B1:B20"), 0))

If x >= 1 Then Cells(Target.Row, x + 49) = Date

ActiveCell.Offset(1, 0).Select

ActiveCell.Offset(-1, 0).Select

ElseIf Not Intersect(Target, Range("M5:M80000")) Is Nothing Then

If Target.Value = "" Then

If r = "" Then Exit Sub

Cells(Target.Row, 63).ClearContents

Cells(Target.Row, 64).ClearContents

ActiveCell.Offset(1, 0).Select

ActiveCell.Offset(-1, 0).Select

Exit Sub

End If



Dim b As Variant

' b = Split(Application.UserName, ",")(1)

Cells(Target.Row, 63) = Right(Split(Application.UserName, ",")(1), Len(Split(Application.UserName, ",")(1)) - 1)

Cells(Target.Row, 64) = Date

ActiveCell.Offset(1, 0).Select

ActiveCell.Offset(-1, 0).Select

' Cells(Target.Row, 62) = Date

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

r = Target

End Sub

getting error in the line 

r = Target

excel
MS Office Version: 2016
posted by: Dr Liss (rep: 26) on Wed Mar 24, 2021 at 1:18 pm
Answer
Discuss

Answers

0
Selected Answer

Dr Liss

In the selection change event macro, you're trying to set r to a range. Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) r = Target.Value Debug.Print r End Sub
or same with r=Target.Address.
posted by: John_Ru (rep: 6142) on Wed Mar 24, 2021 at 1:55 pm
Discuss

Discussion

Thanks John for your timely response!
I just changed the line as 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
set r = Target End Sub

This worked. its weird
 As you said when I tried to debug.print r it throws type mismatch error. not sure how it worked even with mismatch in type. 
Dr Liss (rep: 26) Mar 24, '21 at 3:00 pm
Congratulations to @John_Ru
Within a very few months of great help to hundreds of people he has now fortified his position as #3 on this forum, meaning he has twice as many points as the next laureate. And, what's more, he just needs less than another 50 points to start chasing Don for second place. That race will officially start when John has half as many points as Don. Soon to happen. Watch this space!
Dr Liss, please support John's effort. Can you Select the answer he has provided? If it solved your problem he should have the points.
Variatus (rep: 4889) Mar 24, '21 at 11:35 pm
No, it's not in the least bit weird. It's just that you conflate the the range object with the range's value property. A range, like Target, has many properties, for example, Count, Top, Left, Font, Interior, Width, Height, Formula and more. One of these properties is the Value property. That's the default. If you just call for "Target" the answer will be "Target.Value". You aren't alone in forgetting this. That's why experts recommend that programmers should always write "Target.Value" when they mean "Target.Value". I always do.
You Dim r As Variant.. A variant can hold anything. Accordingly, r = Target would mean r = Target.Value but Set r  =Target would make r a range. The difference is in the keyword "Set" and this is what Excel seems to forget. Excel knows that r is something about the Target range but looks at the context of the next use to determine if it was the Value or the range. By clarifying what r holds at the time of assigning the value there is no more need to guess later on. That's not weird. It's reasonable.
Variatus (rep: 4889) Mar 24, '21 at 11:58 pm
@Variatus. Thanks for your kind words above and the explanation about Excel guessing context (where a property isn't specified). I didn't know that but will try to curb my shortcut omissions.

I've enjoyed doing a bit more VBA (after quite a gap), especially since there's always something new to learn here, whether from questions, answers or tutorials. Keep up the good work everyone! 
John_Ru (rep: 6142) Mar 25, '21 at 2:17 am
Thnaks Variatus and John. I more thing I observed late is this r in selectionchange function is used for delete functionality. After I use set r = Target.
This deletion functionality stopped working. Do you have any idea?
Dr Liss (rep: 26) Mar 25, '21 at 3:11 am
Dr Liss. Not sure what you mean but r is now a range so (in your macro in the question), did you add. Value property to r in you code? Especially the part below... 
Application.Match(r, Sheets("Key").
Please clarify if not.
John_Ru (rep: 6142) Mar 25, '21 at 3:45 am
I did use r.value wherever I had mentioned just r. Especially inside match funtion.Though it is throwing error as Overflow
Dr Liss (rep: 26) Mar 25, '21 at 5:11 am
Dr Liss

Only just got to my PC and tried this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Target
r.Delete
End Sub
where the target cell (and contents) get deleted, shifting other cells in the row to the left.

Please post a file (in your original question).
John_Ru (rep: 6142) Mar 25, '21 at 5:23 am
ahahah Variatus, I have been just too busy to compete with you two recently! You guys have amazing answers and tips and your points are very well deserved!!!
don (rep: 1989) Mar 25, '21 at 10:23 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login