| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large formula to VBA if can be done?
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~ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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~ |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
"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 & ")" |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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 & ")" |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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 & ")" |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula to VBA if can be done?
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 & ")" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Large formula | Excel Worksheet Functions | |||
| Formula too large | Excel Discussion (Misc queries) | |||
| Large Formula Help | Excel Discussion (Misc queries) | |||
| =large formula | Excel Discussion (Misc queries) | |||
| to large if formula | Excel Worksheet Functions | |||
Linear Mode
