Formula in macro

[Eric]

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

 

[Guest]

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

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

 

[Eric]

Tom,

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

Thank you.

Eric.

 

[Bob Phillips]

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]

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)

 

[Eric]

Thanks Tom.

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)

 

[Eric]

Bob,

Thank you.

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)