Syntax help with variable

[GreenyMcDuff]

Hey all,

I have this macro to filter data. However, the last part hasn't been syntaxed correctly.

I have 2 questions -

Firstly, What is the correct syntax,
Secondly, Does this look like the most efficient way to carry out this process?

Selection.AutoFilter
Dim Rdate As Long
Rdate = DateAdd("d", -1, Date)
'This loop is supposed to set the date to friday if the macro is run on Sunday or Monday
Do While Weekday(Rdate, vbMonday) > 5
Rdate = Rdate - 1
Loop
Dim range_Eval8 As Range
Set range_Eval8 = Range("A1:CA1").Find("Price Change Date (Ss8-FA8)")

Range(range_Eval8, range_Eval8).Select
'Filters data for the date as specified by Rdate
ActiveSheet.Range("$A$1:$BU$14").AutoFilter Field:=range_Eval8.Column, Criteria1:=">=" & Rdate, Operator:=xlAnd

Dim Firstrow_Eval8 As Integer
Dim Lastrow_Eval8 As Integer
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible)(2).Select
Selection.End(xlDown).Select
Lastrow_Eval8 = ActiveCell.Row

'Selects first filtered row in Column 3
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible)(3).Select
Firstrow_Eval8 = ActiveCell.Row
ActiveCell.FormulaR1C1 = "YES"
Selection.AutoFill Destination:=Range("C" & Firstrow & ":C" & Lastrow), Type:=xlFillDefault

Many thanks

Chris

 

[Norie]

Chris

That honestly doesn't look as hard as it sounds.

There's a lot of repitition where you are doing the same thing but with different columns, subsets etc.

One thing you should definitely not do is try to do it all in one go, that includes creating any huge formulas.

You should start of using simple formulas, perhaps in helper columns.

I honestly wouldn't recommend autofilter especially if you could use a formula for every row that gets the same results.

 

[GreenyMcDuff]

Ok thats good to know :D looks a bit dauting at first.

You any good with Formulas??

In subsets 8&9 I have created new columns with the appropriate yes/no values in them.

What I need is a Formula to look at these columns (which may not always be in the same place) and say if there is at least 1 yes in the columns then return a value of yes.

This is what I have so far:

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R[-1]C[-2]:R[-1]C[1],""YES""),""Yes"",""NO"")"
Range("C2").Select

I have pretty much used the record formula button for this. It works if the Columns are next to each other and if the columns are always in the same place.

Any ideas??

Thx Norie

Chris

 

[Norie]

Chris

Well I'm not too good with formulas to be honest

Is the formula you've posted what you've tried so far?

Does it not work properly?

Could that be because of the fields/columns you need to work with aren't always in the same position?

Have you considered creating another dataset from the original that only includes the relevant columns?

If that's an option it wouldn't be too hard to get those columns in some sort of usable order.

 

[GreenyMcDuff]

Hey Norie,

I've managed to get all the Formula's working now :D

I have one last question (I think, but probably not :P)

With regards to the date formula you wrote for me - Sometimes there are blanks in the Price Change Date Column, The formula returns these as blanks.

Is there a simple way to tell the Formula to enter these as NO's?

This is the Code I have

'Evaluted Price Check
Dim range_Eval8 As Range
Dim range_Eval9 As Range
Dim rng As Range
Dim rng2 As Range
Dim Rdate As Date
Dim LastRow As Long
Dim New1 As Range
Dim New2 As Range


LastRow = Range("A" & Rows.Count).End(xlUp).Row


Set range_Eval8 = Range("A1:CA1").Find("Price Change Date (Ss8-FA8)")

range_Eval8.Offset(, 1).EntireColumn.Insert

Set rng = Cells(2, range_Eval8.Column + 1)

rng.Offset(-1) = "New Column 1"


Set range_Eval9 = Range("A1:CA1").Find("Price Change Date (Ss9-FB6)")
range_Eval9.Offset(, 1).EntireColumn.Insert

Set rng2 = Cells(2, range_Eval9.Column + 1)

rng2.Offset(-1) = "New Column 2"

Rdate = DateAdd("d", -1, Date)
'This loop sets the date to friday if the macro is run on Sunday or Monday
Do While Weekday(Rdate, vbMonday) > 5
Rdate = Rdate - 1
Loop

rng.Resize(LastRow - 1).Formula = "=IF(" & Cells(2, range_Eval8.Column).Address(False, True) & ">=DATEVALUE(""" & Rdate & """), ""YES"", ""NO"")"
rng2.Resize(LastRow - 1).Formula = "=IF(" & Cells(2, range_Eval9.Column).Address(False, True) & ">=DATEVALUE(""" & Rdate & """), ""YES"", ""NO"")"

Set New1 = Range("A1:CA1").Find("New Column 1").Offset(1)
Set New2 = Range("A1:CA1").Find("New Column 2").Offset(1)


Range("C2").Select
ActiveCell.Formula = _
"=IF(OR(" & New1.Address(False, False) & "=""YES""," & New2.Address(False, False) & "=""YES""),""YES"",""NO"")"

Selection.AutoFill Destination:=Range("C2:C" & LastRow)

So the last part that looks at the date and Returns a YES returns a YES if there is no date supplied. If there is no date I want the Formula to return a NO

Many Thanks Norie

Chris