#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
||||
|
||||
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 & """)" 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 & """)" Code:
=SUMIFS(L:L,P:P,S50,E:E,14M:M, "<= 5") Code:
"=SUMIFS(C[-8],C[-4],RC[-1],C[-15]," & numyear & ",C[-7],""<=" & tillww & """)" Code:
"=SUMIFS(C12,C16,RC19,C5," & numyear & ",C13,""<=" & tillww & """)" |
#4
|
||||
|
||||
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] |
#5
|
||||
|
||||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Average if and sumif | jennamae | Excel | 4 | 01-17-2014 05:10 AM |
object variable or with block variable not set | MJP143 | Excel | 1 | 02-11-2013 05:07 AM |
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 |
Multiple criteria in SUMIF? | pumpkin head | Excel | 1 | 02-17-2006 09:06 AM |