Multi sheet macros.

[Nitecon]

Hey guys I'm new here and a DBA, and I'm trying to get an example for excel. I'm extremely new to VB so I would love to get an example to learn / work from. Here is my situation. I have 3 work sheets in my excel workbook. Main Sheet, Alias Sheet and Phone Sheet. All sheets have a common column called username. Main sheet contains almost 25 thousand rows, so I want to use a MessageBox to ask the user which column they want to search (username) and what to match it to, in this case Alias Sheet Column C(also entered in message box by the user)

So once that is done it starts a foreach loop on the main sheet, and basically copies A1 searches for A1 in Alias Sheet (C1:ENDOFCOL) If it finds a matching username it then copies MessageBox number 3's column from the Alias sheet, to Message box post location.

So to try clarify it a bit. The user will in total be asked for 4 inputs
Main Search column (A)
Alias Search Column (C)
Alias Copy Column (E)
Main Paste Column (H)

Then the macro will search every cell in Column A of the Main Sheet, and try to find a match to a row in the alias sheet's Column C. If a match is found, then Copy Column E of the Alias sheet to The Main sheet's Column H

I know this may be a bit complicated, but it will really help me out a huge amount, as it is the tip of the iceberg on what I want to use the end result of the macro for, although it's complicated it will be a great help for me to learn the scripting language as well.

For other DBA's I basically want to do a SQL Join on Table 1 col 1 where it is equal to Table 2 Col 3. And then use the result set to pate the 5th element of the query into column H.

Any and all help will be hugely appreciated.

Thanks guys!

[Bob Phillips]

[vba]

Public Sub CopyData()
Const APP_TITLE As String = "Search Macro"
Const MAIN_SHEET As String = "Main Sheet"
Dim UserName As String
Dim Alias As String
Dim RowNum As Long

UserName = InputBox("Supply the user name to search for", APP_TITLE)
If UserName <> "" Then

Alias = InputBox("Supply the alias to search for", APP_TITLE)
If Alias <> "" Then

With Worksheets(MAIN_SHEET)

On Error Resume Next
RowNum = Application.Evaluate("MATCH(1,(A1:A50000=""" & UserName & """)*(C1:C50000=""" & Alias & """),0)")
On Error GoTo 0
If RowNum > 0 Then

.Cells(RowNum, "E").Copy .Cells(RowNum, "H")
Else

MsgBox "No match found for '" & UserName & "' & '" & Alias & "'", vbOKOnly + vbInformation, APP_TITLE
End If
End With
End If
End If
End Sub
[/vba]

[Nitecon]

This is awesome thanks XLD, much appreciated. The one thing I am trying to understand here is, the popup box asks for username here. Which appears to me will be an actual cell value. What I am trying to figure out now is how do I just specify the Column, for instance Column A and have the script automatically start at A1 and go all the way down the column checking each cell value against the second sheet (alias sheet)'s column.

What I need is:
Application.Evaluate("MATCH(1,(TOP TO BOTTOM USERNAME COLUMN)*(TOP TO BOTTOM ALIAS COLUMN for each cell of USERNAME COLUMN),0)")

Now the thing that really baffles me is...

I can see how you matched / evaluated a given user name to search in all columns of the same sheet's column C. Or perhaps I am reading this wrong... What I'm party trying to find out is how I can iterate through the cells in column A of sheet 1, and evauate it again Column A in Sheet 2. I haven't seen a reference anywhere so far where someone matches stuff from one sheet to another sheet and copy and pastes it to a column on the first sheet.

[Bob Phillips]

I think an example workbook might help here.

[Nitecon]

The first image here is the main sheet:
atlprvms1.nitecon.com/extra/images/MasterSheet.PNG

The second image here is the second / alias sheet
atlprvms1.nitecon.com/extra/images/Sheet2.PNG

So with this example, the user will be presented with a messagebox that asks Which column do you want to search through, and they will select column B of the Master sheet. Then for each of teh aliases that are there the macro will look for it in sheet 2's alias column (also entered by the user) If it finds the alias in sheet 2, it pasts it in the master sheet as shown in column E. But column E should also be a request to the user what column it should be in.

I hope this makes more sense.

Thanks so much for the help.

PS: I'm still too new here to be able to link images, so I had to write the url out to the image links.

[Bob Phillips]

Just use a formula

=INDEX(Sheet2!C:C,MATCH(B2,Sheet2!D:D,0))

[Nitecon]

Haha great thanks so much xld, over thinking something never help!