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

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
529
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,760
Office Version
  1. 2010
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
14,760
Office Version
  1. 2010
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,099
Latest member
rdhoy

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
Top