how to avoid using the offset property to retrieve data from a cell in the row but different column

[Pookiemeister]

Is there another way to obtain the values without using the offset property. This is for a production line schedule. Column H is the starting point for the refEdit. Currently I am using the refedit to retrieve these values. The only problem is it requires the user to start the selection in the same column whether there is a value there or not. Each row contains different products but the columns are same. (i.e. Column A will always contain the date, Column B will be product Code, Column C will be Description) the refEdit value unfortunately, has to start in column H in order to obtain the desired cell value but there are there are other values in column I. The problem is if there is no values in column H but there are values in column I the user will pick column I. This will pick the wrong values with offset. I know this is not the best explanation. I wish I could show a picture but I know that is not allowed.
Here's the code:

Private Sub cmdbtnDone_Click()
    
    'Declare variables
    Dim r As Range, r1 As Range
            
    'Sets variables to a range
    Set r = Range(RefEdit1)
    Set r1 = r(1)
    
    wbName = ActiveWorkbook.Name
    
    Load Chattfrm
    Chattfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
    Chattfrm.txtbxPrdctNm.Value = r1.Offset(0, -5).Value
    sPrdCde = r1.Offset(0, -6).Value
    Chattfrm.txtBxLtNum.Value = r1.Offset(0, -4).Value
    Chattfrm.txtBxShopNumber.Value = r1.Offset(0, -1).Value
    Chattfrm.txtbxdz.Value = Me.txtbxRangeTotal.Value
    Chattfrm.cmbPrdCde.Value = sPrdCde & " " & "(" & Chattfrm.txtbxPrdctNm & ")"
    Call ReName
    Unload Me
    Workbooks(wbName).Activate
    ActiveWorkbook.Close
    Call Test
    Chattfrm.Show
    Chattfrm.cmbMonth.Enabled = True
    Chattfrm.cmbMonth.Value = Format(Date, "mmmm")
    
End Sub

 

[DanteAmor]

Try this. use refedit only to select the row number.

Private Sub cmdbtnDone_Click()
    
    'Declare variables
    Dim r As Range, r1 As Range, wRow As Double
            
    'Sets variables to a range
    Set r = Range(RefEdit1)
    Set r1 = r(1)
        
    wRow = r1.Row
    
    wbName = ActiveWorkbook.Name
    
    Load Chattfrm
    Chattfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
    Chattfrm.txtbxPrdctNm.Value = Cells(wRow, "C").Value 
    sPrdCde = Cells(wRow, "B").Value 
    Chattfrm.txtBxLtNum.Value = Cells(wRow, "D").Value 
    Chattfrm.txtBxShopNumber.Value = Cells(wRow, "G").Value 
    Chattfrm.txtbxdz.Value = Me.txtbxRangeTotal.Value
    Chattfrm.cmbPrdCde.Value = sPrdCde & " " & "(" & Chattfrm.txtbxPrdctNm & ")"
    Call ReName
    Unload Me
    Workbooks(wbName).Activate
    ActiveWorkbook.Close
    Call test
    Chattfrm.Show
    Chattfrm.cmbMonth.Enabled = True
    Chattfrm.cmbMonth.Value = Format(Date, "mmmm")
    
End Sub

 

[Pookiemeister]

That worked. Thank you.

 

[DanteAmor]

You're welcome & thanks for the feedback.