VBA / Excel Find & Sort Code Question

[JazzBeatles]

Hey all--I've recently taken up learning VBA in an attempt to automate several of my company's datasheets and thus shave off countless hours of sorting through the jumbled mess.
As a C++ programmer, I know that VBA is more like a first language for some people. It's more like my 23rd language. That being said, I would like a little help on some code attemping to accomplish this task:

Search Sheet One for a match in Sheet Two; then copy a block of data in Sheet One(ie. 2 rows and 20 collumns) one collumn to the right of the matched data to Sheet Two.

Sample would be:
SHEET ONE SHEET TWO
Emp 2 2 22 222 Emp 1
B BB BBB
Emp 1 1 11 111 Emp 2
A AA AAA
Emp 3 3 33 333 Emp 3
C CC CCC

After the code runs, the outcome should be:
SHEET ONE SHEET TWO
Emp 2 2 22 222 Emp 1 1 11 111
B BB BBB A AA AAA
Emp 1 1 11 111 Emp 2 2 22 111
A AA AAA B BB BBB
Emp 3 3 33 333 Emp 3 3 33 333
C CC CCC C CC CCC

The code should sift through the information and return the matches with the data in blocks to the right of it. I've tried an Excel vlookup solution, but too many imbedded functions like offset prevented it from working properly. I'm currently trying to get the VBA Find function to accomplish this, but I just need a little push in the right direction if I'm even going about this the right way. Recording macros doesn't help since both sheets, the source and the output will be dynamic in their criteria. I've tried vlookup, match, offset etc. in Excel to no avail. I'm sure this has to be fairly simple code in VB, as at its essence I just need it to find a match then return a range of data to the right of the match.

Thank you all in advance for any help rendered. I surely appreciate it.

 

[xlbo]

the FIND function is definitely the one to use here

You will also need to loop through thye data inh sheet 2 - now, you don't say if you have a contiguous list so it is probably best to use a for i = 1 to x type loop e.g.

dim lRow as long, i as long, fWhat as string, fCell as range
[COLOR=green]'Assumes Emp1, Emp 2 etc are in column A in sheet 2[/color]
with sheets("Sheet2")
 lRow = .cells(65536,1).end(xlup).row
[COLOR=green]set up a loop to iterate from row 2 to last row of data in incements of 2[/color]
 for i = 2 to lRow step 2
  fWhat = .cells(i,1).text
  [COLOR=green]'use find function[/color]
  set fCell = sheets("Sheet1").columns("A").find(what:=fWhat,lookin:=xlvalues, lookat:=xlpart)
 [COLOR=green]'check to see a match has been made[/color]
 if not fCell is nothing then
  [COLOR=green]'do your copy here using i to determine which row to paste into in sheet1[/color]
 else
  [COLOR=green]no match found[/color]
 end if
 next i
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JazzBeatles]

Thanks for the help. It's gotten me started. I'm running into a problem actually executing the copy and paste command. I'm getting errors about needing it expecting an object when it gets to me attempting to set dataRange as startCell:endCell. What is it exactly that I'm doing wrong? Again, I'm just now trying to get the hang of the syntax. Am I even going about this portion the proper way?

 If Not fCell Is Nothing Then
 With Sheets("DATASHEET2")
 Set startCell = fCell.Offset(0, 2)
 Set endCell = fCell.Offset(1, 21)
 Set dataRange = startCell & endCell
 dataRange.Copy

 With Sheets("DATASHEET1")
 Set sCell = fWhat.Offset(0, 2)
 Set eCell = fWhat.Offset(0, 21)
 Set dRange = sCell & eCell
 dRange.Select
 Selection.Paste

If this is all wrong, how would I impliment the part after it finds a match? I need to select a block of 2 rows by 21 columns offset 4 to the right of the lookup information and copy it over 4 columns offset from the fWhat value.

Again, thanks a ton for the help. I'm getting there!

 

[xlbo]

No need to set any ranges really:

with sheets("Sheet2")
 lRow = .cells(65536,1).end(xlup).row
  for i = 2 to lRow step 2
    fWhat = .cells(i,1).text


    set fCell = sheets("Sheet1").columns("A").find(what:=fWhat,lookin:=xlvalues, lookat:=xlpart)
     if not fCell is nothing then
       [b]fCell.offset(0,4).resize(2,21).copy destination:= .range(.cells(i,5),.cells(i,5))[/b]
     else
     end if
   next i

This uses the RESIZE property to create the range to copy and the DESTINATION part of the copy function to place it in col E on sheet 2 in row "i"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JazzBeatles]

Alright, again thanks for the help! I've gotten the bulk of the main program up and running. Now for the bells and whistles. One being the implimentation of dropdown boxes so the user can choose data within a list and when the user is finished, user indicates so and the code executes.
Problem I am encountering is dropdown list manipulation. Using VB, can I have the value/text/data/contents of the selected item from the drop down list be written to a particular cell? Currently, the cell link obviously only returns the relative number value of the order the data appears on the list. And further more, can I trim down the dropdown list contents to exclude blank data from displaying?

Thanks once again for all the help. I'm honestly surprised at how helpful you all really are!

 

[xlbo]

You will need to code the putting of selected item onto worksheet using the combobox / listbox change event. In terms of excluding blanks from the list, that would depend on how you are getting the data into the drop down box and indeed which type of dropdown box (listbox / combobbox, forms toolbar / controls toolbar)....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question