function via vb
 

ive got two worksheets both with approx 20,000 records of various dat
but they both have a unique identifier (i'll call this ref no.) i
sheet A there is a column named dates which doesnt appear in sheet 2.
have been using an offset(match()) formula to look in sheet a for
match on the ref no and if it exists find the associated date and plac
in the new column on sheet B. doing this over all 20,000 records i
sheet b slows down the time it takes to calculate, and all othe
calculations in the book. I figured i might be able to use a vb cod
loop of some sort to carry out the action for me.

please hel

--
ceem
-----------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065
View this thread: http://www.excelforum.com/showthread.php?threadid=52455

function via vb
 

Hello Ceemo,

Here is bit of VBA code you can try and modify for your use.
enjoy Rick

Option Explicit
Sub RefNoDates()

Dim Srng As Range, fndrng As Range
Dim wsA As Worksheet, wsB As Worksheet
Dim iRow As Long

Set wsA = Worksheets("SheetA")
Set wsB = Worksheets("SheetB")

' set range of Ref No's on SheetA, Start at "B2"
' down to "Bxxxxx" , end of ref no's
Set Srng = wsA.Range("B2:B" & wsA.Range("B2").End(xlDown).Row)

' loop thru all ref no's on SheetB
For iRow = 2 To wsB.Range("B2").End(xlDown).Row
' search for ref no
Set fndrng = Srng.Find(what:=wsB.Cells(iRow, "B"))
If Not fndrng Is Nothing Then
' if found, copy date from Column C,
' paste to sheetB Cells(irow,"C")
wsA.Cells(fndrng.Row, "C").Copy wsB.Cells(iRow, "C")
End If
Next iRow

End Sub



"ceemo" wrote in message
...

ive got two worksheets both with approx 20,000 records of various data
but they both have a unique identifier (i'll call this ref no.) in
sheet A there is a column named dates which doesnt appear in sheet 2. I
have been using an offset(match()) formula to look in sheet a for a
match on the ref no and if it exists find the associated date and place
in the new column on sheet B. doing this over all 20,000 records in
sheet b slows down the time it takes to calculate, and all other
calculations in the book. I figured i might be able to use a vb code
loop of some sort to carry out the action for me.

please help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=524553