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?
 
It can be like this with a global statement

Code:
Option Explicit
[COLOR=#0000ff]Dim parts As String[/COLOR]


Sub Prin()


    'still working on
    Dim PL1 As Worksheet, PL2 As Worksheet
    Dim SPR_PL As String
    Dim BPL As Range
    Dim LRPL As Long
    
    SPR_PL = "19-0509" 'FrmCheckIn.SPR_ID.Value"
    
    Set PL1 = Sheets("Checkin")
    Set PL2 = Sheets("LabelQ")
    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
        
[COLOR=#0000ff]        Call Choosey(PL1, BPL)[/COLOR]
        
        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(PL1, BPL)
    If PL1.Cells(BPL.Row, 10).Value <> "N/A" Then parts = "Console": Exit Sub
    If PL1.Cells(BPL.Row, 11).Value <> "N/A" Then parts = "Bench": Exit Sub
    If PL1.Cells(BPL.Row, 12).Value <> "N/A" Then parts = "Impell": Exit Sub
    If PL1.Cells(BPL.Row, 13).Value <> "N/A" Then parts = "Board": Exit Sub
    If PL1.Cells(BPL.Row, 14).Value <> "N/A" Then parts = "Manager": Exit Sub
    If PL1.Cells(BPL.Row, 15).Value <> "N/A" Then parts = "Keyboard": Exit Sub
    If PL1.Cells(BPL.Row, 16).Value <> "N/A" Then parts = "Assembly": Exit Sub
    If PL1.Cells(BPL.Row, 17).Value <> "N/A" Then parts = "code": Exit Sub
    If PL1.Cells(BPL.Row, 18).Value <> "N/A" Then parts = PL1.Cells(BPL.Row, 18).Value
End Sub


Or with function

Code:
Option Explicit


Sub Prin()


    'still working on
    Dim PL1 As Worksheet, PL2 As Worksheet
    Dim SPR_PL As String
    Dim BPL As Range
    Dim LRPL As Long
    
    SPR_PL = "19-0509" 'FrmCheckIn.SPR_ID.Value"
    
    Set PL1 = Sheets("Checkin")
    Set PL2 = Sheets("LabelQ")
    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
        
        PL2.Cells(LRPL + 2, 3).Value = [COLOR=#0000ff]Choosey(PL1, BPL)[/COLOR]
        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


[COLOR=#0000ff]Function Choosey(PL1, BPL)[/COLOR]
    If PL1.Cells(BPL.Row, 10).Value <> "N/A" Then Choosey = "Console": Exit Function
    If PL1.Cells(BPL.Row, 11).Value <> "N/A" Then Choosey = "Bench": Exit Function
    If PL1.Cells(BPL.Row, 12).Value <> "N/A" Then Choosey = "Impell": Exit Function
    If PL1.Cells(BPL.Row, 13).Value <> "N/A" Then Choosey = "Board": Exit Function
    If PL1.Cells(BPL.Row, 14).Value <> "N/A" Then Choosey = "Manager": Exit Function
    If PL1.Cells(BPL.Row, 15).Value <> "N/A" Then Choosey = "Keyboard": Exit Function
    If PL1.Cells(BPL.Row, 16).Value <> "N/A" Then Choosey = "Assembly": Exit Function
    If PL1.Cells(BPL.Row, 17).Value <> "N/A" Then Choosey = "code": Exit Function
    If PL1.Cells(BPL.Row, 18).Value <> "N/A" Then Choosey = PL1.Cells(BPL.Row, 18).Value
End Function
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
All other parts work and place the information where I need. My big problem is how to account for the option of part ID. The interface is a group of option buttons with one text box. Which ever is chosen negates the others.
That is the variable.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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