admin管理员组

文章数量:1123506

I'm using Excel 365 and trying to take advantage of VisualBasic for this query. I'm working with two tables where if Table: EventDiary[Match] = 1 then I want to enter the [Actual] value (-30) into Table Sim.

The value will replace the cell's contents in Table: Sim (which is currently 60). The column is found via EventDiary[L] which equals 2. The row is found via EventDiary[Index] which equals 3.

How would I achieve this?

Table Name: EventDiary

L Match Index Actual
2 1 3 -30
4 0 0 0
6 0 0 0

I'm using Excel 365 and trying to take advantage of VisualBasic for this query. I'm working with two tables where if Table: EventDiary[Match] = 1 then I want to enter the [Actual] value (-30) into Table Sim.

The value will replace the cell's contents in Table: Sim (which is currently 60). The column is found via EventDiary[L] which equals 2. The row is found via EventDiary[Index] which equals 3.

How would I achieve this?

Table Name: EventDiary

L Match Index Actual
2 1 3 -30
4 0 0 0
6 0 0 0

Table Name: Sim

Index 1 2 3 4 5 6
1 51 24
2 9 15
3 12 60
  • excel
  • vba
  • match
Share Improve this question edited 18 hours ago jonrsharpe 122k30 gold badges264 silver badges472 bronze badges asked 18 hours ago topstufftopstuff 1291 silver badge9 bronze badges 2
  • Explicitely VBA is required? – Black cat Commented 18 hours ago
  • Yes because I'm overwriting the contents of a cell – topstuff Commented 18 hours ago
Add a comment  | 

1 Answer 1

Reset to default 1

This VBA code is just does what is described in the question, with static column references as of the screenshot.
Sheet1 is the sheet's name where the two tables are placed.

Sub copyrepl()
matchval = 1
Dim s1 As ListObject, t1 As ListObject
Set s1 = Sheet1.ListObjects("EventDiary")
Set t1 = Sheet1.ListObjects("Sim")
colvalue = s1.DataBodyRange.Columns(2).Find(matchval, , xlFormulas, xlWhole).Row
actvalue = Cells(colvalue, 4)
indvalue = Cells(colvalue, 3)
colvalue = Cells(colvalue, 1)
rowvalue = t1.DataBodyRange.Columns(1).Find(indvalue, , xlFormulas, xlWhole).Row
colvalue = t1.HeaderRowRange.Find(colvalue, , xlFormulas, xlWhole).Column
Cells(rowvalue, colvalue) = actvalue
End Sub

本文标签：

版权声明：本文标题：excel - If value matches x, enter new value into another table using row and column matches - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736573892a1944812.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。