Concatenating Using VBA based on Expiry Date

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi...

<table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:78pt" height="20" width="104"> <table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:78pt" height="20" width="104">FUTSTK</td> <td style="width:83pt" width="111">VOLTAS</td> <td class="xl65" style="width:65pt" align="right" width="87">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>VOLTAS</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> </tbody></table></td> <td class="xl63" style="border-left:none;width:83pt" width="111">
</td> <td class="xl63" style="border-left:none;width:65pt" width="87">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">I want to accomplish the following from the above table.

1. Delete all the Data for the "OPTIDX" Symbols. I want data only for the rows having "FUTSTK"

2. Append "-I" for 1st expiry date i.e 28-Jul-2011 and "-II" for 2nd Expiry Date i.e 29-Sep-2011 and "-III" for 3rd Expiry date.

Desired Output

VOLTAS-I
VOLTAS-II
WELCORP-I
WELCORP-II
WELCORP-III
WIPRO-I
WIPRO-II
YESBANK-I
YESBANK-II
YESBANK-III

Thank you


</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Perhaps

Code: 
Sub test2()

Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("C:E").Delete
Columns("A").Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sir,

I got Error. Next used without For

Code: 
Sub test2()  Dim LR As Long, i As Long Application.ScreenUpdating = False LastRow = Cells(Rows.Count, "B").End(xlUp).Row  With Range("A1:O" & LastRow)      .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"      .Offset(1, 0).EntireRow.Delete  End With  ActiveSheet.AutoFilterMode = False  LR = Range("A" & Rows.Count).End(xlUp).Row Columns("C").Insert Range("C2").Value = "-I" For i = 3 To LR     If Range("B" & i).Value = Range("B" & i - 1).Value Then         Range("C" & i).Value = Range("C" & i - 1).Value & "I"     Else         Range("C" & i).Value = "-I"     End If Next i For i = 2 To LR     Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value     Range("D" & i).NumberFormat = "yyyymmdd"     Range("D" & i).Value = Range("P" & i).Value Next i [COLOR=Red]Next i[/COLOR] Columns("C").Delete Columns("N:O").Delete Columns("L").Delete Columns("I:J").Delete Columns("C:E").Delete Columns("A").Delete Application.ScreenUpdating = True End Sub</pre>
 
Upvote 0
Oops!

Code: 
Sub test2()

Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("C:E").Delete
Columns("A").Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Sir,

Still didn't get the Time Stamp Column

Here's the Original Data

