Help with Macro with Find,Loops, and Copy/Paste

[jastro]

Hi all,

New poster here, and I was hoping to get some help with a macro for Excel 2007. I have a working understanding of programming in Java and C++, but teaching myself how to write macros in Excel is apparently beyond me. I want to write a macro that would be able to

1. Take the contents of each cell in Column K of sheet 1 and search for it in Column A of sheet 2
2. If the value is found, copy the entire row that the value is found in and paste it back to sheet 1 , starting from column L of the same row as the cell that was initially searched for.
3. If the value is not found, move on to the next cell of Column K.

for example, the program should start with finding the contents of cell K1, searching for it in the entire Column A of Sheet 2, and if it is found in, lets say, row 3, the macro will copy the entire contents of row 3 and paste it to sheet 1, L1 and beyond (depending on how many cells were in the row). Then it will move on to K2 and perform the exact same actions.


If I were to approach this in Java, I would use nested loops, with one going through every cell of Sheet 1 Column K and the other going through every cell of Sheet 2 Column A. I would then add an If/else statement, specifying that if example[x] - (x is a variable that is changed with each increment of the loop) were equal to the cell that the 2nd loop was currently on, it would then copy the entire row and paste it to sheet 1 of column L row x.

I have tried getting some background by recording a macro of me doing the basic steps for one search in excel, but when I look at the code and get to the find method, it will enter the specific value of the cell I'm trying to search and not a variable one. Ex. it will enter "XP_23414" in the find method instead of something like "Selection.paste" or something of the like, which would make the macro useful for only that specific case.

If it helps, the values that will be in Column K Sheet 1 and Column A Sheet 2 will be in this format "XP_12456" or something of the like, which would make me think of a "string" in java.


Any help is appreciated, as I am currently really lost. Taking on something like this without any prior foundation in vba was not a smart idea.

Thanks, and sorry for the wall of text
Jastro

 

[sous2817]

Hello and welcome to the board!

Here's my stab at it:

Sub Find_And_Move()
Dim lr_Sheet1 As Long, found As Long, i As Long, lc_Sheet2 As Long

'''''''''''''''''''''''
' Turns off a few things to make it run a bit faster
'''''''''''''''''''''''

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

'''''''''''''''''''''''
' find last row of sheet1 and sheet 2
'''''''''''''''''''''''

lr_Sheet1 = Sheet1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'''''''''''''''''''''''
' find last column used in sheet2
'''''''''''''''''''''''

lc_Sheet2 = Sheet2.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'''''''''''''''''''''''
' only loops through the number of rows used in sheet1
'''''''''''''''''''''''

For i = 1 To lr_Sheet1

'''''''''''''''''''''''
' Setting the default found value to 0 lets me cheat in the next step
'''''''''''''''''''''''

found = 0

'''''''''''''''''''''''
' I cheated a bit here, if it's not found an error is returned.  This skips over the error and doesn't change the default value of found
'''''''''''''''''''''''

On Error Resume Next

'''''''''''''''''''''''
'sets the value of found to the row of the matching value on sheet 2
'''''''''''''''''''''''

found = Sheet2.Cells.Find(Sheet1.Range("K" & i).Value, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'''''''''''''''''''''''
' turn error checking back on
'''''''''''''''''''''''

On Error GoTo 0

'''''''''''''''''''''''
' here's the check to make sure there wasn't an error
'''''''''''''''''''''''

    If found > 0 Then
'''''''''''''''''''''''
' copies just the row from "A" to the last column used in sheet2 to sheet1.
'''''''''''''''''''''''

        Range(Sheet2.Cells(found, 1), Sheet2.Cells(found, lc_Sheet2)).Copy Destination:=Sheet1.Range("L" & i)
    End If
Next i

'''''''''''''''''''''''
'Turns the things back on
'''''''''''''''''''''''

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[JoeMo]

Do you want to match the entire content of a K cell or just in part. For example if K1 -sheet1 is "XP_12456" and column A sheet2 has a cell that contains "system ID - XP_12456", does that count as a match? Can there be more than one match in sheet2?

 

[jastro]

Sous2817, I think that did it! It seems to work perfectly! Thanks so much for your help! Looking at the code, I don't think I could have possibly figured that out by myself.


@ JoeMo

Sorry, I should have specified. The contents of column K sheet 1 and Column A sheet 2 are in the same format and can be compared directly. There will not be more than 1 match in sheet 2. Sous2817 gave me the answer I was looking for, but thank you so much for replying and helping me with my problem!

 

[sous2817]

Hurray! Glad I could help. Best of luck with the rest of your project and feel free to post back should you need anything else.

And don't sweat the learning curve. Stick with it and ask questions, people here are almost always more than willing to help.

You were pretty close in your java solution, and you could achieve the same thing looping through both columns in VBA. If I had to guess, there is probably something similar in Java..like an array list that would have an "IndexOf" method that you could pass the strings from the first list, effectively cutting down the total number of loops you'd need to do to two (one to build the array list and one to loop through the values in column K).

Nonetheless, glad you've got it sorted out.

Happy Excel-ing!