Macro help needed!

utilityplayer469 · September 10th, 2009, 05:16 PM

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!

kazmone1 · September 10th, 2009, 06:16 PM

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.

Code:

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
       else
             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
       else
             i = i + 1
       end if
loop

'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.

utilityplayer469 · September 11th, 2009, 07:43 AM

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.

utilityplayer469 · September 11th, 2009, 12:58 PM

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