How to offset a RefEdit1 Cell Value

[Pookiemeister]

Private Sub cmdbtnDone_Click()  Dim r As Range, r1 As Range
  Set r = Range(RefEdit1)
  Set r1 = r(1)
  Debug.Print r1.Address
  Debug.Print ActiveCell.Offset(0, 1).Range(r1.Address).Select
End Sub

The above code finds the first cell of the Range selection from the RefEdit. Then for test purposes only, it displays the range in the Immediate box in VBA. Which it does with the Debug.Print r1.Address. But what I need to do is to offset the r1.Address cell value, which in this case is (0 rows, and 1 column to the right) and display the new cell range with the second debug.print. All that displays is the value of "True". How can I display the new cell value of the same row and one column to the rights value? Thank You.

 

[Rick Rothstein]

Private Sub cmdbtnDone_Click()  Dim r As Range, r1 As Range
  Set r = Range(RefEdit1)
  Set r1 = r(1)
  Debug.Print r1.Address
  Debug.Print ActiveCell.Offset(0, 1).Range(r1.Address).Select
End Sub

The above code finds the first cell of the Range selection from the RefEdit. Then for test purposes only, it displays the range in the Immediate box in VBA. Which it does with the Debug.Print r1.Address. But what I need to do is to offset the r1.Address cell value, which in this case is (0 rows, and 1 column to the right) and display the new cell range with the second debug.print. All that displays is the value of "True". How can I display the new cell value of the same row and one column to the rights value? Thank You.

If I understand correctly, try the last line this way...

Debug.Print r1.Offset(0, 1).Value

 

[Norie]

Try this.

Private Sub cmdbtnDone_Click()  Dim r As Range, r1 As Range
  Set r = Range(RefEdit1)
  Set r1 = r(1)
  Debug.Print r1.Address
  Debug.Print r1.Offset(0 ,1).Address
End Sub

 

[Pookiemeister]

Thank you both for the quick response. As stated before, I used debug.print for test purposes and both your ideas worked as expected. However when I set this equal to another forms textbox it displays a "Run-time error '1004' Application-defined or object-defined error. The first line of code of

Chatfrm.txtbxdz = Me.txtbxRangeTotal.Value

works as expected. It transfered the summed value of the Range of cells to Chatfrm.txtbxdz.
Prior to this error, I received a Runtime error 424. So I set the range of r1 to the max range excel can do. This is to make sure it covers all the user's current and future entries. Below is all the code. Thank you.

Private Sub cmdbtnDone_Click()'On Error Resume Next
    Dim r1 As Range
    Set r1 = Range("A1:XFD1048576")
    Chatfrm.txtbxdz = Me.txtbxRangeTotal.Value
    Chatfrm.txtBxLtNum = r1.Offset(0, -3).Address
    Chatfrm.cmbSDPFLine = ActiveWorkbook.Name
    Select Case Chatfrm.cmbSDPFLine.Value
        Case Is = "SDPF - LINE 1 (SLAT).xlsx"
            Chatfrm.cmbSDPFLine.Value = "Slat"
        Case Is = "SDPF - LINE 2A.xlsx"
            Chatfrm.cmbSDPFLine.Value = "Uhlmann"
        Case Is = "SDPF - LINE 3.xlsx"
            Chatfrm.cmbSDPFLine.Value = "Korber"
        Case Is = "SDPF - LINE 4.xlsx"
            Chatfrm.cmbSDPFLine.Value = "IMA"
    End Select
    Unload Me
    ActiveWorkbook.Close
    Chatfrm.Show
    
End Sub

RefEdit Code Below:

Private Sub RefEdit1_Change()    On Error Resume Next
    Dim sumtxtbxRangeTotal As Long
    
    cell = Me.RefEdit1.Value
    Me.txtbxRangeTotal = ""
    Me.txtbxRangeTotal.Text = Application.WorksheetFunction.Sum(Range(cell))
    sumtxtbxRangeTotal = CLng(txtbxRangeTotal.Text)
    txtbxRangeTotal = sumtxtbxRangeTotal
    
    Debug.Print txtbxRangeTotal
End Sub

 

[Norie]

You can't offset 3 columns to the left of column A. that takes you off the worksheet.

 

[Pookiemeister]

Ok... So what you are saying is if the value I am offsetting from is in column H then there is no way to extract data from any of the columns "A through G"? And based on that formula column H now becomes a column A, respectively. Am I correct on my explanation? Also, if I am correct, is there not a way to retrieve the values from those cells?

Based on what you stated. I removed the negative sign from the number and reran the code but I am still getting the same runtime error 1004. Thank You

 

[Norie]

No, I'm saying you can't offset from column A to the left.

You can offset from column H to the left up to 7 columns to the left.

If you offset 1 column to the left of H you'll get column G, 2 columns to the left you'll get F and so on up to 7 columns to the left for column A.

 

[Pookiemeister]

Ok...great. So how can I resolve the runtime error.

 

[Norie]

Where are you getting the error?

 

[Pookiemeister]

I am sorry. I should of posted that.The runtime error occurs

Chattemfrm.txtBxLtNum = r1.Offset(0, -3).Address

It's Offsetting from Column H. If that helps. Thank you for your help.