range with embedded formula with quotes

[sogc]

why doesn't this code work?

Sub atest3()
ActiveCell.Value = Range("CountIfs(Day_of_Week, ""Sun"", Time, ""9:30 AM"")").Value
End Sub

I have double quoted the Sun and 9:30 AM values as the original quotes around these (which is required for the countifs formula) were causing the problem.

 

[Bong]

Assuming that this is in EXCEL:

What do you expect the range argument to look like? It's supposed to be something like [red]"a12:x16"[/red], or [red]cells(4,3),cells(6,8)[/red].

CountIf (note: not CountIf[red]s[/red]) returns a single number, so range(CountIf(...anything...)) would be an incomplete argument, assuming that you want the formula to be evaluated. Otherwise, it seems to be a pretty wierd name for a named range, unless I'm totally missing something.

_________________
Bob Rashkin

 

[AnotherHiggins]

I'm also assuming you are talking about Excel. Is that correct?

Are you trying to simply put a formula into a cell? The fact that you're doubling up quotes leads me to ask that.

Whatever you are trying to do, have you tried using the Macro Recorder? (Again, assuming that you are talking about Excel.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sogc]

Yes it is in Excel. Countifs is a valid function in Excel 2007. It allows for multiple criterae.

What I want is for the formula to be resolved and for the value to be placed in active cell.

I am not trying to enter the formula into the active cell, but rather the value of the formula itself.

For instance, I had success with a different formula but it did not have quotes embedded in the formula. See below.

Sub atest()
MyValue = Range("OFFSET(FirstRecord,1,5,1,1)").Value
ActiveCell.Value = MyValue
End Sub

 

[AnotherHiggins]

Offset returns a range.

Countifs returns a number.

So what you're saying is equivalent to
ActiveCell.Value = Range(37).Value

Range(37) doesn't make any sense.

Assuming your syntax of countifs is correct (my company won't be going to 2007 until is has been very well vetted) then try this:

ActiveCell.Value = CountIfs(Day_of_Week, "Sun", Time, "9:30 AM")

If the VBE doesn't recognize countifs, then try

ActiveCell.Value = Application.WorksheetFunction.CountIfs(...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sogc]

When I try the code

ActiveCell.Value = CountIfs(Day_of_Week, "Sun", Time, "9:30 AM")

I get the error message, "Sub or Function not defined". So I guess although 2007 has the formula, the VBA engine hasn't been updated for it yet.

I tried your last piece of code, anotherhiggens but it threw a general error.

I'm going to try to use the normal COUNTIF within a COUNTIF function to get my double criteria evaluated.

 

[AnotherHiggins]

You could always drop the value into the cell, then convert the formula to a value with something like

Selection.Value = Selection.Value

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Grr - that should have read, "You could always drop the formula into the cell..."

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sogc]

I solved my issue. Here is the code that properly executed.

Sub atest3()
ActiveCell.Value = Application.WorksheetFunction.CountIfs(Worksheets("Parameters").Range("Day_of_Week"), "Sun", Worksheets("Parameters").Range("Time"), "9:30 AM")
End Sub

I had to specify "Application.WorksheetFunction". But than likely it was the specification of the worksheets on which the ranges come from. I suspect my previous code worked because it was simply, but the more complex formula doesn't allow for shortcuts in VBA coding.

Thanks for your thoughts and assistance.