Dave....Help Again

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

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

=ADDRESS(MATCH("November",A1:A12,0),1,,1)

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.

Thanks

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:

=ADDRESS(MATCH("November",A1:A12,0),3,,1)

...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
FormulaStuffUp:
If Err.Number = 1004 Then
On Error Resume Next
MsgBox "Dont you know how to edit a formula!!!!!"
End If

End Sub

Dave

OzGrid Business Applications