Great Bob, this worked. I had to take out some spacing issues. but it
works~ :) The only other questions i have are, the use of STRING? this
is how we put in a formula to a cell(s)?

Since i am only selecting one cell, can i make it a range of cells
instead of using C2 or whatever. Its always going to be the cells in J2
to the last row that gets this formula.
What i have so far is:

Option Explicit

Sub Insertformula()
Dim sClient As String
Dim sTest As String

sClient = "ClientList"
sTest = "INDEX(" & sClient & ",MATCH(LEFT(RIGHT(C2,LEN(C2)-" & _
"FIND(""/"",SUBSTITUTE(C2,""-"",""/"",2),1)),5)," & _
sClient & ",0))"

Range("J2").Formula = _
"=IF(ISERROR(" & sTest & "),""Fiserv""," & Chr(10) & sTest &
")"

Range("J2").Copy Destination:=Range("J3:J7")


End Sub


How do i get the last row?, this will always change. I just selected
the destination range to test this formula.

Thanks so much for all your help

Keri




Bob Phillips wrote:
Might be wrap-around, try this

Dim sClient As String
Dim sTest As String

sClient = "Clients!$A$2:$A$296"
sTest = "INDEX(" & sClient & ",MATCH(LEFT(RIGHT(C6,LEN(C6)-" & _
"FIND("" / "",SUBSTITUTE(C6,"" - "","" / "",2),1)),5)," & _
sClient & ",0))"

ActiveCell.Formula = _
"=IF(ISERROR(" & sTest & "),""""," & Chr(10) & sTest & ")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"FurRelKT" wrote in message
ups.com...
It did go through with:
ActiveCell.Formula =
"=IF(ISERROR(INDEX(Clients!$A$2:$A$296,MATCH(LEFT( RIGHT(C6,LEN(C6)-FIND("
/ ",SUBSTITUTE(C6," - "," /

",2),1)),5),Clients!$A$2:$A$296,0))),""BLANK"",ISE RROR(INDEX(Clients!$A$2:$A
$296,MATCH(LEFT(RIGHT(C6,LEN(C6)-FIND("
/ ",SUBSTITUTE(C6," - "," / ",2),1)),5),Clients!$A$2:$A$296,0)))"

But now i have a Run-time error '13':
type mismatch





FurRelKT wrote:
Bob, I tried these ways...

'Range("J6").Formula= "=IF(ISERROR(INDEX('Clients'!$A$2:$A$296," _
"MATCH(LEFT(RIGHT(C6,LEN(C6)-FIND(" / ",SUBSTITUTE(C6," - "," /
",2),1)),5)," _


"'Clients'!$A$2:$A$296,0))),"TEST",ISSERROR(INDEX( 'Clients'!$A$2:$A$296,"
_
"MATCH(LEFT(RIGHT(C6,LEN(C6)-FIND(" / ",SUBSTITUTE(C6," - "," /
",2),1)),5)," _
"'Clients'!$A$2:$A$296,0))))"


ActiveCell.Formula =

"=IF(ISERROR(INDEX(Clients!$A$2:$A$296,MATCH(LEFT( RIGHT(C6,LEN(C6)-FIND("
/ ",SUBSTITUTE(C6," - "," / ",2),1)),5),Clients!$A$2:$A$296,0)))," _
"BLANK"," _
"ISERROR(INDEX(Clients!$A$2:$A$296,MATCH(LEFT(RIGH T(C6,LEN(C6)-FIND("
/ ",SUBSTITUTE(C6," - "," / ",2),1)),5),Clients!$A$2:$A$296,0)))"

What am i missing?

Thanks,
K~



Bob Phillips wrote:
ActiveCell.Formula = _

"=IF(ISERROR(INDEX(Clients!$A$2:$A$296,MATCH(LEFT( RIGHT(C6,LEN(C6)-" & _


"FIND(""/"",SUBSTITUTE(C6,""-"",""/"",2),1)),5),Clients!$A$2:$A$296,0))),"""
"," & _
"INDEX(Clients!$A$2:$A$296,MATCH(LEFT(RIGHT(C6,LEN (C6)-" & _


"FIND(""/"",SUBSTITUTE(C6,""-"",""/"",2),1)),5),Clients!$A$2:$A$296,0)))"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"FurRelKT" wrote in message
ups.com...
Aidan,
Column J is blank, I want to add the formula to the cells. Then in
K, i
will use the Vlookup to perform another calculation based off the
value
in J.
It's not working to record, i tried that already. Thanks for your
response. Let me know what i can do. Thanks

K~



wrote:
In what way do you want it turned into VBA - if it needs to be
entered
as a formula, record a macro then edit the cell but make no
changes -
stop the recorder - you then have the formula in R1C1 format.
FurRelKT wrote:
New to excel VBA: I have a formula in my cell range of
J2:lastRow


=IF(ISERROR(INDEX(Clients!$A$2:$A$296,MATCH(LEFT(R IGHT(C6,LEN(C6)-FIND("/",S

UBSTITUTE(C6,"-","/",2),1)),5),Clients!$A$2:$A$296,0))),"",INDEX(Clie nts!$A$

2:$A$296,MATCH(LEFT(RIGHT(C6,LEN(C6)-FIND("/",SUBSTITUTE(C6,"-","/",2),1)),5
),Clients!$A$2:$A$296,0)))

How do i turn this into VBA?

Any help would really be appreciated.

K~