Writing VBA macro for generating missing variable

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
299
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.

HobbiesABCDE
SwimmingYYYNN
TennisNYYYN
BadmintonNNYYY
VolleyballYNNYN

<tbody>
</tbody>

The expected outcome will be looked like as below:


Hobbies Candidate
TennisA
BadmintonA
Badminton B
VolleyballB
Volleyball
<strike></strike>
C
SwimmingD
SwimmingE
TennisE
VolleyballE

<tbody>
</tbody>


Your kind assistance is greatly appreciated.

Many thanks.

Regards,
Ryan
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,041
Office Version
365, 2010
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.
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,041
Office Version
365, 2010
With your top table starting in A1, the results via this routine begin in A8.

Code:
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.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,041
Office Version
365, 2010
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

Active Member
Joined
Aug 21, 2011
Messages
299
I'm running the macro inside the VB editor. What do you mean by the cursor is located somewhere inside a macro ?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,041
Office Version
365, 2010
You have to click somewhere on the macro's code while in the editor, then F5 will launch it.
 

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
299
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

Well-known Member
Joined
May 8, 2018
Messages
1,041
Office Version
365, 2010
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?
 
Last edited:

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
299
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,136
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top