- Jan 6, 2012
- Office Version
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:
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