我有一个电子表格,需要帮助完成。我将在下面包含一个示例来简化我的要求。
我有一个动态单元格。单元格 B1。在单元格 B1 中,宏将粘贴一个变量。该变量可以是任何范围的答案。但对于我们的示例,假设 B1 获取"香蕉"的值。
如何编写一个动态查找宏,该宏仅从我们的示例中选择单元格 A3,但也能够查找每次运行宏时粘贴到那里的任何变量?
表 A
A1: 苹果
A2:橙色
A3: 香蕉
A4: 草莓
A5: 等等。
更新:对于那些困惑的人,我问什么。我将粘贴我的完整宏和我尝试过的内容。我对 VBA 很陌生。
Sub HardCodeTicketDetails()
Range("OO3:PQ3").Select
Selection.Copy
Sheets("Onboarding Ticket Details").Select
Range("BA2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'I dont want the value in BA1 to be hardcoded
Range("bb2:cc2").Select
Selection.Copy
'The following did not work: I want it to look up in column A in the_
'"Onboarding Ticket Details" sheet what was pasted in cell BA2 and just_
'select the matching cell in column A.
cStart = Sheets("Onboarding Ticket Details").Cells(2, "ba")
Columns("A:A").Select
Set cell = Selection.Find(What:=cStart, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If cell Is Nothing Then
MsgBox ("Error: Does not exist in Ticket Details")
Else
MsgBox ("This ticket has been hard coded, you may delete or
hide the tab.")
End If
End Sub
将此代码放入工作表的私有代码表中。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect(Target, range("B1")) is nothing Then
if not iserror(application.match(range("B1").value, columns(1), 0)) then
cells(application.match(range("B1").value, columns(1), 0), "A").select
end if
End If
End Sub