 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / August 2007Macro syntax problem - Dean
I have the following formula in EXCEL (it works) =H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77) which I want to have visual basic type into the current cell. Here is my attempt at writing it in VBA but I am getting a compile error (expected end of statement). Can someone help me, please with my syntax (perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or something)? ActiveCell.FormulaR1C1 = "=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y ",R[-23]C[-6]:R[-1]C[-6])" Thanks! Dean >I have the following formula in EXCEL (it works) > [quoted text clipped - 13 lines] >Thanks! >Dean When you have quote marks within your formula, which is a string, you've got to double them so VBA doesn't think it's the end of the string: ... C[-3],""=Y"",R[-23] ... --ron Thanks, it compiles now but it is producing the wrong equation. Specifically, The H78 has apostrophes on either side of it. And the C78 has been replaced with $bz:$bz Any idea why? Thanks again Dean >>I have the following formula in EXCEL (it works) >> [quoted text clipped - 21 lines] > > --ron >Thanks, it compiles now but it is producing the wrong equation. >Specifically, [quoted text clipped - 6 lines] >Thanks again >Dean In R1C1 format, H78 is just a string, not a cell address, and C78 is the 78th column which is column BZ in A1 format. If those values are meant to be cell addresses, you need to put them in R1C1 format and not in A1 format (like the rest of your formula). --ron Ahh, I see. Thanks much. >>Thanks, it compiles now but it is producing the wrong equation. >>Specifically, [quoted text clipped - 16 lines] > rest of your formula). > --ron try ActiveCell.Formula = "=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)" or range("A1").formula ="=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"  SignatureGary > Thanks, it compiles now but it is producing the wrong equation. > Specifically, [quoted text clipped - 32 lines] >> >> --ron Oh, I like this much better than the other format I started with. I guess I grabbed whatever code that I had and tried to modify it, without thinking to ask, if I could do it this way. Silly, silly me! Now I know two ways, with Ron's help, which is good. Thanks! Dean > try > ActiveCell.Formula = [quoted text clipped - 39 lines] >>> >>> --ron Actually, I have one new problem. I put the following line of code into a macro that had been working perfectly and now it crashes at this line with an application defined or object defined error message. Am I screwing up the syntax again? From what I can tell, both cells C55 and H55 have numbers in them. Range("C200").Select ActiveCell.Formula = "=IF(H55="",0,H55-C55)" ' need to do this because the cells have moved Thanks! Dean > Oh, I like this much better than the other format I started with. I guess > I grabbed whatever code that I had and tried to modify it, without [quoted text clipped - 50 lines] >>>> >>>> --ron 2 things: you don't need to select the cell, and you need to double up the quotes, Range("C200").Formula = "=IF(H55="""",0,H55-C55)" SignatureGary > Actually, I have one new problem. I put the following line of code into a > macro that had been working perfectly and now it crashes at this line with an [quoted text clipped - 60 lines] >>>>> >>>>> --ron >Actually, I have one new problem. I put the following line of code into a >macro that had been working perfectly and now it crashes at this line with [quoted text clipped - 8 lines] >Thanks! >Dean Yes, you are screwing up the syntax again. Your problem is with the included quote marks, as I wrote previously: ...=IF(H55="""",0,... > When you have quote marks within your formula, which is a string, > you've got to > double them so VBA doesn't think it's the end of the string: --ron Ah, yes, that short term memory thing! I'm sorry. Thanks to you both for responding. Dean >>Actually, I have one new problem. I put the following line of code into a >>macro that had been working perfectly and now it crashes at this line with [quoted text clipped - 21 lines] > > --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.