• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

the grass is greener? (looking over the fence)

Lawrence Dodge

Lawrence Dodge

Member
Hi all--

My colleague thinks I am an Excel genius (he just doesn't know I am a member of Chandoo.org :) ) He asked me for help on this scenario.

He has a large spreadsheet with lots of inventory data. When the actual count is outside a certain range compared to the projected count (plus or minus), the spreadsheet populates a cell (AB ##) within the spreadsheet with a text entry (letters and numbers). So far, so good. Now is the problem, he currently takes that populated value to another workbook and does a find to find that item. Then he copies and pastes the next 6 horizontal cells from the second workbook to the first workbook. He may have to do this once or 20 times, sounds like fun I know, but it does get a bit tedious.

In a nutshell, we need to know "If AB ## has a value, then go to second workbook, find value, copy and paste the array 1X6 (I think that is the correct terminology) to Worksheet #1 starting at AC ##"

Any help in keeping my genius status fresh would be greatly appreciated
 
I'm assuming you would be open to using a macro?

Is the other workbook already opened, or would macro need to open it?

Here's a start:

[pre]
Code: 
Sub FindAndCopy()
Dim OtherWorkbook As Workbook
Dim StartRow As Integer
Dim LastRow As Integer
Dim StartColumn As String
Dim FindValue As String
Dim i As Integer
Dim c As Range

Set OtherWorkbook = Workbooks("Other Book.xls")
'If not already open, you'll need to use something like
'Application.Open "C:DocumentsOther Book.xls"

'Which cell are we starting at?
StartRow = 2
StartColumn = "AB"

LastRow = Cells(StartRow, StartColumn).End(xlDown).Row

With ActiveSheet
For i = StartRow To LastRow
FindValue = .Cells(i, StartColumn).Value
'May need to tweak this line
Set c = OtherWorkbook.Worksheets("Other Sheet").Range("AB:AB").Find _
(what:=FindValue)

c.Offset(0, 1).Resize(1, 6).Copy .Cells(i, StartColumn).Offset(0, 1)
Next
End With
End Sub
[/pre]
 
Questions to Lawrence:


1. What does cell AB## looks like? I'm just curious because i work on a lot of inventory analysis, too.


2. I don't quite understand what you mean by "copies and pastes the next 6 horizontal cells from the second workbook to the first workbook. " do you mean once you have found the AB## in 2nd workbook you copy from the cell holding AB## and copy across 6 columns on the same row?
 
Adding on fred's Q2, I assumed in my macro that if I found the value in say cell AB3, that you wanted to copy cells AC3:AH3 over to original workbook. That is being controlled by this line:

c.Offset(0, 1).Resize(1, 6).Copy .Cells(i, StartColumn).Offset(0, 1)
 
Thanks Luke / Fred, sorry I did not explain myself. Let me try this way, if cell AB 21 has a value of 1QASW2, then we need to go to the other speadsheet (which will be open already), find "1QASW2" in lets say cell B2, then copy cells C2 - I2 back onto the original spreadsheet in cells AC 21 - AI 21. Hope that makes this a little clearer. Thanks again for your help:)

LCD
 
Lawrence,

Do you know that the item to be found exists in 2nd workbook? and, if it exists, will it always be in col B?
 
Then the macro I posted earler should work. You just need to define the name of workbook properly at beginning, and modify this line:

[pre]
Code: 
Set c = OtherWorkbook.Worksheets("Other Sheet").Range("AB:AB").Find _
(what:=FindValue)
[/pre]
to the correct sheet name and column reference.
 
You must log in or register to reply here.
Back
Top