lookup, index, choose, match, none seem to work for this situation...

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
Been trolling through every function I can find in Excel and searching this board to come up with a way to find a value on a worksheet and return adjacent data with no luck. I think my main issue is that the data is not (and never will be) in ascending order, so that seems to eliminate most of the obvious Excel functions.

I need to create a macro that takes Make, Model, List Price etc. from worksheet "A" and copy/paste it to specific cells in worksheet "B". Easy, done. My problem is that I need the macro to then "lookup" that same model number on worksheet "C", then copy/paste more data (from adjacent columns in the same row that it found the model # in) back to worksheet "B". Can't find any way to do this on data that is not in ascending order. Surely this can be done? (Again, the copy/paste part is a no-brainer, I just need some code to use the model# from current row on sheet "A", then find that model number and return the row # from sheet "C" so I can continue the copy/paste operation)

If it helps to understand:
sheet "A" contains info about a particular inventory item "Make, Model, Price, etc.
sheet "C" contains the static info for that particular item, Gender, Size, Weight (32 columns of data)
...that all need to be combined into a table on sheet "B" at the touch of a button.

THANKS for your help
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Without specifics to go on it would be along the lines of

Code:
Dim Found As Range
Set Found = Sheets("C").Columns("A").Find(what:=modelno, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Sheets("C").Range("B" & Found.Row).Resize(, 4).Copy Destination:=Sheets("B").Range("H" & i)
 

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
OK, some more detail. Here is what it currently looks like:

' Brand Name
Sheets("A").Select
ccol = ActiveCell.Column
crow = ActiveCell.Row
Cells(crow, ccol).Activate
Selection.Copy
Sheets("B").Select
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Mfg. Model #
Sheets("A").Select
Cells(crow, ccol).Activate
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("B").Select
Range("L4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

...and on for a few more iterations.

*** Here I need to:
capture the model# from sheet "A" in the selected row;
find that model# on sheet "C" (in col B);
select that row (with the same "Cells(crow, ccol).Activate" as above??;
continue with the copy/paste from the appropriate row on sheet "C" ***

' Bracelet Color
Sheets("C").Select
Cells(crow, ccol).Activate
ActiveCell.Offset(0, 23).Select
Selection.Copy
Sheets("B").Select
Range("L12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Bracelet Weight
Sheets("C").Select
Cells(crow, ccol).Activate
ActiveCell.Offset(0, 21).Select
Selection.Copy
Sheets("B").Select
Range("L13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

...and on and on

Make sense?
 

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167

ADVERTISEMENT

Not really. There's no need for all that selecting, it makes the code hard to follow, plus code posted without code tags is very difficult to read.

As far as the selecting goes, I don't know how else to copy multiple cell values from a row on one sheet and paste those values to specific cells in a column on another sheet. In any case, that works for me, and I have many macro's using that method that I use dozens of times a day to accomplish what I need. So while I am always open to a better way of doing that, it is sort of a side issue at the moment.

For my issue in the OP, it boils down to this: Get a value from a specific cell in the current row on the current sheet, find that same exact value looking in a specific column on another sheet.

eg. Col G in the current row of the current sheet contains "Widget01", so go to the other sheet, find "Widget01" in Col B and activate that cell.

Once I get that far I am pretty sure I can do the rest on my own. I have been trying to adapt your code and code from Excel Help files to find my data, but no luck.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I already gave you that code in general, you just need to adjust the ranges to suit.
 

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167

ADVERTISEMENT

I already gave you that code in general, you just need to adjust the ranges to suit.

Sorry to be dumb, but as I said, that's what I have been trying to do with no luck. Your code as-is error's out and I have not been able to decipher exactly what it is doing. Your code seems to be looking for the hard value "modelno", which actually needs to be a variable that I can't seem to integrate into your statement. Can you briefly break some of this down for me?
Thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
No. Explain in words exactly what your code is meant to do and I'll have a go at writing it in a sensible fashion. You'll have to explain exactly what gets copied and to where. This isn't going to happen until tomorrow.
 

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
Sorry we don't seem to be communicating well on this. I don't need any help copying or pasting anything. Like I said in a previous post, all I need to do is get a value in the current row of the current sheet, go to the other sheet, find that value in Col B and activate that cell. I can take it from there.

Column "G" of Sheet "Inventory" contains the model # - example "AT56-A89"
I want to press my button to invoke a macro that will:

1) Go to Column G of the current row (I will already be on the correct sheet and row)
2) get the model #
3) go to sheet "Features"
4) look in Column "B" for the same model # (it will appear once and only once)
5) activate the cell with the model #

...and that's it. From there I can do the copying/pasting operation to populate Sheet "B"
 

boznian

Board Regular
Joined
Mar 25, 2003
Messages
167
Anything further on this? I am dead in the water on my project until I can sort this out...
 

Watch MrExcel Video

Forum statistics

Threads
1,132,993
Messages
5,656,278
Members
418,292
Latest member
spd87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top