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

boznian

Board Regular
Joined
Mar 25, 2003
Messages
161
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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
161
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
161

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
161

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
161
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
161
Anything further on this? I am dead in the water on my project until I can sort this out...
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,329
Messages
5,510,635
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top