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
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