match cells in 2 sheets/paste rows if found

[jmarkc]

Hi All-
I am very new to VBA. Am trying to learn it as I muddle my way through a project I'm on.

As such, part of the project includes matching cells that begin in column B (range b4 to end) from an "Output" sheet with those in column B (range b6 to b800) of the "Financial Info" sheet.The cells in "Output" can vary based upon a userform listbox - the user selects which suppliers they want to see.

Once there's a match in "Financial Info", I would like that whole row to be copied to "Output", starting in cell B4. It can replace the lookup cell on that page.

I'm stuck. I can select the range in "Output" but am unsure how to go cell by cell & match it to each cell in "Financial Info". I believe there needs to be at least one loop here, but I can't seem to figure it out. I've struggled with this for some time now...

I can e-mail the document if it helps.

 

[ColinKJ]

Hi jmarkc,

Give this a try, you need to put it in a standard module

Code:

Sub FindandCopy()
Sheets("Output").Activate
With Sheets("Financial Info")
R = Sheets("Output").Range("B65536").End(xlUp).Row
For a = 4 To R
RI = .Range("B65536").End(xlUp).Row
For x = 1 To RI
If Cells(a, 2) = .Cells(x, 2) Then
Col = .Range("IV" + Mid$(Str$(x), 2)).End(xlToLeft).Column
For z = 1 To Col
Cells(a, z) = .Cells(x, z)
Next z
GoTo nexta
End If
Next x
nexta:
Next a
End With
End Sub

Code:

You can assign the macro to a Ctrl+Key combination.

Regards

ColinKJ

 

[jmarkc]

Thanks! I'll try it out tomorrow. I appreciate the help!!

 

[jmarkc]

Thanks a bunch ColinKJ! This worked perfectly. Way above my head and level of thinking it through at this point in my novice VBA mind. The way I attempted to do the same thing was much more cumbersome.

I will attempt this myself also, but I wonder this: The spreadsheet currently has about 17 columns (A:Q), but I may not want ALL of that data. What is the best way to eliminate rows in the middle of this range (say G:H or E, G & H) when it is pasted to "Output"?

What this code basically does for me is provide summary data, so I know I won't need some of the info that's in the middle of the entire range (A:Q).

Thanks again. I am blown away at the apparent ease of this code. Wish it was that easy for me!

 

[ColinKJ]

Hi jmarkc,

You could just Hide the columns you don't want to display on the Output sheet. If you do this before you run the Macro, and save the sheet, the macro will still run and copy over all the data, just that you won't see the hiden columns.

Colin

 

[jmarkc]

You are absolutely right Colin. I had deleted the columns I didn't need instead of hiding them, so it was showing too much data. Once again, thanks for the assistance!