PDA

View Full Version : Linking?



drums4monty
02-21-2007, 12:17 PM
Hi All

I have a Workbook with 2 worksheets consisting of Coloumn A & B and over 2000 Rows.

Sheet 1 has Reference numbers in the Col A and some data in col B. Worksheet 2 has Reference numbers in col A and a price in col B.

An example of the Reference nums is AB001, AB002, AB003, AB005 etc, as you can see there are gaps in the sequence, Worksheet 2 is the same. Worksheet 1 reference nums starts at say Row 100, this cannot be changed and Worksheet 2 reference nums start at Row 1.

Is it possible to link the 2 worksheets so that the price in col b sheet 2 for the ref AB001 for example, gets placed in col c of sheet 1 for ref AB001 etc?

I know it is as clear as mud but can someone help please?

Regards

Alan

Charlize
02-21-2007, 12:52 PM
Sub move_it()
Dim lrow As Long, lrow2 As Long
Dim cell As Range, result As Range, rngsource As Range
Dim rngtocopyfrom As Range
lrow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
lrow2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
Set rngsource = Sheets(1).Range("A100:A" & lrow)
Set rngtocopyfrom = Sheets(2).Range("A1:A" & lrow2)
For Each cell In rngsource
Set result = rngtocopyfrom.Find(cell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
result.offset(, 1).Copy Worksheets(1).Range("C" & cell.Row)
End If
Next cell
End Sub

Bob Phillips
02-21-2007, 02:00 PM
=VLOOKUP(A100,Sheet2!A:B,2,False)

and copy down

drums4monty
02-21-2007, 04:03 PM
Thanks both of you.

Charlize code works great. xld, I get the right figure for the first cell but when I copy down I get NA! error.

Regards

Alan

mdmackillop
02-21-2007, 04:09 PM
If you prefer the formula
=IF(ISNA(VLOOKUP(A100,Sheet2!A:B,2,FALSE)),"",VLOOKUP(A100,Sheet2!A:B,2,FALSE))

Bob Phillips
02-21-2007, 04:50 PM
If you prefer the formula
=IF(ISNA(VLOOKUP(A100,Sheet2!A:B,2,FALSE)),"",VLOOKUP(A100,Sheet2!A:B,2,FALSE))

Why does the board insert that space in the second FALSE? I had it happen to me the other day.

Bob Phillips
02-21-2007, 04:51 PM
Thanks both of you.

Charlize code works great. xld, I get the right figure for the first cell but when I copy down I get NA! error.

Regards

Alan

Alan,

That is odd, there is no need to anchor any of the ranges. Can you post your worbook, if only to satisfy my curiosity?

mdmackillop
02-21-2007, 05:00 PM
Why does the board insert that space in the second FALSE? I had it happen to me the other day.
Seems to happen quite often, I've noticed. An annoying little glitch.