Writing VBA macro for generating missing variable

[ryan8200]

Hello members,

I would like to write VBA macro for listing the missing variables as per below.

Among candidates A, B C, D and E. I would like to list down the missing variables (Hobbies), accompanied by the candidates name. ​

[TABLE="width: 500"]
<tbody>[TR]
[TD]Hobbies[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Swimming[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Badminton[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Volleyball[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

The expected outcome will be looked like as below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Hobbies [/TD]
[TD]Candidate[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Badminton[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Badminton [/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Volleyball[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

Volleyball​

<strike></strike>
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Swimming[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Swimming[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Volleyball[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]


Your kind assistance is greatly appreciated.

Many thanks.

Regards,
Ryan

 

[kweaver]

How am I confused here? It looks to me from the top section that Candidate A does swimming and volleyball rather than tennis and badminton?

Oh, sorry...I reread it clearly and you want what they don't have an interest in! Sorry.

 

[kweaver]

With your top table starting in A1, the results via this routine begin in A8.

Sub MissingHobbies()
Dim c As Integer, h As Integer, ans As Integer
ans = 8
For c = 2 To 6    ' candidates
  For h = 2 To 5  ' hobbies
  If Cells(h, c) = "N" Then
  Cells(ans, 1) = Cells(h, 1)
  Cells(ans, 2) = Cells(1, c)
  ans = ans + 1
  Else
End If
Next h
Next c
End
End
End Sub

Let me know.

 

[ryan8200]

Why the code is not working after I hit F5 ?

 

[kweaver]

The F5 key will only run a macro if you are in the VB Editor & the cursor is located somewhere inside a macro. Otherwise, while on the sheet, it opens the GoTo dialog box.
Just assign a keyboard shortcut to the macro if you want to run it without selecting the Developer tab and the Macros option.

 

[ryan8200]

I'm running the macro inside the VB editor. What do you mean by the cursor is located somewhere inside a macro ?

 

[kweaver]

You have to click somewhere on the macro's code while in the editor, then F5 will launch it.

 

[ryan8200]

You have to click somewhere on the macro's code while in the editor, then F5 will launch it.

I hit F5 on the VBA window while I place my cursor on any cell on the data file. I have tried on other VBA code which executing other functions. They worked.

 

[kweaver]

You're misunderstanding something. Open the VBA window, click anywhere in this macro code. Now the cursor is ON THIS MACRO. Pressing F5 will run it.
Why are you going through all that effort when you could either (a) create a keyboard shortcut to run the macro or (b) click on Macros on the Developer tab and double-click on this one?

 

[ryan8200]

You're misunderstanding something. Open the VBA window, click anywhere in this macro code. Now the cursor is ON THIS MACRO. Pressing F5 will run it.
Why are you going through all that effort when you could either (a) create a keyboard shortcut to run the macro or (b) click on Macros on the Developer tab and double-click on this one?

Hello Kweaver,

Thanks. I have placed the starting cell at B2. Now, it works. How careless were I

Now, I would like to add header "Candidate" and "Hobbies" on the output and displayed the output on new worksheet ?

Can you help me on that ?

Many thanks and sorry for occupying your precious time.