Re-post VBA into Formula
 

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
--
"Toppers" wrote:

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


"joesf16" wrote:


vze2mss6

--
vze2mss6

Re-post VBA into Formula
 

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,"")

"joesf16" wrote:

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
--
"Toppers" wrote:

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


"joesf16" wrote:


vze2mss6

--
vze2mss6

Re-post VBA into Formula
 

Toppers wrote...
....
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,"")

Re-post VBA into Formula
 

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) '
irow = .Match(Range("h2"), Range("C:C"), 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.

--
vze2mss6


"Harlan Grove" wrote:

Toppers wrote...
....
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,"")

Re-post VBA into Formula
 

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


"joesf16" wrote:

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) '
irow = .Match(Range("h2"), Range("C:C"), 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.

--
vze2mss6


"Harlan Grove" wrote:

Toppers wrote...
....
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,"")

Re-post VBA into Formula
 

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.

"Harlan Grove" wrote:

Toppers wrote...
....
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,"")