Unable to inverse column selection by cell loop

Question

I am unable to inverse column selection I'm receiving error 424 Object required.

Code works fine with manual selection such as this Set MyRange = Selection

Sub Text2Numb()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim MyRange As Range
    Dim MyCell As Range
    Set MyRange = Range("A:M").Select
    For Each cell In MyRange
        If cell.Value > 0 Then
            cell.Value = -cell.Value
        Else
            cell.Value = -cell.Value
        End If
    Next cell
End Sub

Runtime Error 424 Object required

Vityata · Accepted Answer · 2019-08-22T20:58:26.353

3

This is the way to inverse the value in the cell:

cell.Value = cell.Value * -1

There is additional logic, making sure the cell is a numeric one - IsNumeric(myCell).

Sub ReverseNumbers()

    Dim myCell As Range
    Dim myRange As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Set myRange = ws.Range("A1:A5")
    For Each myCell In myRange
        If IsNumeric(myCell) Then
            If myCell.Value <> 0 Then
                myCell.Value = myCell.Value * -1
            End If
        End If
    Next myCell

End Sub

Additionally, consider avoiding ActiveSheet and Select - How to avoid using Select in Excel VBA.

edited Aug 22 '19 at 20:58

answered Aug 22 '19 at 20:31

Vityata

42,633
8
55
100

Sorry I am now getting Runtime Error 13 Type mismatch – Alvi Aug 22 '19 at 20:48
@Alvi - I have added the `IsNumeric()` logic, it should work. – Vityata Aug 22 '19 at 20:58

Unable to inverse column selection by cell loop

1 Answers1