Bob, you have ben so very helpful. thank you so very much, I really
appreciate it~ Since i am new to this, i am glad that this group
provides such help. Additionally, i can understand it better when
someone, such as yourself helps out. thanks again. I got it working. I
am sure to have many more questions :)


Keri~




Bob Phillips wrote:
"FurRelKT" wrote in message
oups.com...
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)?


No, it is not necessary, I just do it that way as I think it makes it
clearer and is easier to debug. If you are confident, you can plug the
values directly into the statement.


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.

Range("J2").Copy Destination:=Range(Range("J3"),Range("J3").End(xlD own))

But you can just as easily do it when setting the formula, no need to copy

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