Solved: Excel 2003 macro help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Nic Cunliffe

Thread Starter
Joined
Sep 18, 2008
Messages
22
Hi all, I need some help please with excel 2003 vba code. I have recorder the macro which I want to look at a cell and copy the value, select the custom filter, paste the cell value into the custome filter and then sort the results in Z-A order.

The code however (generated by macro recorder is inserting the text value rather than the cell reference.

Can anyone tell me how to edit the code please?? The part number 2000801990 is shown in red - this should be the cell reference.

Thanks



Nic

Sub engineer_consumption()
'
' engineer_consumption Macro
' Macro recorded 18/09/2008 by Nic Cunliffe
'
'
Range("B1").Select
Selection.Copy
Selection.AutoFilter Field:=2, Criteria1:="=2000801990", Operator:=xlAnd
Range("C177").Select
Application.CutCopyMode = False
Range("A8:C44630").Sort Key1:=Range("C177"), Order1:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortTextAsNumbers
End Sub
 

Attachments

Joined
Oct 20, 2004
Messages
7,837
Have you tried just replacing "=2000801990" with "C177" or whatever the cell reference should be?
 

Nic Cunliffe

Thread Starter
Joined
Sep 18, 2008
Messages
22
Hi,

Thanks for your response. I have attached a sample. I did try replacing the macro generated text with the cell reference and this worked in the sense that the custom filter selection was populated but it populated with the cell reference as though it was text rather than the cell value.

Thanks
 

Nic Cunliffe

Thread Starter
Joined
Sep 18, 2008
Messages
22
Found the solution on Mr excel.com, thanks to user ZACK.

Setting criteria criT as the cell reference and then referencing this criT statement in the auto filter sub. Works like a dream!! Also learnt that switching auto filter on and off between selections is important so wrote another macro to reset the spreadsheet each time.

Thanks to all for assistance


Sub filterTry()
Application.ScreenUpdating = False
Dim criT As String
Sheets("sheet1").Select
criT = Range("b1")
'set this sheet and range to filtered criteria
Sheets("Sheet1").Select
Range("A8:c44630").Select
Selection.AutoFilter Field:=2, Criteria1:=criT, Operator:=xlAnd
Application.ScreenUpdating = True
End Sub
 
Joined
Oct 20, 2004
Messages
7,837
Good find! Zack used to be a major contributor to this board - is an Excel MVP, and a really nice guy.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top