Only 1 is chosen in a row

[DThib]

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?

 

[DanteAmor]

If you have "#N/A" (error) in the cell, use:

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:

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]

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.

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?!?

 

[DanteAmor]

What do you have in this:

[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.

 

[jim may]

What is PLB?

 

[DThib]

BPL is

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.

 

[DanteAmor]

You can answer the following:

What do you have in this:

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

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

 

[DThib]

You can answer the following:

Answered above, but here it is again

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]

You are confusing the answer
Here it says:

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

And here it says:

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:

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]

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

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.