<table border="0" cellpadding="0" cellspacing="0" width="972"><col style="width:48pt" span="14" width="64"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:48pt" height="20" width="64">A</td> <td class="xl64" style="width:48pt" width="64">B</td> <td class="xl64" style="width:48pt" width="64">C</td> <td class="xl64" style="width:48pt" width="64">D</td> <td class="xl64" style="width:48pt" width="64">E</td> <td class="xl64" style="width:48pt" width="64">F</td> <td class="xl64" style="width:48pt" width="64">G</td> <td class="xl64" style="width:48pt" width="64">H</td> <td class="xl64" style="width:48pt" width="64">I</td> <td class="xl64" style="width:48pt" width="64">J</td> <td class="xl64" style="width:48pt" width="64">K</td> <td class="xl64" style="width:48pt" width="64">L</td> <td class="xl64" style="width:48pt" width="64">M</td> <td class="xl64" style="width:48pt" width="64">N</td> <td class="xl64" style="width:57pt" width="76">O</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">INSTRUMENT</td> <td>SYMBOL</td> <td>EXPIRY_DT</td> <td>STRIKE_PR</td> <td>OPTION_TYP</td> <td>OPEN</td> <td>HIGH</td> <td>LOW</td> <td>CLOSE</td> <td>SETTLE_PR</td> <td>CONTRACTS</td> <td>VAL_INLAKH</td> <td>OPEN_INT</td> <td>CHG_IN_OI</td> <td>TIMESTAMP</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">11295.5</td> <td align="right">11295.5</td> <td align="right">39508</td> <td align="right">111327.1</td> <td align="right">969625</td> <td align="right">44900</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">11322.35</td> <td align="right">11322.35</td> <td align="right">640</td> <td align="right">1807.97</td> <td align="right">22625</td> <td align="right">5575</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">11512.15</td> <td align="right">11519.8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">6692.8</td> <td align="right">6692.8</td> <td align="right">81</td> <td align="right">270.98</td> <td align="right">15650</td> <td align="right">400</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6211</td> <td align="right">6770.3</td> <td align="right">0</td> <td align="right">0</td> <td align="right">50</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>CNXIT</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">6782.3</td> <td align="right">6831.8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">5685</td> <td align="right">5699</td> <td align="right">5616.1</td> <td align="right">5638</td> <td align="right">5638</td> <td align="right">31575</td> <td align="right">35668</td> <td align="right">677440</td> <td align="right">-3180</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">5697.4</td> <td align="right">5710</td> <td align="right">5633</td> <td align="right">5654.7</td> <td align="right">5654.7</td> <td align="right">2389</td> <td align="right">2707.2</td> <td align="right">98760</td> <td align="right">1280</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>MINIFTY</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">5710.9</td> <td align="right">5719</td> <td align="right">5651</td> <td align="right">5672.75</td> <td align="right">5672.75</td> <td align="right">335</td> <td align="right">381.05</td> <td align="right">4080</td> <td align="right">4080</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2630.6</td> <td align="right">2443.5</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2460.4</td> <td align="right">2461.9</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>NFTYMCAP50</td> <td class="xl63" align="center">########</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2476.6</td> <td align="right">2484.25</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> </tbody></table>

Thanks
 
Upvote 0
Try

Code: 
Sub test2()

Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Sir,

Thank you finally it worked. Finally i have one question.

I have Lot size values in another worksheet which will be updated every month at the time of expiry dates.

Now i would like to find the best possible way to multiply the lot size with no.of contracts.

Here's the data which is contained in another excel file.

<table border="0" cellpadding="0" cellspacing="0" width="163"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL </td> <td class="xl63" style="width:48pt" align="right" width="64">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY </td> <td align="right">25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY </td> <td align="right">20</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50</td> <td align="right">150</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Symbol </td> <td class="xl63" align="right">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH</td> <td align="right">8000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">TRIVENI </td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABAN </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABB </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABGSHIP </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABIRLANUVO</td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ACC </td> <td align="right">250</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ADANIENT </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ADANIPOWER</td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ALBK </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ALOKTEXT </td> <td align="right">10000</td> </tr> </tbody></table>
For Each Symbol The lot size will be same for all the three expiry periods. How do i accomplish this task

I want to Multiply the Contract column with the Lot Size Values for Each symbol.

Kindly give your suggestion.

Regards,

Zaska
 
Upvote 0
This seems to be a different question plus I am about to go out for the day so I suggest that you start a new thread.
 
Upvote 0
Thank you sir. This code saved me lot of time and it is uncomparable with the recorded macro

Have a nice day


​
 
Upvote 0
Sir,

How to insert the Following formula in the VBA Code. Actually Sheet1 is contained in another workbook named "NseConverter.xls" which is stored in E:\Macros and i want to fill this formula till the last column.

Code: 
=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet1!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet1!$B$1:$B$226)
Code: 
Sub test()
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Application.ScreenUpdating = True
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close 
 End Sub
Regards,

Zaska
 
Upvote 0
Try

Rich (BB code): 
Sub test()
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Range("G2:G" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&[E:\Macros\Output\NseConverter.xls]Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),[E:\Macros\Output\NseConverter.xls]Sheet1!$B$1:$B$226)"
Application.ScreenUpdating = True
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
 End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,585
Messages
6,179,706
Members
452,939
Latest member
WCrawford

Share this page

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