MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dave....Help Again

Posted by Peter C on April 04, 2001 4:53 AM

You assisted me with a formula awhile ago but I need to combine your formula,


with an Offset formula to return a value 2 columns across (same row). I can't seem to nest it with the offset and returns an error message, I tried all the paranthesis, but no luck.
Also, this is to be placed in a userform text box so that the value can be typed over and edited, can you provide the code.

I realise this is quite simple, but can't seem to do it.


Posted by Dave Hawley on April 05, 2001 12:14 AM

Hi Peter

Not too sure I have understood you and if i have you may be making life too hard for yourself by using OFFSET. All you need to do is change the Column argument of the ADDRESS formula to 3:


...Or nest the Address with the VLOOKUP formula.

Regarding the TextBox bit Try this:

Private Sub UserForm_Initialize()
TextBox1 = Sheets("Sheet1").Range("F3").Formula
End Sub

Private Sub CommandButton1_Click()
Dim TboxFormula
Dim CellFormula

On Error GoTo FormulaStuffUp
TboxFormula = TextBox1
CellFormula = Sheets("Sheet1").Range("F3").Formula
Sheets("Sheet1").Range("F3").Replace CellFormula, TboxFormula

Exit Sub
If Err.Number = 1004 Then
On Error Resume Next
MsgBox "Dont you know how to edit a formula!!!!!"
End If

End Sub


OzGrid Business Applications