PDA

View Full Version : Loop through columns and vlookup



KarimBs
08-11-2013, 05:25 PM
Hi.

Can anybody help me code this thing I'm doing more efficiently? I am new to VBA and have to first calculate returns (LN(B3/B4) in first sheet for over 30 different securities (their prices are 4 columns apart, the time series are different lengths).I have done a code for this first step, but it takes too long to run, since the number of rows in a column is often more than 3000. The second step, in sheet 2 (Second table below) is to use something like vlookup to paste the calculated returns to the third column in the second Sheet, assigning it to the right date (whose row number in the column differs) The securities are in order, so it is really only about keeping the route steps. I have been using the code below, but I can not figure out how to make the code more efficient :(

The table attached shows how my data is arranged, and also column C in the first sheet illustrates how I need to calculate the returns and the second sheet (C) shows what values and where they need to be looked up.


Thank you!

my codes:

Sub Returnsln()
For j = 3 To 150 Step 4
For i = 4 To 3799
Cells(i, j).Activate
ActiveCell.FormulaR1C1 = "=LN(RC[-1]/R[-1]C[-1])"
Next i
Next j
End Sub

Sub vlookingup()
For b = 3 To 130 Step 4
For c = 4 To 1655
Cells(c, b).Activate
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],TRI!RC[-2]:R[3799]C[1],3,FALSE)"
Next c
Next b
End Sub






Date(A)
Price (B)
Return(C)
Empty Column (D)
Date(E)
Price (F)
Return(G)
Empty Column (H)
ETC


12.2.2012
7


14.3.2011
4





13.2.2012
9
=LN(B2/B1)

15.3.2011
3
=LN(F2/F1)


















Date (A)
Variable2(B)
Return (C)
Empty Column (D)
Date(F)
Variable2(G)
Return(H)
ETC


X

=Vlookup(X;(previoustable range A2:C?;3,FALSE)

Y

=Vlookup(X;(previoustable range F2:H?;3,FALSE)



X+1









etc

p45cal
08-12-2013, 12:20 AM
but it takes too long to run, since the number of rows in a column is often more than 3000try,untested:
Sub Returnsln()
For J = 3 To 150 Step 4
Range(Cells(4, J), Cells(3799, J)).FormulaR1C1 = "=LN(RC[-1]/R[-1]C[-1])"
Next J
End Sub

Sub vlookingup()
For b = 3 To 130 Step 4
Range(Cells(4, b), Cells(1655, b)).FormulaR1C1 = "=VLOOKUP(RC[-2],TRI!RC[-2]:R[3799]C[1],3,FALSE)"
Next b
End Sub