sync

Posted by Tan Le on September 10, 2001 9:43 PM

Hi Mr Excel!

I have two Excel workbooks. First workbook has two columns: Symbol (col. A) and Industry (col.B). The second workbook has also two columns: Symbol (col. A) and Description (col. B). As you can see both workbooks have the same Symbol because the contents of Symbols anr identical. The problem is both Symbols in both workbooks are not in any order per row. What I try to do is to copy the Description from the second workbook to the first workbook with respect to the Symbol. That will the third row in the first workbook containing the Descrition info. So that the first workbook will have in this order per row: Symbol, Industry, and Description. Below is the macro VBA in that I wrote: I know there are lots of error.

Sub Desc()
Dim Desc As String
For i = 1 To 10000
If Range("A" & i).Value &LT;&GT; "" Then
Windows("firstworkbook.xls").Activate
Symbol = ActiveCell.Value 'go to A1 and assign it
Windows("secondworkbook.xls").Activate
Columns("A:A").Select 'Symbol column
Selection.Find(What:=Symbol, After:=ActiveCell, LookIn:=xlFormulas, _LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _MatchCase:=True, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select 'move to the right
Desc = ActiveCell.Value 'assign Desc string
Windows("firstworkbook.xls").Activate
ActiveCell.Offset(0, 2).Select 'move to col C
ActiveSheet.Paste.Value Desc 'Paste Desc to col C
ActiveCell.Offset(1, -2).Select 'Go back to symbol
Next i
End Sub

I sincerely appreciate you help.

Tan Le

Posted by Richard S on September 11, 2001 12:04 AM

ActiveCell.Offset(0, 1).Select 'move to the right Desc = ActiveCell.Value 'assign Desc string ActiveCell.Offset(0, 2).Select 'move to col C ActiveSheet.Paste.Value Desc 'Paste Desc to col C ActiveCell.Offset(1, -2).Select 'Go back to symbol Next i End Sub

Tan

Assume Book2 has Symbol and Industry and Book1 has Symbol and Description. Each column has a header as above. In Book2, name the range containing the Symbols, including the header, "Symbol", and name the range containing the description, including the header. "Description". In column C Row A of Book1, type the Description header EXACTLY the same as it appears in Book2. Copy would be the best bet. Then enter the following formula in cell C2.

=IF(ISERROR(MATCH(A2,Book2!Symbol,0)=TRUE),0,HLOOKUP($C$1,Book2!Description,(MATCH(A2,Book2!Symbol,0))))

Copy down as far as needed.

I know it's a bit of a monster, and someone else could probably do it easier. What it does is looks for the symbol in columnA in the curent book in the range "Symbol" in book2. If it isn't there, it returns zero, if it is, it looks for the word description ($C$1) in the range called desription, then returns the value the number of rows down where the symbol was found.

Hope this makes sense.

Richard

Posted by Richard S on September 11, 2001 12:06 AM

Sorry, got the Books around the wrong way. Just reverse them

Posted by Juan Pablo on September 11, 2001 7:30 AM

I think this is simpler than that, just use the VLOOKUP formula. Put in C1 in Book1:

=VLOOKUP($A1,[Book2.xls]Sheet1!$A$1:$B$2000,2,0)

And copy down as far as needed

Juan Pablo Hi Mr Excel! ActiveCell.Offset(0, 1).Select 'move to the right Desc = ActiveCell.Value 'assign Desc string ActiveCell.Offset(0, 2).Select 'move to col C ActiveSheet.Paste.Value Desc 'Paste Desc to col C ActiveCell.Offset(1, -2).Select 'Go back to symbol Next i End Sub

Posted by Tan Le on September 11, 2001 2:34 PM

Hi Richard,

I am so glad that I you are helping me. Your answer is better than VBA. But I need more help. I can't get it to work. How do name the range "header"? Is is just typing the titles in row 1 for every column? I re-iterate the problem below and incorporate your formula. Please see if that is correct!

I have two Excel workbooks. Book1 has two columns: Symbol (col. A) and Industry (col.B). The Book2 has also two columns: Symbol (col. A) and Description (col. B). As you can see both workbooks have the same Symbol because the contents of Symbols are identical.

Book1.xls:

1 Symbol Industry
2 CORN FOOD
3 OAT WHEAT
4 CARROT VEGE
.
.
.
.

Book2.xls:

1 Symbol Description
2 CORN IT'S A YELLOW SEED
3 CARROT IT'S A RED VEGE
4 OAT IT'S A BROWN GRAIN

The goal is to make book1.xls to look like this:

1 Symbol Industry Description
2 CORN FOOD IT'S YELLOW SEED
3 OAT WHEAT IT'S BROWN GRAIN
4 CARROT VEGE IT'S RED VEGE

You suggest to put the equation in book1.xls cell C2 equal to:
=IF(ISERROR(MATCH(A2,Book1.xls!Symbol,0)=TRUE),0,HLOOKUP($B$2,Book2!Description,(MATCH(A2,Book1!Symbol,0))))

I tried...any idea?

Best regards,
Tan Le

Posted by Aladin Akyurek on September 11, 2001 3:57 PM

I have 12 worksheets in a workbook, in each one I'm running solver with a slightly different set of constraints. I tried to record a macro where I clicked into each cell, selected solver (where the constraints are already entered), hit 'solve', hit 'ok', and went on to the next sheet. However, when I run the macro, it says there's an error at the command 'SolverOK'. How can I automate the 12 runs of solver in the different worksheets?

Posted by Richard S on September 11, 2001 3:59 PM

___Change the Book1.xls here to Book2.xls I tried...any idea? Best regards,

Tan,

Have you named the ranges? In Book2.xls, highlight all the data containing the symbols and name the range "Symbol". This can be done by clicking in the name box at the top left hand corner of he screen, or through menu command Insert|name|define. The same has to be done for the column containing descriptions. In the formula, where it say Book2.xls!Symbol and Book2.xls!Description, the Symbol and Description are the named ranges. If they are nor named, it won't work. ALternatively, you can replace the names with the actual range, ie $A$1:$A$50 if there are fifty rows. Naming the ranges just makes it easier to increase the size of the database without having to re-do the formula. Sorry about late reply, but I think I'm on the other side of the world.
Richard