在单元格中输入数字,并使用2D范围搜索该数字的数组 [英] Enter number into a cell and search an array for that number using a 2D range
问题描述
例如:输入 -54.12
到一个单元格中,公式/ VBA会在数组中搜索该数字。
这可能会更好地解释我正在尝试实现的,尽管比编码更多的解释:
Sub Test()
Dim值As Integer
Dim Account As Range
Dim Lookup As Boolean
值=范围(D8)
帐户=范围(E:E)
设置值= A
设置帐户= B
'如果存在A在B然后查找= true
'如果查找是真的,然后滚动到单元格
End Sub
然后,我可以通过值更改设置一个Private Sub,每次输入新值时都会做同样的事情?
直接从 https://stackoverflow.com/a/19126873/1505120 (其中我忘了提及G1中的搜索字词):
直接从 MrExc el
假设数据数组在A1:D5:
在G2 : = COUNTIF(B2:D5,G1)
在G4中: = IF ROWS($ G $ 4:G4)> $ G $ 2 ,INDEX($ A $ 2:$ A $ 5中,INT(SMALL(IF($ B $ 2:$ D $ 5 = $ G $ 1,(ROW($ B $ 2:$ D $ 5)-ROW($ B $ 2)+1)* 10 ^ 5 + COLUMN($ B $ 2:$ D $ 5)列($ B $ 2)+1),ROWS($ G $ 4:G4) )/ code>
在H4中: = IF(ROWS($ G $ 4:G4) > $ G $ 2 ,INDEX($ B $ 1:$ D $ 1,MOD(SMALL(IF($ B $ 2:$ D $ 5 = $ G $ 1,(ROW($ B $ 2:$ D $ 5)-ROW ($ B $ 2)+1)* 10 ^ 5 + COLUMN($ B $ 2:$ D $ 5)列($ B $ 2)+1),ROWS($ G $ 4:G4)),10 ^ 5)))
后两者使用 Ctrl + Shift + 输入并复制。
I am reconciling accounts and was wondering if there is a formula/VBA I can use to search for certain values, as the find function doesn't work?
For example: enter -54.12
into a cell, formula/VBA searches an array for that number.
This may explain better what I'm trying to achieve, albeit more of an explanation than coding:
Sub Test()
Dim Value As Integer
Dim Account As Range
Dim Lookup As Boolean
Value = Range("D8")
Account = Range("E:E")
Set Value = A
Set Account = B
' IF A is present in B then look up = true
' if look up is true then scroll to cell
End Sub
I could then set a Private Sub by value change to do the same thing each time a new value is entered?
Lifted directly from https://stackoverflow.com/a/19126873/1505120 (where I forgot to mention the search term goes in G1):
Lifted direct from MrExcel
Assuming data array is in A1:D5:
In G2: =COUNTIF(B2:D5,G1)
In G4: =IF(ROWS($G$4:G4)>$G$2,"",INDEX($A$2:$A$5,INT(SMALL(IF($B$2:$D$5=$G$1,(ROW($B$2:$D$5)-ROW($B$2)+1)*10^5+COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS($G$4:G4))/10^5)))
In H4: =IF(ROWS($G$4:G4)>$G$2,"",INDEX($B$1:$D$1,MOD(SMALL(IF($B$2:$D$5=$G$1,(ROW($B$2:$D$5)-ROW($B$2)+1)*10^5+COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS($G$4:G4)),10^5)))
The latter two entered with Ctrl+Shift+Enter and copied down.
这篇关于在单元格中输入数字,并使用2D范围搜索该数字的数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!