Only 1 is chosen in a row

DThib

Active Member
Joined
Mar 19, 2010
Messages
464
Office Version
  1. 365
Platform
  1. Windows
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top