Posts by noeyedeer

Re: VBA routine Autofilter on multiple cells in Excel 2003

Right, I'm going with this method now:-

http://www.rondebruin.nl/copy5_2.htm

Just need to find a way around the 'parent' part not selecting the right sheet!!

Re: VBA routine Autofilter on multiple cells in Excel 2003

Ok, I've re-arranged and don't get the error but it's not filtering anything!

ActiveSheet.AutoFilterMode = False
Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=1, Criteria1:=Range("B1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=2, Criteria1:=Range("C1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=3, Criteria1:=Range("D1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=4, Criteria1:=Range("E1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=5, Criteria1:=Range("F1").Text

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=7, Criteria1:=Range("H1").Value

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=8, Criteria1:=Range("I1").Value

Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=9, Criteria1:=Range("J1").Value

Re: VBA routine Autofilter on multiple cells in Excel 2003

Hmm, rustier than I thought!

My first attempt gives me a Runtime error '1004' and says 'AutoFilter method of Range class failed' when it gets to the line with field=2

ActiveSheet.AutoFilterMode = False
Range("B2:J2").AutoFilter
Range("B2:J2").AutoFilter Field:=1, Criteria1:=Range("B1").Text
Range("B2:J2").AutoFilter Field:=2, Criteria1:=Range("C1").Text
Range("B2:J2").AutoFilter Field:=3, Criteria1:=Range("D1").Text
Range("B2:J2").AutoFilter Field:=4, Criteria1:=Range("E1").Text
Range("B2:J2").AutoFilter Field:=5, Criteria1:=Range("F1").Text
Range("B2:J2").AutoFilter Field:=7, Criteria1:=Range("H1").Value
Range("B2:J2").AutoFilter Field:=8, Criteria1:=Range("I1").Value
Range("B2:J2").AutoFilter Field:=9, Criteria1:=Range("J1").Value

I have some drop down boxes on the front sheet of a workbook that read data from a list of references on the second sheet.

This leaves me with 8 cells of data that I then want to apply (an Autofilter I think is best?) to a 4th sheet called 'Simulator'.

Is it best to use a VBA routine for this (I think it's the only way?) and is it fastest to apply each filter separately or to send all 8 pieces of data across at once?

My Simulator sheet has around 10,000 possible combinations of the 8 pieces of data, so once the first filter is applied things speed up as a large chunk of data has been filtered out.

Sorry if I'm rambling, but haven't dabbled in VBA for about 4 years so just trying to make sure my thought process is correct.

I've taken a look at this thread, and it's similar to what I'm trying to achieve (although I should end up with only one result after all 8 peices of data have been applied to the filter):-

http://www.ozgrid.com/forum/showthread.php?t=85547&page=1

Think we answered this one in your t'other post about t' RATE function
:smash:

Sussed it, maybe I'm not as much of a drongo as I thought !! :bouncing:

=MID(AB6,1,FIND(", ",AB6,1)-1)

Morning all,

A quick one for you all. I have a column of data which lists countries and currency codes, a section of which is below.

Argentina, Pesos
Australia, Dollars
Barbados, Dollars

In the next column I would like to place a function ( LEFT maybe ??) that returns only the country name, or anything that appears before the comma.

I'm sure it's dead simple, but unfortunately so am I !!
:tongue:

I use the below macros when I need Manual calc for my worksheet.....

Sub Auto_Open()
Application.Calculation = xlCalculationManual
End Sub

and then......

Sub Auto_Close()
Application.Calculation = xlCalculationAutomatic
End Sub

Hope this helps you out a little....

Did you try writing your Rate function by selecting 'Insert' then 'Function' from the Excel menu ?

This brings up the wizard which explains all the info required to make the formula work properly.

If you have then post a few more details or an example and you'll get a few solutions for sure.

any chance of uploading your sheet as an example, I'm finding your explaination a bit hard to follow....:blah:

I have a cell that is Data/Validation linked to a column of ISO Currency Codes(eg USD, GBP), Next to the colum of currency codes I have a list of country names matched to their currency.

I am now trying to find a function(Vlookup, maybe ??) that looks at the currency selected by the user and then returns the name of the country in the adjacent cell ? :question:

Thanks Dennis.

If I use a Combo list can I use the data that the user selects or do I have to use the number returned as the result ?

For instance if the user selects USD from the list can I somehow pick up this data or do I have to work from the number that is returned as a result ? :question:

Morning all,

It's a lvoely suuny one here today, a balmy 25 and I'm off to do the Bridge Climb later.

Anyway enough of that..........

I have a sheet that the user enters payment information into andf then runs a macro which converts this to the necessary text file to import into some banking software.

I am now writing the section for Overseas payments and have a question for you.

I need the user to select the currency of the payment, which needs to be a valid ISO currency code. I have created a list of 2 columns in my sheet, 1 column hasd the ISO code(eg USD or GBP) and the other has the country(eg United States or United Kingdom).

What is the most effective way of collecting this nfo from the user ? Would it be a combo box ? :question:

Glad to be of assistance :bouncy:

Got this far, need to nip out for lunch !!

=IF(DATE(YEAR($A4),MONTH($A4),1)<=DATE(YEAR(D$3),MONTH(D$3),1) & DATE(YEAR($B4),MONTH($B4),1)&gt;DATE(YEAR($D3),MONTH($D3),1),$C4,"-")

:puzzled:

Sure I'll have a look at a formula.

Dallas was pretty fun, met some nice people there down in Greenville (I think that's what it was called, the area with loads of bars and clubs ?)

Actually Katie, I was in Texas last autumn. I have a buddy living in Dallas so we went to see the Southfork ranch !!!:coolrsvd:

And I loved Austin and Houston was fun. So I know you're not ALL hicks :coolwink:

Didn't think the Net had reached most of Texas yet !!? :coolwink:

How about formatting the destination cells ? :question:

I'm not an Aussie !!!!!!! :o2

A Pommie and proud of it :coolwink:

I'm just working in Sydney for a while. Handing back to Bangkok in 2 weeks for a few months and then back to blighty for a beautiful English summer :no: