Formula in macro
 

Hello,

I am trying to write a quick macro to insert the content of a specific cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

Tom,

This places the formula in the cell. What if I want the result of the
formula instead?

Thank you.

Eric.

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific
cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

With ActiveCell
.Formula = "=Index(Client_List, Client_Selection,1)"
.Value = .Value
End With

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific
cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a
macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

Set Client_List = Range("A1") ' A1 contains something like 10
Set Client_Selection = Range("B1:B200")
ActiveCell.Value = Application.Index(Client_List, Client_Selection,1)

--
Regards,
Tom Ogilvy


"Eric" wrote in message
...
Tom,

This places the formula in the cell. What if I want the result of the
formula instead?

Thank you.

Eric.

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific
cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a
macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

Thanks Tom.

"Tom Ogilvy" wrote in message
...
Set Client_List = Range("A1") ' A1 contains something like 10
Set Client_Selection = Range("B1:B200")
ActiveCell.Value = Application.Index(Client_List, Client_Selection,1)

--
Regards,
Tom Ogilvy


"Eric" wrote in message
...
Tom,

This places the formula in the cell. What if I want the result of the
formula instead?

Thank you.

Eric.

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific
cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a
macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub

Formula in macro
 

Bob,

Thank you.


"Bob Phillips" wrote in message
...
With ActiveCell
.Formula = "=Index(Client_List, Client_Selection,1)"
.Value = .Value
End With

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Tom Ogilvy" wrote in message
...
ActiveCell.Formula = "=Index(Client_List, Client_Selection,1)"

this assumes Client_List and Client_Selection are defined names/ranges
(Insert=Name=Define)

--
Regards,
Tom Ogilvy


"Eric" wrote:

Hello,

I am trying to write a quick macro to insert the content of a specific
cell
from a 1 column array.
I am not sure of the syntax when you use an existing function in a
macro,
which probably explain
why it does not work.
Here's the quick macro

Thank you.

Eric.




Sub Macro1()
'
' Macro1 Macro


ActiveCell.FormulaR1C1 = Index(Client_List, Client_Selection)
Range("K20").Select
End Sub