Excel VBA Make Cell Active in a Specified Range

Question

I'm not sure why the following code gives a "Not found" Message when the value in the initial active cell (-15.0) is also a value within the specified range.

Sub Test()
Dim c As Double
Dim srchRng As Range
With ActiveWorkbook.ActiveSheet
    c = ActiveCell.Select
    Set srchRng = .Range("V17:V37").Find(what:=c)
    If srchRng Is Nothing Then
        MsgBox "Not found"
    End If
End With
End Sub

Note sometimes a value of 0 exists within the specified range. If so I don't need to do anything but if 0 does not exist, I first need to find the least negative value (I use separately within the worksheet =MAX(IF(V17:V37<=0,V17:V37),MIN(V17:V37)) and this becomes my initial active cell value [eg -15.0] to look for in the specified range), and this least negative value will then be set to zero (by using a goal seek function in this cell)

So this macro will ultimately incorporate an If statement or conditional lookup

Any feedback appreciated.

I made suggested changes but revised code below still returns "Not found". Cell V38 has the value -15.0 but this number does appear in the range V17:V37. This simple macro should result in the cell in the range corresponding to the c value being selected. Any further help appreciated.

Dim c As Double
Dim srchRng As Range
With ThisWorkbook.Sheets("Reel_Pack")
c = Range("V38")
Set srchRng = .Range("V17:V37").Find(what:=c)
If srchRng Is Nothing Then
    MsgBox "Not found"
End If
End With
End Sub

I found the following code to do the above.

    Dim c As Double
    c = ThisWorkbook.Sheets("Sheet1").Range("V58").Value
    Dim cel As Range
    For Each cel In ThisWorkbook.Sheets("Sheet1").Range("V17:V57")
        With cel
            If .Value = c Then
                .Select
            End If
        End With
    Next cel
    End Sub

But can someone suggest please how to improve this by actually incorporating the equation I have in cell V58 within this macro? As mentioned that equation is =MAX(IF(V17:V37<=0,V17:V37),MIN(V17:V37)).

So the macro would look into the range V17:V37 and if there is an exact zero then Do Nothing otherwise locate the cell with the least negative number (which is what the equation above does) and select that cell.

@DarrenBartrup-Cook's point is absolutely correct, don't Select the ActiveCell. I'd go a step further and say change ActiveWorkbook.ActiveSheet to ThisWorkbook.Sheets("YourSheetName") And if your function =MAX(IF... is in a specific range (e.g. "V16"), refer to that range directly instead of using ActiveCell. — BigBen
Something to consider - are they definitely the same number? Is one -15.0 and the other -15.01. Also it's worth setting some of the other FIND parameters: LookIn:=xlValues, LookAt:=xlWhole. The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. — Darren Bartrup-Cook

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-06-27T16:08:33

I hope this is the correct reason:

c = ActiveCell.Select is returning the value -1 which equates to TRUE in VBA.

So, I think, the line is asking the question c = ActiveCell is Selected? which returns a boolean TRUE/FALSE converted to a double which equals -1.

c = ActiveCell returns the default property of the ActiveCell which is Value.

So that line should read c = ActiveCell or c = ActiveCell.Value rather than c = ActiveCell.Select.

Edit:
Not so sure of my reasoning now - c= Range("A1").Select selects cell A1 and returns -1 still.
I guess that why you should avoid Select like the plague unless you want to actually select a cell rather than just reference it. Have a read of How to avoid using Select in Excel VBA

Excel VBA Make Cell Active in a Specified Range

1 Answers