Lookup macro

Status
Not open for further replies.

shenoy

New Member
Joined
Jan 18, 2005
Messages
5
Hi all,
I am a newbee to VBA as well as to this forum. can anybody please help me with this problem.
I have two spreadsheets with lets say 10 columns each. there are around 3000 rows in the first spreadsheet and around 4000 in the second sheet. lets call them book A and book B
The first column in the both books contains microfilm numbers and is unique for every record.. Book A has another column called ORC number which is also a unique for every record.
Book B has only the microfilm number common with book A, and rest of the data totally is different. Another thing is, Book B might contain more than one occurance (or omission) of the microfilm number and contains no ORC number.
Now my task is to replace the microfilm number in book B with the ORC numbers from Book A by comparing the two microfim numbers. This is pretty easy doing it manually with lookups but since this is a repitative task i am writing a code.. the code works fine but is very slow. Maybe i did not use the right lingo.. can somebody help me please..
for simplicity's sake i have said book A and book B but actually I have to work with 12 different such books.
Any help is appreciated. thanks!

My code
Sms01000 is the book A
Assuming i am in book B,

Range("N1").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-13],sms01000.txt!C1,sms01000.txt!C23)"
Application.Calculation = xlCalculationAutomatic
Range("N1").Select
Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))
Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)
Columns("N:N").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
hi!
Hope this helps!

Code:
Sub ReplaceFnumToORCNum()
Dim DestBook As Worksheet
Dim SourceBook As Worksheet
Dim Cl As Range
Dim Foundx As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set DestBook = Application.Workbooks("BookB").Sheets("sheet1")
Set SourceBook = Application.Workbooks("BookA").Sheets("sheet1")
' Assuming that the microfilm numbers are in Column A and orc number in B
For Each Cl In DestBook.Range("a1:a" & DestBook.Range("A:A").End(xlUp).Row)
    Set Foundx = SourceBook.Range("A:A").Find(Cl.Value)
    If Not Foundx Is Empty Then
        Cl.Offset(0, 1) = Foundx.Offset(0, 1) ' this places the orc number besides the microfilm number
    Else
        Cl.Offset(0, 1) = "Entry not found on " & SourceBook.Name
    End If
Next Cl
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub
 
Upvote 0
It's unlikely that code that uses a loop is going to be quicker than code that avoids looping by using native XL worksheet functions.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top