Formulas instead of VBA

[Guest]

Hi.

Through the discussion group I got an answer to a question but it turns out
I can't use VBA. Can it be done through the use of formulas? Here is the
VBA that I have:


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_Exit:

If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False

With Application
icol = .Match(Range("h1"), Range("3:3"), 0) '<=== change h1 if required
irow = .Match(Range("h2"), Range("C:C"), 0) '<=== change h2 if required
End With
Cells(irow, icol) = Range("a1")
ws_Exit:
Application.EnableEvents = True
End Sub

 

[Guest]

The answer is no as the result of a formula is always in the cell in which
formula resides i.e. a formula cannot put an answer in another cell. A
formula in C1 cannot place a result in say F1.

The address of the "result" cell in your case is the unknown - hence the
"row,col" serach (MATCH) values in h1 & h2.

Perhaps if you explained the problem in another way, there may be an
alternative formula solution. But if it is a case of placing a number N in a
"random" cell then I believe VBA is the only way.

 

[Guest]

Hi

I have a crazy idea. Would it be possible to put a formula in all of
the remaining cells in the worksheet that could be populated with the data
from A1?

The formula would cause each cell to look for it's coordinates in h1 and
h2. If it found it it would enter the data from A1 into it's cell.

As an example I have placed question marks into some of the cells that
would contain this formula.




A B C D E F G H
1 N Row
2 Column
3 4 32 34 42 11 15
4 8 ? ? ? ? ?
5 1 ? ? ? ? ?
6 2 ? ? ? ? ?
7 9 ? ? ? ? ?
8 3 ? ? ? ? ?


Thanks much,