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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

kweaver

Well-known Member
Joined
May 8, 2018
Messages
991
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
991
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
991
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
991
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
991
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,095,185
Messages
5,442,889
Members
405,205
Latest member
Marc__

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top