Formula without assigning cells

Posted by Jason on February 09, 2002 9:41 AM

I was looking for some help with a formula that would trigger a response without having to assign specific cells. For example:

I type A in a cell and hit enter. A triggers B,C,D,E,F to automatically fill in with predertimed text (from a list, scenario, macro, etc.) into the 5 cells beneath and/or to the left of the starting cell.

Basically I am trying to make a simple trigger, but Excel is not setup that way and I am not sure how to do it. I will be using random cells across the spreadsheet, so I cannot assign specific cells (C2, C3, B4, B5, etc.). If anyone has any suggestions it would be greatly appreciated.

Thanks

Posted by Lodbrok on February 09, 2002 12:30 PM

Put this in the Sheet's code module :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RowText, ColText
Application.EnableEvents = False
If Selection.Cells.Count = 1 And ActiveCell.Value = "A" Then
RowText = Array("a", "b", "c", "d", "e")
ColText = Array("f", "g", "h", "i", "j")
Target.Offset(0, 1).Resize(, 5).Value = RowText
Target.Offset(1, 0).Resize(5, 1).Value = Application.Transpose(ColText)
End If
Application.EnableEvents = True
End Sub