Population and matrix / array

[JorgenKjer]

Hi

Is there anybody that can help with a VBA code?

I have a matrix, which must be filled in with "X"

If for example A2 matches C1, there must be an "X" in C2

The value in cells B1, C1, D1 and E1 is always the same.

The values in column A are variable and the number of rows is dynamic

I hope this makes sense

Thank you in advance for your help

Yours sincerely

Jørgen Kjer

	A​	B​	C​	D​	E​
1​		1​	2​	3​	4​
2​	2​	​	X​	​	​
3​	2​	​	X​	​	​
4​	2​	​	X​	​	​
5​	2​	​	X​	​	​
6​	4​	​	​	​	X​
7​	3​	​	​	X​	​
8​	1​	X​	​	​	​
9​	4​	​	​	​	X​
10​	2​	​	X​	​	​
11​	1​	X​	​	​	​

 

[Fluff]

Any particular reason for a macro, as this can easily be done with a simple formula.

+Fluff v2.xlsm
	A	B	C	D	E
1		1	2	3	4
2	2		X
3	2		X
4	2		X
5	2		X
6	4				X
7	3			X
8	1	X
9	4				X
10	2		X
11	1	X
Main

Cell Formulas
Range	Formula
B2:E11	B2	=IF($A2=B$1,"X","")

 

[JorgenKjer]

Thanks Fluff for your quick reply
I have created the matrix with a formula,
but to prevent someone from accidentally deleting the formula,
I feel it is safe to use a VBA code if possible.
The matrix is part of a larger project that I am trying to do with VBA coding.
However, my VBA knowledge is somewhat limited.
Regards Jørgen

 

[Fluff]

Ok, how about

Sub JorgenKjer()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   For r = 2 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(1, c) = Ary(r, 1) Then
            Ary(r, c) = "X"
         Else
            Ary(r, c) = ""
         End If
      Next c
   Next r
   Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

 

[JorgenKjer]

Hi Fluff
It's brilliant.
It works exactly as it should
Thank you

 

[Fluff]

You're welcome & thanks for the feedback.