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
580
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,217,763
Messages
6,138,459
Members
450,140
Latest member
myexcel202424

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