Large formula to VBA if can be done?

  • Thread starter Thread starter FurRelKT
  • Start date Start date
F

FurRelKT

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(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)))

How do i turn this into VBA?

Any help would really be appreciated.

K~
 
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.
 
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~
 
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)
 
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(RIGHT(C6,LEN(C6)-FIND("
/ ",SUBSTITUTE(C6," - "," / ",2),1)),5),Clients!$A$2:$A$296,0)))"

What am i missing?

Thanks,
K~
 
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"",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)))"

But now i have a Run-time error '13':
type mismatch
 
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)
 
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
 
FurRelKT said:
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)?
Click to expand...


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.
Click to expand...

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Array Help in VBA 3
Aliases Cell formulas Convert to VBA 1
Unable to figure out VBA Code needed 3
Calculate the total overlapping time of multiple tasks, excluding non-working times 1
Complicated IF Formula 5
IF + IF? 7
How to divide numbers in Excel? 0
Nested If Function to be converted into a user-defined customfunction 1

Back
Top