Only 1 is chosen in a row

DThib

Active Member
Joined
Mar 19, 2010
Messages
445
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,760
Office Version
  1. 2010
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
445
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
14,760
Office Version
  1. 2010
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,484

ADVERTISEMENT

What is PLB?
 

DThib

Active Member
Joined
Mar 19, 2010
Messages
445
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
14,760
Office Version
  1. 2010
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
445
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
14,760
Office Version
  1. 2010
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
445
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,978
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top