function via vb

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ceemo - 20 Mar 2006 21:35 GMT

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

Reply to this Message

Rick Hansen - 21 Mar 2006 00:01 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> please help

Reply to this Message