Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 10th, 2009, 05:16 PM
Authorized User
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy Macro help needed!

Hey! I'm looking for a macro that search a list on one page and return all the values that meet two conditions like the following:

Type Phase Description
Pipeline L Mixing Point
Exchanger V/L 04MC-102 Shellside Inlet
Other V 04MC-102 Shellside Outlet

Pipeline is in A6, L is in B6, and Mixing Point is in C6.

I have a whole list of values and I need a macro to search through this table for all Descriptions that have a certain Type and Phase and then return all the descriptions that meet this criteria in a new table on the next worksheet in the same file.

Can anyone PLEASE help!!! Thank you so much!
Old September 10th, 2009, 06:16 PM
Registered User
Join Date: Sep 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Can you provide a little more information?

From what I understand it looks like you would be able to do a simple loop since it might be more complex using excel formulas.

Public Sub Test (criteria as string)

Dim i as integer, j as integer
Dim blanks as integer
Dim sht as worksheet 
Dim resultSht as worksheet
Dim results() as string

set sht = thisworkbook.sheets("SheetWithDataToSearch")
set resultSht = thisworkbook.sheets("SheetWhereDataWillBeReturned")
i = i + 1

Do while true
       if sht.range("A" & i).value = "" Then
             blanks = blanks + 1
             blanks = 0
             if strcomp(sht.range("A" & i).value,criteria,vbtextcompare)=0 then
                 redim preserve results(j)
                 results(j) = sht.range("A" & i).value
                 j = j + 1
             end if
       end if

       if blanks >= 5 then
             exit do
             i = i + 1
       end if

'clear out old results
resultsht.usedrange.value = ""
'paste new results
resultSht.range("A1").value = results()

set sht = nothing
set resultsht = nothing

end sub
Not perfect, but should work.
If you are trying to do pattern matching you are going to need to reference to RegExp similar to JavaScript. You can get more complex if you want, but that is the basic jist.

Last edited by kazmone1; September 10th, 2009 at 06:19 PM..
Old September 11th, 2009, 07:43 AM
Authorized User
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts

I have tried to copy and paste and paste screen shots but I can't get any of that to work.

I'm very unfamiliar with the VB program and it has been a long time since I worked with coding so I'm not really sure I understand how to get the code you provided to actually search the sheet I want it to and to paste it in the desired location.

On the sheet I want it to search, tab titled "Pressure Profile", I need it to return all descriptions (column C)that are both pipeline and L. I have created drop down lists in columns A (Type) and B (Phase) for the user to pull up the desired type and phase. But I only need certain the descriptions that match the pipeline and L to be moved on to the next table.

The new table needs to be created on tab titled "Settle-out Calculation" and needs to be a list of the descriptions found in the first table. This list should be returned in a column starting at B5.

Thanks for all of your help, I really appreciate it.
Old September 11th, 2009, 12:58 PM
Authorized User
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts

Hey! Thanks for your help. I figured it out. Have a great weekend!

Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Macro Doesn't Run? CoryKutchera Excel VBA 3 January 5th, 2005 10:30 AM
Macro Help! Jolin VB How-To 0 April 22nd, 2004 08:38 AM
macro - loop [email protected] Access 1 October 20th, 2003 09:29 AM
Macro KennethMungwira Access 1 June 13th, 2003 12:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.