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

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
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:
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this. use refedit only to select the row number.

Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,654
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top