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

 

[GreenyMcDuff]

I dont think my question was particularly clear.

All I wanted to know was correct syntax for the following code line:

Selection.AutoFill Destination:=Range("C" & Firstrow & ":C" & Lastrow), Type:=xlFillDefault

Where Firstrow and Lastrow are specified integers

Cheers

Chris

 

[Joe4]

Your formula should work fine, as long as there is a formula already in the first cell (column C, row firstrow) and that is your active cell.

If it is not working, check the values of firstrow, last row, and where your cursor is at the time it tries to invoke the AutoFill (you can use F8 to step through your code line-by-line and watch what it does each step of the way).

 

[Norie]

Chris

Isn't there a wee bit missing from the variable names?

Something like '_Eval8' perhaps?

 

[GreenyMcDuff]

lol, awwwww i'm soo embarassed. Thx guys

On a slightly related topic (now that the code works) I am having a problem with the selection it is making.

This is the code I have:

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_Eval8 & ":C" & Lastrow_Eval8), Type:=xlFillDefault
ActiveSheet.ShowAllData

However, when I copy the word YES into the filtered cells it gets copied into all the rows in between the filtered rows.

How can I avoid this happening?? I have a feeling I should use the CurrentRegion code, but I am not sure how to implement it.

Thanks again Norie :P

Chris

 

[Joe4]

Can you describe the structure of your data, and the logic for determining just how far down you want the AutoFill to go?

 

[Norie]

Chris

Can you remind us what the code is doing?

I know you did in another thread, but the nights are drawing in and so is my capacity for remembering things.

Are you still trying to do some sort of filtering?

 

[GreenyMcDuff]

Sure Joe,

When certain codes are run through our databse, they return the data which has about 80 Columns to it.

Various Columns have the date when the data was last updated - in this case the column labelled "Price Change Date (Ss8-FA8)" contains the date that I am conerned about.

(I have to run this step several times and was planning on setting up an array and a loop once I have this working - although this info may not be entirely relevant at the moment!)

When the data is filtered using the criteria Rdate, I need to return a value of YES into column C for the all the visible rows.

And then the cycle repeats.

I hope this makes sense. If I haven't explained myself clearly enough anywhere please let me know and I will endeavour to clarify.

Many thanks for your help Joe

Chris

Hope this helps explain it Norie.

 

[GreenyMcDuff]

Hi Joe,

I started another thread with this topic because I wasn't sure if it was related to the title of this one.

If you think its not do you want to close the thread?? It's up to you.

This is the title of the thread:

[URL="http://[B"]Copy Value into Visible Filtered Cells only
found here: http://www.mrexcel.com/forum/showthread.php?t=499286[/URL]

Sorry for doing this

Chris

 

[GreenyMcDuff]

Fella's does this look like it should work???

Selection.AutoFilter
Dim Rdate As Long
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

'SUBSET 8 FILTER
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_Eval8 & ":C" & Lastrow_Eval8), Type:=xlFillDefault
Range("C" & Firstrow_Eval8 & ":C" & Lastrow_Eval8).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Value = "Yes"

ActiveSheet.ShowAllData

Coz it does for me :D:D:D

Thx for help guys

Chris