RE: $$Excel-Macros$$ Help with formula in vba
Hi Ravinder, thanks a lot, yes this is very helpful. exactly what i needed . :) Thanks and Regards, Gargee Singh From: ravinderexcelgr...@gmail.com To: excel-macros@googlegroups.com CC: quote.ex...@gmail.com Subject: RE: $$Excel-Macros$$ Help with formula in vba Date: Sat, 7 Dec 2013 12:45:01 +0530 Pfa, may be help you. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of gargee singh Sent: Saturday, December 07, 2013 11:47 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help with formula in vba Hello everyone, Hope you all are doing well. I need some help with inserting a formula with VBA , the formula checks for a condition in the cell K8 and and goes to another workbook to vlookup , my problem is that i only need to drop down the formula until cells in Row A is not blank and that will not be constant. so sometimes we may only have 10 rows of data that until A11 and sometimes more, how do i define this in the formula. the formula is in column y. =IF(K8=AIR,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Air'!$A$2:$B$62,2,FALSE),(IF(K8=HOT,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Hotel'!$A$1:$B$88,2,FALSE),IF(K8=CAR,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Car'!$A$1:$D$59,2,FALSE),VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Others'!$A$1:$C$70,2,FALSE) Thanks. Gargee -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel
RE: $$Excel-Macros$$ Help with formula in vba
Hi Ravinder, thanks again for your help however when i use the exact formula it doesnt work, i have been tring to find the issue but couldnt find. this is the exact formula. Range(y2:y Range(a1).End(xlDown).Row).Formula = = IF(K2= AIR ,VLOOKUP(X2,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Air'!$A$2:$B$62,2,FALSE),(IF(K2= HOT ,vlookup(x2,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]H otel'!$A$1:$B$62,2,FALSE),(IF(K2= OTH ,vlookup(x2,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Others'!$A$1:$B$62,2,FALSE),IF(K2= CAR ,vlookup(x2,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Car'!$A$1:$B$62,2,FALSE),VLOOKUP(x2,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Rail'!$A$1:$B$62,2,FALSE)) Thanks and Regards, Gargee Singh From: garge...@hotmail.com To: excel-macros@googlegroups.com CC: quote.ex...@gmail.com Subject: RE: $$Excel-Macros$$ Help with formula in vba Date: Sat, 7 Dec 2013 15:28:59 +0530 Hi Ravinder, thanks a lot, yes this is very helpful. exactly what i needed . :) Thanks and Regards, Gargee Singh From: ravinderexcelgr...@gmail.com To: excel-macros@googlegroups.com CC: quote.ex...@gmail.com Subject: RE: $$Excel-Macros$$ Help with formula in vba Date: Sat, 7 Dec 2013 12:45:01 +0530 Pfa, may be help you. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of gargee singh Sent: Saturday, December 07, 2013 11:47 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help with formula in vba Hello everyone, Hope you all are doing well. I need some help with inserting a formula with VBA , the formula checks for a condition in the cell K8 and and goes to another workbook to vlookup , my problem is that i only need to drop down the formula until cells in Row A is not blank and that will not be constant. so sometimes we may only have 10 rows of data that until A11 and sometimes more, how do i define this in the formula. the formula is in column y. =IF(K8=AIR,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Air'!$A$2:$B$62,2,FALSE),(IF(K8=HOT,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Hotel'!$A$1:$B$88,2,FALSE),IF(K8=CAR,VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Car'!$A$1:$D$59,2,FALSE),VLOOKUP(X8,'C:\Users\User\Desktop\Reports\[Reason Codes.xlsx]Others'!$A$1:$C$70,2,FALSE) Thanks. Gargee -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out
Re: $$Excel-Macros$$ Help: Creating formula using VBA
Hi Dave, Much appreciation every works perfect. Hug Love On Thu, Jul 29, 2010 at 7:08 AM, Dave Bonallack davebonall...@hotmail.comwrote: Hi, In H4 enter: =IF(G4=0,,D4/G4) And similarly for other cells that contain a division operator Basically we are saying, if the denominator = 0 then nothing, otherwise, do the math. Regards - Dave. -- Date: Wed, 28 Jul 2010 21:08:00 +0300 Subject: $$Excel-Macros$$ Help: Creating formula using VBA From: janetdicks...@gmail.com To: excel-macros@googlegroups.com Hi Team, I have a challenge, how can I create a formula using VBA?! Attached file show details, but i don't want to see *#DIV/0! *error in case columns used in division does not have any data!! column E - F = column G (sometimes column E - F does not have any data, so i don't want to see 0 in column G column (G/D) = column H (sometimes column G or D does not have any data, so i don't want to see *#DIV/0! *error Jen D -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Help: Creating formula using VBA
Hi, You can use below formula in H4 cell =IF(ISERROR(D10/G10),,(D10/G10)) Thanks, Noorain Ansari -- Date: Wed, 28 Jul 2010 21:08:00 +0300 Subject: $$Excel-Macros$$ Help: Creating formula using VBA From: janetdicks...@gmail.com To: excel-macros@googlegroups.com Hi Team, I have a challenge, how can I create a formula using VBA?! Attached file show details, but i don't want to see *#DIV/0! *error in case columns used in division does not have any data!! column E - F = column G (sometimes column E - F does not have any data, so i don't want to see 0 in column G column (G/D) = column H (sometimes column G or D does not have any data, so i don't want to see *#DIV/0! *error Jen D -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe