Re-post VBA into Formula

[joesf16]

Hi

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.

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 is 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,

 

[Toppers]

Put this in your cells with ? ...

In H1 put required address (Relative format) e.g D6

Copy across and down

=IF(ADDRESS(CELL("row",D4),CELL("col",D4),4)=$H$1,$A$1,"")

 

[Harlan Grove]

In H1 put required address (Relative format) e.g D6

Copy across and down

=IF(ADDRESS(CELL("row",D4),CELL("col",D4),4)=$H$1,$A$1,"")

If this is going to take up an entire worksheet, any reduction in
formula length would save megabytes of on the file size. Better to
make H1 hold the address in absolute format, e.g., $D$6 and use the
formula

=IF(CELL("Address",D4)=$H$1,$A$1,"")

 

[joesf16]

Hi. Thanks so much.

I'm not sure what formula I will need in A1? I believe something that
would do this?

icol = .Match(Range("h1"), Range("3:3"), 0) '
And then this formula in h1?

=IF(CELL("Address",D4)=$H$1,$A$1,""

My understanding is that this formula in h1 will hold the whole cell
reference, so that will change the formula for won't it?

Thanks to all.

 

[Toppers]

In A1 put your value of N

in H1 put adress where you want to place N in absolute format e.g. $D$6

In D4 ,where you had the question mark(?) put

=IF(CELL("Address",D4)=$H$1,$A$1,"") (s per Harlan's reply).

Copy this formula across and down so D4 will change to E4 etc

HTH

 

[Toppers]

Harlan,
I had your formula initially but received an error when I
tried it (but must have been some obvious typo!). I retried with your posting
and of course it works.

Thanks again.