function via vb

[ceemo]

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

[Rick Hansen]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>