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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kweaver

Well-known Member
Joined
May 8, 2018
Messages
927
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
927
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
927
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
927
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
927
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.
 

Forum statistics

Threads
1,089,584
Messages
5,409,145
Members
403,252
Latest member
iscoupon01

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top