CODEWITHSUNDEEP
vbaexcelcoordinates
Schalton
Schalton

Reputation: 3104

Selecting a Cell by it's position (Left,Top)

I'm creating a sales channel map and use the .Left/.Top w/ + (.5*.width/.Height) to get the center of the images I'm connecting. I'd like to also use this method to select the cell that corresponds to this coordinate.

The only solution I can think of (and could implement, but I'd rather avoid an iterative approach) would be something like:

Sub FindCellLoc(DesiredYLocation,DesiredXLocation)
'Finds the Column Number of the X coordinate
RunningTotalX = 0
For X = 1 to 100000000
    RunningTotalX = RunningTotalX + Cells(1,X).width
    if RunningTotalX >= DesiredXLocation then
        TargetCol = Cells(1,X).Column
        Goto FoundCol
    End if
Next X
FoundCol:
'Finds the Column Number of the X coordinate
RunningTotalY = 0
For Y = 1 to 100000000
    RunningTotalY = RunningTotalY + Cells(Y,1).width
    if RunningTotalY >= DesiredYLocation then
        TargetRow = Cells(Y,0).Column
        Goto FoundRow
    End if
Next Y
FoundRow
Cells(TargetRow,TargetCol).Select
End Sub

I'd really appreciate any input about a non-iterative approach.

Thanks, -E

Upvotes: 2

Views: 4703

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

Here is a routine to select a cell based on the x and y position:

Public Sub SelectCellByPos(x, y)
    With ActiveSheet.Shapes.AddLine(x, y, x, y)
        .TopLeftCell.Select
        .Delete
    End With
End Sub

Upvotes: 4

Dick Kusleika
Dick Kusleika

Reputation: 33165

I assume you have access to the shape object from which you got the desired locations. If so, you could do something like

Function GetCenterCell(shp As Shape) As Range

    Dim lRow As Long, lCol As Long

    lRow = (shp.TopLeftCell.Row + shp.BottomRightCell.Row) \ 2
    lCol = (shp.TopLeftCell.Column + shp.BottomRightCell.Column) \ 2

    Set GetCenterCell = shp.Parent.Cells(lRow, lCol)

End Function

Sub test()

    Dim shp As Shape

    Set shp = Sheet1.Shapes(1)

    Debug.Print GetCenterCell(shp).Address

End Sub

That won't give you the exact middle if there isn't an exact middle. It will skew top and left as the integer division truncates (I think). But using the TopLeftCell and BottomLeftCell properties will be far superior to iterating, even if it means you're iterating through the cells in that range or some other implementation.

Upvotes: 2

Related Questions