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

[boznian]

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

 

[VoG]

Without specifics to go on it would be along the lines of

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]

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?

 

[VoG]

Make sense?

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.

 

[boznian]

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]

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

 

[boznian]

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]

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]

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]

Anything further on this? I am dead in the water on my project until I can sort this out...