If value matches x, enter new value into another table using row and column matches

Question

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
1	51	24
2	9	15
3	12	60

Explicitely VBA is required?

Black cat
– Black cat

2025-01-10 11:23:00 +00:00
Commented Jan 10 at 11:23 — Black cat
– Black cat, Commented Jan 10 at 11:23
Yes because I'm overwriting the contents of a cell

topstuff
– topstuff

2025-01-10 11:28:22 +00:00
Commented Jan 10 at 11:28 — topstuff
– topstuff, Commented Jan 10 at 11:28

Black cat · Accepted Answer · 2025-01-11 09:19:29Z

2

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")
'Set t1 = Sheet2.ListObjects("Sim")  if the Sim table is on Sheet2
colvalue = s1.DataBodyRange.Columns(2).Find(matchval, , xlFormulas, xlWhole).Row
actvalue = Sheet1.Cells(colvalue, 4)  
indvalue = Sheet1.Cells(colvalue, 3)
colvalue = Sheet1.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
'Sheet2.Cells(rowvalue, colvalue) = actvalue  if the Sim table is on Sheet2
End Sub

edited Jan 11 at 9:19

answered Jan 10 at 12:34

Black cat

7,6358 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

topstuff Jan 10 at 14:45

If Sim table was on Sheet2 instead of Sheet1, how would this code change? I've tried changing Line 5's Sheet1.ListObjects("Sim") to Sheet2.ListObjects("Sim") but it copies the value to Sheet1 still but interestingly, in the same position as it would appear on Sheet2?

Black cat Jan 10 at 18:22

Also add Sheet2. to Cells(rowvalue, colvalue) = actvalue

Collectives™ on Stack Overflow

If value matches x, enter new value into another table using row and column matches

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related