Adv Filter Formula Criteria
 

I'm trying to access the Advanced Filter Programmatically. I have a crude
macro that runs to completion but falters on:

y.Range("C2").Formula = "=" & "OR(Source!J2<" & """""" _
& "," & "AND" & "(Source!K2<" & """""" _
& ",Source!K2<" & "" & """Consumer""" & "))" & ""

This is what ends up in the cell:
=OR(Source!#REF!<"",AND(Source!#REF!<"",Source!# REF!<"Consumer"))

It should be:
=OR(Source!J7<"",AND(Source!K7<"",Source!K7<"Co nsumer"))

How can I rewrite this so it will create a working formula in the cell? I
know criteria work because I can run the Advanced Filter successfully if I do
it manually.

--
tj

Adv Filter Formula Criteria
 

Your code worked ok for me.

Are you sure that the #ref's showed that way?

Have you checked to verify that there is a worksheet named Source in that
workbook?

And your line of code looks like it could be just:

y.Range("C2").Formula _
= "=OR(Source!J2<"""",AND(Source!K2<"""",Source!K2 <""Consumer""))"



tjtjjtjt wrote:

I'm trying to access the Advanced Filter Programmatically. I have a crude
macro that runs to completion but falters on:

y.Range("C2").Formula = "=" & "OR(Source!J2<" & """""" _
& "," & "AND" & "(Source!K2<" & """""" _
& ",Source!K2<" & "" & """Consumer""" & "))" & ""

This is what ends up in the cell:
=OR(Source!#REF!<"",AND(Source!#REF!<"",Source!# REF!<"Consumer"))

It should be:
=OR(Source!J7<"",AND(Source!K7<"",Source!K7<"Co nsumer"))

How can I rewrite this so it will create a working formula in the cell? I
know criteria work because I can run the Advanced Filter successfully if I do
it manually.

--
tj

--

Dave Peterson

Adv Filter Formula Criteria
 

The macro renames Sheet1 to Source earlier on. The code executes, but when I
look at the Formula in worksheet, it comes up #REF, and there are no filter
results on Results.

Running the same Filter typed manually, it works.

I'll try the simpler line you provided below and post back with results.


--
tj


"Dave Peterson" wrote:

Your code worked ok for me.

Are you sure that the #ref's showed that way?

Have you checked to verify that there is a worksheet named Source in that
workbook?

And your line of code looks like it could be just:

y.Range("C2").Formula _
= "=OR(Source!J2<"""",AND(Source!K2<"""",Source!K2 <""Consumer""))"



tjtjjtjt wrote:

I'm trying to access the Advanced Filter Programmatically. I have a crude
macro that runs to completion but falters on:

y.Range("C2").Formula = "=" & "OR(Source!J2<" & """""" _
& "," & "AND" & "(Source!K2<" & """""" _
& ",Source!K2<" & "" & """Consumer""" & "))" & ""

This is what ends up in the cell:
=OR(Source!#REF!<"",AND(Source!#REF!<"",Source!# REF!<"Consumer"))

It should be:
=OR(Source!J7<"",AND(Source!K7<"",Source!K7<"Co nsumer"))

How can I rewrite this so it will create a working formula in the cell? I
know criteria work because I can run the Advanced Filter successfully if I do
it manually.

--
tj

--

Dave Peterson

Adv Filter Formula Criteria
 

I'm still getting the #REF error. I'll play with it a while and post back
tomorrow.

--
tj


"Dave Peterson" wrote:

Your code worked ok for me.

Are you sure that the #ref's showed that way?

Have you checked to verify that there is a worksheet named Source in that
workbook?

And your line of code looks like it could be just:

y.Range("C2").Formula _
= "=OR(Source!J2<"""",AND(Source!K2<"""",Source!K2 <""Consumer""))"



tjtjjtjt wrote:

I'm trying to access the Advanced Filter Programmatically. I have a crude
macro that runs to completion but falters on:

y.Range("C2").Formula = "=" & "OR(Source!J2<" & """""" _
& "," & "AND" & "(Source!K2<" & """""" _
& ",Source!K2<" & "" & """Consumer""" & "))" & ""

This is what ends up in the cell:
=OR(Source!#REF!<"",AND(Source!#REF!<"",Source!# REF!<"Consumer"))

It should be:
=OR(Source!J7<"",AND(Source!K7<"",Source!K7<"Co nsumer"))

How can I rewrite this so it will create a working formula in the cell? I
know criteria work because I can run the Advanced Filter successfully if I do
it manually.

--
tj

--

Dave Peterson