Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-22-2014, 02:31 AM
jingo jingo is offline Need help sumif with variable for VBA Windows 7 32bit Need help sumif with variable for VBA Office 2010 32bit
Novice
Need help sumif with variable for VBA
 
Join Date: Jan 2014
Posts: 1
jingo is on a distinguished road
Default Need help sumif with variable for VBA

i try to use use VBA as below but not work, can someone help me to fix it.
how we can use "</ >" with variable

numyear = Range("B3").Value


tillww = Range("D2").Value

ActiveCell.FormulaR1C1 = "=SUMIFS(C[-8],C[-4],RC[-1],C[-15]," & numyear & "C[-7], ""<= "" & tillww & ")"

Thank you
Reply With Quote
  #2  
Old 01-22-2014, 04:52 PM
macropod's Avatar
macropod macropod is offline Need help sumif with variable for VBA Windows 7 32bit Need help sumif with variable for VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Three questions:
1. What is the actual formula you're trying to generate (in A1 notation)?;
2. Why are you using R1C1 notation?
3. Why are you referencing ActiveCell rather than specifying a cell address?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-23-2014, 01:46 AM
BobBridges's Avatar
BobBridges BobBridges is offline Need help sumif with variable for VBA Windows 7 64bit Need help sumif with variable for VBA Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Speaking as an R1C1 bigot, I snap back that he's using R1C1 because that's the natural and proper way for programmers to think. Don't let him talk you out of it, jingo. Bully for you!

But you didn't say what error message you get, and that's a critical part of the puzzle. You really must learn not only to tell us how (exactly) your code is failing, but to look at that message carefully yourself. It seems to me there's a fundamental problem in the statement; count the quote marks and see for yourself. That statement would never be accepted by the VBA editor. There has to be one more quote in your program somewhere, perhaps like this:
Code:
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-8],C[-4],RC[-1],C[-15]," & numyear & "C[-7], ""<= " & tillww & """)"
Paul, the translation of that R1C1 formula depends on where ActiveCell is. Pretend it's in T50; in that case
Code:
"=SUMIFS(C[-8],C[-4],RC[-1],C[-15]," & numyear & "C[-7], ""<= " & tillww & """)"

...translates to...

"=SUMIFS(L:L,P:P,S50,E:E," & numyear & "M:M, ""<= " & tillww & """)"
If numyear=14 and tillww=5 then in the worksheet it comes out
Code:
=SUMIFS(L:L,P:P,S50,E:E,14M:M, "<= 5")
That's still wrong; there needs to be a comma between "14" and "M:M". Jingo, I think you have to change your statement to
Code:
"=SUMIFS(C[-8],C[-4],RC[-1],C[-15]," & numyear & ",C[-7],""<=" & tillww & """)"
But it'd probably be even clearer if you made the references absolute:
Code:
"=SUMIFS(C12,C16,RC19,C5," & numyear & ",C13,""<=" & tillww & """)"
Also, I think Paul's third question is a good one.
Reply With Quote
  #4  
Old 01-23-2014, 03:11 AM
macropod's Avatar
macropod macropod is offline Need help sumif with variable for VBA Windows 7 32bit Need help sumif with variable for VBA Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I used to use the R1C1 notation way back in the MS Multiplan days and can still work with it quite well. The A1 notation, though, is much more readable - IMHO.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 01-23-2014, 11:02 AM
BobBridges's Avatar
BobBridges BobBridges is offline Need help sumif with variable for VBA Windows 7 64bit Need help sumif with variable for VBA Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Actually, I agree with you for the purpose of ordinary Excel formulae. It's only because I do so much with VBA that I use R1C1 most of the time; for programming, it's much easier to think in.

It does have one other advantage. When we're talking about formulae on this forum, A1 notation requires one to explain the context when using relative references. That is, if I say the formula should be =SUM(C$2:C45), I have to explain as an aside that this is correct only when entered in C46. But in R1C1 I can just say =SUM(R2C:R[-1]C) and the location doesn't matter.

But, alas!, most people don't know R1C1 so it wouldn't be helpful to use it no matter how much clearer it is.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help sumif with variable for VBA Average if and sumif jennamae Excel 4 01-17-2014 05:10 AM
Need help sumif with variable for VBA object variable or with block variable not set MJP143 Excel 1 02-11-2013 05:07 AM
Need help sumif with variable for VBA Run-time error '91': Object variable or With block variable not set tinfanide Excel Programming 2 06-10-2012 10:17 AM
Help! 'SUMIF' Formulas dave1372 Excel 2 09-08-2011 07:05 AM
Need help sumif with variable for VBA Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft