Syntax help with variable

G

GreenyMcDuff

Active Member
Joined
Sep 20, 2010
Messages
313
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
Click to expand...

Many thanks

Chris
 
Sort by date Sort by votes
Yes, please be sure not to post the same question multiple times. Per forum rules, posts of a duplicate nature are typically locked or deleted (see: http://www.mrexcel.com/forum/showthread.php?t=127080). Things can get very messy and confusing if the same conversation is going on in two different threads.

I added a link to your previous post pointing to the new question, and I will "lock" this one so people know to reply there.
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I see that you may have found the answer. In that case, I will leave this thread open and lock the other one.
 
Upvote 0
Chris

How about forgetting about filtering and just using a formula?

You could easily create a formula for every row of data in a spare/helper column that returns YES (or whatever) based on the same criteria you are filtering with.

The only thing might be getting the address for the function, since it appears that the column you want to check isn't the same every time.

Something like this perhaps.
Rich (BB code): 
Dim range_Eval8 As Range
Dim rng As Range
Dim LastRow As Long
Dim Rdate As Date
 
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) = "NewColumn"
 
Rdate = Date
 
rng.Resize(LastRow - 1).Formula = "=IF(" & Cells(2, range_Eval8.Column).Address(False, True) & ">=DATEVALUE(""" & Rdate & """), ""YES"", ""NO"")"

By the way if you want to avoid the problem you had with the variable names put Option Explicit at the top of the module.

That won't fix it but you'll definitely be told about any errors.:)
 
Upvote 0
Lol will do that Option Explicit thing from now - will certainly save me from future embarassment :P

Hmmm I like you outside of box thinking....


I tried that code - is there anything I have to do with it to make it fit my data?

I ran it with no errors - but it did nothing to my spreadsheet????

Just so I can be sure about what the code is doing:

1. It counts the number of rows in column A and sets this as Lastrow
2. Sets range_Eval8 to be that Price Change Date Column
3. Inserts a dummy column after it
4. I get confused
5. Could you please explain the rest of it to me please :)

thx

Chris
 
Last edited:
Upvote 0
Chris

That's what it should do anyway.

I don't think you've missed anything.

Try stepping through the code with F8, perhaps sprinkle a few breakpoints about (F9) - not too many though.:)

As you step through check out what's happening on the worksheet.

If you already have a worksheet you've run the code on you could quickly check if the new column was created and has been filled with formulas.

If it has there could be a problem with the formula.:)

PS I hope you are doing all this on a copy of the data.
 
Upvote 0
Lol yeah the data is just a load of outdated crap I downloaded from our database and I have a hard copy of everything that works elsewhere.


Ah ha it is working ... I wasn't looking at the correct column - so much for not embarassing myself again.

Would it be possible to just put the YES/NO values in to the column labelled "Evaluated Price Available?" by copying and pasting them? or can the code be manipulated to just write the formula into that column

(I suspect the latter is true - but my vba brain is not big enough to comprehend how yet)

Chris
 
Last edited:
Upvote 0
Chris

Does this Evaluated... column exist and is empty?

Do you need to find it too?

Whatever, it should be pretty straightforward - probably just need to change column references.

Oh, and add a copy/paste special if you only want the values.

By the way, now that you've mentioned it, you say the data is from a database.

Is there no chance you could query that and import the results of the query?

If you could you probably wouldn't need this code.:)
 
Upvote 0
Lol you would think so - however, I am not senior enough at our company to speak to our developers yet. I am hoping that once I have this macro working I will be given some leeway to do what you have suggested - but for now I am afraid I have to do it this way.

Theres about 20 different options that the user will be able to configure when the macro is eventually complete. (i.e. leaving certain columns in or out and which dates they want to corss reference)

Essentially, I have been told it is not possible because of the sheer number of variables there are when manipulating the data (but I don't believe this).

This macro is supposed to make manipulating the data easier and faster - but it won't fully automate the process for 2 reasons:

1. our clients could want the data in a unique format.
2. I do not have access to customise the format of the download from our database.

Back to the first questions you asked...

Yes, Evaluated Price Available is a blank column

No, it is always in the same place.. Column C

Hope this has helped explain my situation a bit better :)

Chris

Oh one more thing - the reason I was using the AutoFilter was because this process has to be carried out up to 7 times on this sheet, 3 times on a different sheet and 4 times on another one.

I figured i could set up some sort of Array with a For loop if I was using the AutoFilter - would the same be true for the formula???
 
Last edited:
Upvote 0
Chris

The same filter needs to be done multiple times on not only one sheet but others too?:eek:

Or, even worse, multiple different filters to multiple sheets. double:eek:

What kind of set up have you got here?

If you do need a loop of some sort then it's probably possible, depending on what you are looping and what should happend on each iteration.

eg do you want to apply the same filter to all worksheets? That could be done by looping through the worksheets.

Anyway, if the column you want to put the formula in is fixed try this.
Code: 
Dim range_Eval8 As Range
Dim rng As Range
Dim LastRow As Long
Dim Rdate As Date
 
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 = Range("C2") 
 
Rdate = Date
 
rng.Resize(LastRow - 1).Formula = "=IF(" & Cells(2, range_Eval8.Column).Address(False, True) & ">=DATEVALUE(""" & Rdate & """), ""YES"", ""NO"")"
Give that a try.:)

Oh, and perhaps point out to someone Excel isn't really the best tool for this sort of thing.

You can do it and if it's all you've got I suppose your stuck.
 
Upvote 0
Norie,

That works a treat,

I'll try and explain my set up but please bear in mind 2 things:

1. The explanation will be quite lengthy so I may miss out some points, for which I apologise
2. This macro is only designed to be a starting point, if I can get it working properly then hopefully I will be able to expand the project beyond the scope of excel.

Here goes:

1. The data is downloaded from the data base and opened in excel
2. Basic formating occurs (Header row is changed differen colour etc....)
3. One columns tells the user if the data is LIVE or EXTINCT. EXTINCT data is copied into a new sheet and delted from the current sheet.
4. A formula is written to determine the type of data based on the values in one of the columns (can be either Fixed Income, Non Fixed Income, Funds or Other)
5. The data is sorted into sheets accordingly
6. The data has 15 Subsets which contain information relating to Price Change Dates.
7. In the Fixed Income Sheet If the Price Change Date in Subset 8 is no more than a day old, a value of YES is returned into Evaluated Price column.
8. In the Fixed Income Sheet If the Price Change Data in Subset 9 is no more than a day old, a value of YES is returned into the Evaluated Price column.
9. In the Fixed Income Sheet the column "Exchange of Quotation (Ss4-FS17)" needs to be filtered to XTRAKTER then if the Price Change Data in Subset 4 is no more than a month old a value of YES is returned into the XTRAKTER Price Column
10. In the Fixed Income Sheet the column "Exchange of Quotation (Ss5-FS25)" needs to be filtered to XTRAKTER then if the Price Change Data in Subset 5 is no more than a month old a value of YES is returned into the XTRAKTER Price Column
11. In the Fixed Income Sheet the column "Exchange of Quotation (Ss1-FS3)" needs to be filtered to everything except XTRAKTER then if the Price Change Data in Subset 1 is no more than a month old a value of YES is returned into the Exchange Price Column
12. In the Fixed Income Sheet the column "Exchange of Quotation (Ss2-GA1)" needs to be filtered to everything except XTRAKTER then if the Price Change Data in Subset 2 is no more than a month old a value of YES is returned into the Exchange Price Column
13. In the Fixed Income Sheet the column "Exchange of Quotation (Ss3-FS9)" needs to be filtered to everything except XTRAKTER then if the Price Change Data in Subset 3 is no more than a month old a value of YES is returned into the Exchange Price Column
14. All the blank cells in Evaluated Price, XTRAKTER Price, Exchange Price columns must be set to NO

Thats everything for the Fixed Income Sheet. I'll leave the Fund and Non Fixed Income sheets as they all work on pretty much the same premise (they just relate to different sub sets).

If we could get that working I think I would do a cartwheel.

Everything up to point 6 works perfectly - as you can see I am having trouble coming up with an efficient way to return the YES/NO values into the appropriate columns.

I accept that this is a lot of work. If you take a look at it and just end up going **** this could you be so kind as to give me your opinion on which method would be more efficient (the AutoFilter method, or the Formula method). As I have to get this done one way or another.

Thanks for looking Norie

Chris
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Scraping a table from a website, VBA code needs to be updated, running into issues
Replies
1
Views
334
Dan_W
D
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
395
Alex Blakenburg
A
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
155
sspatriots
S
K
  • Question
Help with VB Code to copy workbook
Replies
0
Views
189
kizzie37
K
S
  • Solved
Run-time error '1004': No cells were found.
Replies
4
Views
585
sspatriots
S

Forum statistics

Threads
1,215,341
Messages
6,124,391
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top