使用行号和列号更新单元格值，以根据所选的单元格在间接函数中使用 [英] Update a cell value with Row and Column number for use in indirect function based on which cell is selected

问题描述

我有一个类似于下面的电子表格

Hi I have a spreadsheet similar to below

当我单击一个单元格(红色单元格)时，我想将行号和列号返回到另一个单元格以用于间接查找(蓝色单元格)

Where when I click on a cell (red cell), I want to return the row and column number to another cell for use in an indirect lookup (blue cell)

理想情况下，我只想更新单元格值(如果它在设置范围内)，或者至少将其限制为仅用于工作表以进行错误处理.

Ideally I want to only update the cell value if it's within a set range or at least limit it only to that worksheet for error handling.

希望这很清楚……对Google来说并不是一件容易的事.我的

Hope that's clear... not an easy thing to google. My experiments with

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    MsgBox ActiveCell.Row
End Sub

即使宏运行正常，也什么也没返回，甚至没有消息框.有什么想法吗?

Have returned nothing, not even a message box even though macros run fine. Any ideas?

推荐答案

根据您的示例.确保您的代码在适当的工作表模块中，而不是在标准模块中，并确保Application.EnableEvents = True(您现有的代码应已执行某些操作).

Based on your example. Make sure your code is in the appropriate sheet module, not a standard module and make sure Application.EnableEvents=True (your existing code should have done something).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Intersect(Target(1), Range("C4:H9")) Is Nothing Then Exit Sub

Range("J3").Value = Cells(Target(1).Row, 2) & "," & Cells(3, Target(1).Column)

End Sub

这篇关于使用行号和列号更新单元格值，以根据所选的单元格在间接函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！