I am having a problem with my macros not wanting to run if I use
"selection" in the coding. I am using the exact same code that I had
been using in the past, i just placed it in a new add-in I am creating.
But now I keep getting errors. Is there such a thing as a macro that
will screw up "selection" in the whole workbook? Sometimes I can place
individual macros in workbooks and it will work.

here is an example of code that I use:

Sub MarkDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String

Application.ScreenUpdating = False
On Error Resume Next
Set rAllRange = Selection
If WorksheetFunction.CountA(rAllRange) < 2 Then
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Selection.EntireColumn.Insert

Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)

If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
Set rAllRange = Union(rConstRange, rFormRange)
ElseIf Not rConstRange Is Nothing Then
Set rAllRange = rConstRange
ElseIf Not rFormRange Is Nothing Then
Set rAllRange = rFormRange
Else
MsgBox "Your selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Application.Calculation = xlCalculationManual

For Each rCell In rAllRange
strAdd = rCell.Address
strAdd = rAllRange.Find(What:=rCell, After:=rCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Address

If strAdd < rCell.Address Then
rCell.Offset(0, -1).Value = "Duplicate"
End If
Next rCell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub

In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

Hi,

You should specify the Selection address first,
before running code related selection ...
Perhaps you selection is empty or nothing, so Error raised

Tks
Halim

Im sorta new to vba, what exactly do you mean by specify the selection?


I have tried doing something like:
Dim Rng as Range
Set Rng = Selection

But this does not seem to help. Also this error occurs when I try to
compile the project (do i still have to select something when i do
that?)


wrote:
In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

Hi,

You should specify the Selection address first,
before running code related selection ...
Perhaps you selection is empty or nothing, so Error raised

Tks
Halim

Your code expects expects you (the user) to have the range to clean up already
selected.

Do you select the range first, then invoke the macro?

wrote:

I am having a problem with my macros not wanting to run if I use
"selection" in the coding. I am using the exact same code that I had
been using in the past, i just placed it in a new add-in I am creating.
But now I keep getting errors. Is there such a thing as a macro that
will screw up "selection" in the whole workbook? Sometimes I can place
individual macros in workbooks and it will work.

here is an example of code that I use:

Sub MarkDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String

Application.ScreenUpdating = False
On Error Resume Next
Set rAllRange = Selection
If WorksheetFunction.CountA(rAllRange) < 2 Then
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Selection.EntireColumn.Insert

Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)

If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
Set rAllRange = Union(rConstRange, rFormRange)
ElseIf Not rConstRange Is Nothing Then
Set rAllRange = rConstRange
ElseIf Not rFormRange Is Nothing Then
Set rAllRange = rFormRange
Else
MsgBox "Your selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Application.Calculation = xlCalculationManual

For Each rCell In rAllRange
strAdd = rCell.Address
strAdd = rAllRange.Find(What:=rCell, After:=rCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Address

If strAdd < rCell.Address Then
rCell.Offset(0, -1).Value = "Duplicate"
End If
Next rCell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub

In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

--

Dave Peterson

I do select the range before running the macro.

But this error pops up when I try to compile the project, do I need to
have something selected when compiling the project?

Dave Peterson wrote:
Your code expects expects you (the user) to have the range to clean up already
selected.

Do you select the range first, then invoke the macro?

wrote:

I am having a problem with my macros not wanting to run if I use
"selection" in the coding. I am using the exact same code that I had
been using in the past, i just placed it in a new add-in I am creating.
But now I keep getting errors. Is there such a thing as a macro that
will screw up "selection" in the whole workbook? Sometimes I can place
individual macros in workbooks and it will work.

here is an example of code that I use:

Sub MarkDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String

Application.ScreenUpdating = False
On Error Resume Next
Set rAllRange = Selection
If WorksheetFunction.CountA(rAllRange) < 2 Then
MsgBox "You selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Selection.EntireColumn.Insert

Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)

If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
Set rAllRange = Union(rConstRange, rFormRange)
ElseIf Not rConstRange Is Nothing Then
Set rAllRange = rConstRange
ElseIf Not rFormRange Is Nothing Then
Set rAllRange = rFormRange
Else
MsgBox "Your selection is not valid", vbInformation
On Error GoTo 0
Exit Sub
End If

Application.Calculation = xlCalculationManual

For Each rCell In rAllRange
strAdd = rCell.Address
strAdd = rAllRange.Find(What:=rCell, After:=rCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Address

If strAdd < rCell.Address Then
rCell.Offset(0, -1).Value = "Duplicate"
End If
Next rCell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub

In this particular one, the word "Selection" in "Set rAllRange =
Selection" (beginning of code) is highlighted and the error reads
"Compile error: Type Mismatch"

Any help would be greatly appreciated. I am truly stumped on this one.

--

Dave Peterson