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("/",SUBSTITUTE(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~

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("/",SUBSTITUTE(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~

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("/",SUBSTITUTE(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~

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~

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~

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~