Hi I have an excel spreadsheet with:
2 sheets,Sheet1 and Sheet2
4 columns (A,B,C,D)
100,000s of rows.
I need to search 3 columns, B,C and D, for particular values.
Column B needs to be = 0
Columns C and D need to be "<=0.1"
When all three criteria in the row are matched I need the value from column A in that row to be pasted to Sheet2 and then for the macro to continue searching the rows.
I have attached an extract of the data as the files size is too large to upload.
Last edited by chambone; 03-02-2009 at 10:51 AM.
Welcome to the Board.
Something along the lines of the below perhaps ?
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you very much for your help DonkeyOte, worked perfectly.
I now have a different but similar problem.
I need to search Column B for the number 4 and then copy the values in Columns G,H,I and J into sheet 2.
The attached file is just an extract of all the data. As you can see the columns increse from I to J in row 24. In the actual file this occurs in row 326198.
I dont know if it makes a difference but in the actual data the number of columns occupied by values decreses
Others may suggest Auto Filter... given the volume of data I don't know how feasible that is... I added in some Application events given that fact (re: calculation, screenupdating and events)Please Login or Register to view this content.
I get an error when I run it in the full data sheet. The start of the data sheet is the file attached previously
Last edited by chambone; 02-26-2009 at 12:27 PM.
The code I posted worked for me with your prior sample file
(I'd not have posted it otherwise)
My apologies, my sheets in the main file were listed as Sheet1 and Sheet3 now works fine thanks very much.
DonkeyOte similar problem to your last response.
I need all values selected where:
Column A=-0.1
Column B=0.2
Column B=-0.1
Column A=0 and Column B is between 0.1 and 0.2
Column A=0 and Column B is between 0 and -0.1
It does not matter about the value in Column C.
Would be great if you could help out
Re: your PM ... I'm not sure I understand the scenarios you posted previously.... are these independent processes ?
Please provide a sample with "before(s)" & "after(s)" such that I can see the logic in action.
Okay if I have the three columns,
Copy the value of column A into Sheet2 if column B=0 and or
Copy the value of column A if column C=0.2 or -0.1
I neither have the time nor inclination to create a test file for this so the below is untested, next time please provide a sample file as requested.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks