Only 1 is chosen in a row

DThib

Active Member
Joined
Mar 19, 2010
Messages
427
Hello,
I am writing an addition to a userform that will pull data to another worksheet from the selected row.
I have several cells in a row that are fed by the result of option buttons, all connected by a frame on the userform. I need to add to a data pull a step that will, once a row is selected by another macro, this set of 7 cells will be searched for the one cell that will not state "N/A".
How can I do that?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you have "#N/A" (error) in the cell, use:

Code:
Sub selNoNa()
    wrow = 10    'row selected by another macro
        
    For i = Columns("A").Column To Columns("G").Column
        If Not IsError(Cells(wrow, i)) Then
            MsgBox "cell that will not state N/A " & Cells(wrow, i).Address
        End If
    Next
End Sub



If you have "N/A" (text) then use:

Code:
Sub selNoNa2()
    wrow = 12    'row selected by another macro
        
    For i = Columns("A").Column To Columns("G").Column
        If Cells(wrow, i).Value <> "N/A" Then
            MsgBox "cell that will not state N/A " & Cells(wrow, i).Address
        End If
    Next
End Sub
 

DThib

Active Member
Joined
Mar 19, 2010
Messages
427
Thanks for the idea.
I am trying a Select Case to run this for an answer since it is placing this in a series of responses setting up a label.

Code:
Sub Choosey()  
  Dim Parts, Result As String
  Dim PL1 As Sheets     
   Set PL1 = Sheets("Check in")
 'is pulled from another macro 
 Set Parts = PBL   


    Select Case Parts
      Case PL1.Cells(BPL.Row, 10).Value <> "N/A"
        Parts = "Console"
      Case PL1.Cells(BPL.Row, 11).Value <> "N/A"
        Parts = "Bench"
      Case PL1.Cells(BPL.Row, 12).Value <> "N/A"
        Parts = "Desk"
      Case PL1.Cells(BPL.Row, 13).Value <> "N/A"
        Parts = "Board"
      Case PL1.Cells(BPL.Row, 14).Value <> "N/A"
        Parts = "Power & Battery Manager"
      Case PL1.Cells(BPL.Row, 15).Value <> "N/A"
        Parts = "Keyboard"
      Case PL1.Cells(BPL.Row, 16).Value <> "N/A"
        Parts = "Assembly"
      Case PL1.Cells(BPL.Row, 17).Value <> "N/A"
        Parts = "Bench2"
      Case PL1.Cells(BPL.Row, 18).Value <> "N/A"
        Parts = PL1.Cells(BPL.Row, 18).Value
      End Select


End Sub

When I run a "Compile VBAProject" to check, this throws a "Compile error: Method or data member not found".
What am I missing?!?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
What do you have in this:

Code:
[COLOR=#333333]Set Parts = PBL[/COLOR]

What do you have in the cell, you have "N/A" or "#N/A"

What do you want to review and what are you waiting for?
You can give examples.
 
Last edited:

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471

ADVERTISEMENT

What is PLB?
 

DThib

Active Member
Joined
Mar 19, 2010
Messages
427
BPL is
Code:
BPL = PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)
in another macro.

"N/A" is left if choice is false

sorry for my typo.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You can answer the following:

What do you have in this:

Code:
[COLOR=#333333]Set Parts = PBL[/COLOR]


What do you want to review and what are you waiting for?
You can give examples.
 

DThib

Active Member
Joined
Mar 19, 2010
Messages
427
You can answer the following:

Answered above, but here it is again

Code:
Set BPL = PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)
in another macro

I am trying to call to it from the select case macro nested ( by calling the macro) inside of the macro referenced above.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You are confusing the answer
Here it says:

Code:
Set Parts = [COLOR=#0000ff]PBL[/COLOR]

And here it says:
Code:
Set [COLOR=#0000ff]BPL [/COLOR]= PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)

PBL <> BPL

Assuming that the variables had the same name. Do you have the variable declared as global?

If you do not have it declared as global, then you can not use it in the macro:

Code:
Sub Choosey()  
  Dim Parts, Result As String
  Dim PL1 As Sheets     
   Set PL1 = Sheets("Check in")
 'is pulled from another macro 
 [COLOR=#ff0000]Set Parts = PBL [/COLOR]

And finally:
What do you want to review and what are you waiting for?
You can give examples.
 

DThib

Active Member
Joined
Mar 19, 2010
Messages
427
Code:
Global BPL
Sub Prin()

'still working on
    Dim PL1, PL2 As Worksheet
    Dim LRPL As Long, BPL As Range
    Dim SPR_ST As String
    
        SPR_PL = "19-0509" 'FrmCheckIn.SPR_ID.Value"
        
        Set PL1 = Sheets("Checkin")
        Set PL2 = Sheets("LabelQ")
      'If PL2.Range("A:A") = SPR_ST Then
       ' MsgBox "SPR / FI ID already present in list", vbQuestion + vbRetryCancel, "Duplicate Entry!"
       'Else
        Set BPL = PL1.Columns("F").Find(SPR_PL, LookIn:=xlValues, LookAt:=xlWhole)
        If Not BPL Is Nothing Then
        'pull information
            LRPL = PL2.Range("C" & Rows.Count).End(xlUp).Row + 1


            PL2.Cells(LRPL, 3).Value = SPR_PL
            Call Choosey
            PL2.Cells(LRPL + 2, 3).Value = Parts
            PL2.Cells(LRPL + 4, 3).Value = PL1.Cells(BPL.Row, 23).Value
            PL2.Cells(LRPL + 6, 3).Value = PL1.Cells(BPL.Row, 9).Value
            PL2.Cells(LRPL + 8, 3).Value = PL1.Cells(BPL.Row, 8).Value
         Else
          MsgBox "Data does not exist", vbRetryCancel + vbCritical, "FI not identified"
          
        End If


End Sub
Code:
Sub Choosey()  
  Dim Parts, Result As String
  'Dim PL1 As Sheets
  'Dim Cells As Range
  Dim BPL
  
   Set PL1 = Sheets("Checkin")
   'Set Parts = BPL
    Select Case Parts
      Case Is = PL1.Cells(BPL.Row, 10).Value <> "N/A"
        Parts = "Console"
      Case Is = PL1.Cells(BPL.Row, 11).Value <> "N/A"
        Parts = "Bench"
      Case Is = PL1.Cells(BPL.Row, 12).Value <> "N/A"
        Parts = "Impell"
      Case Is = PL1.Cells(BPL.Row, 13).Value <> "N/A"
        Parts = "Board"
      Case Is = PL1.Cells(BPL.Row, 14).Value <> "N/A"
        Parts = "Manager"
      Case Is = PL1.Cells(BPL.Row, 15).Value <> "N/A"
        Parts = "Keyboard"
      Case Is = PL1.Cells(BPL.Row, 16).Value <> "N/A"
        Parts = "Assembly"
      Case Is = PL1.Cells(BPL.Row, 17).Value <> "N/A"
        Parts = "code"
      Case Is = PL1.Cells(BPL.Row, 18).Value <> "N/A"
        Parts = PL1.Cells(BPL.Row, 18).Value
      End Select


End Sub

These 2 work with each other. I could be approaching this wrong, but the "PL2.Cells(LRPL + 2, 3).Value = Parts" Is the variable that is decided across 9 columns with only 1 being the answer.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,955
Messages
5,525,882
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top