Multiplication Problem ...Suggestion required

Z

zaska

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


<table border="0" cellpadding="0" cellspacing="0" width="554"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL</td> <td class="xl65" style="border-left:none;width:60pt" width="80">TIMESTAMP</td> <td class="xl65" style="border-left:none;width:48pt" width="64">OPEN</td> <td class="xl65" style="border-left:none;width:48pt" width="64">HIGH</td> <td class="xl65" style="border-left:none;width:48pt" width="64">LOW</td> <td class="xl65" style="border-left:none;width:71pt" width="95">CONTRACTS</td> <td class="xl65" style="border-left:none;width:66pt" width="88">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11299.9</td> <td class="xl65" style="border-top:none;border-left:none">11346.95</td> <td class="xl65" style="border-top:none;border-left:none">11212.25</td> <td class="xl65" style="border-top:none;border-left:none">39508</td> <td class="xl65" style="border-top:none;border-left:none">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11340.05</td> <td class="xl65" style="border-top:none;border-left:none">11354.75</td> <td class="xl65" style="border-top:none;border-left:none">11240</td> <td class="xl65" style="border-top:none;border-left:none">640</td> <td class="xl65" style="border-top:none;border-left:none">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">6668.5</td> <td class="xl65" style="border-top:none;border-left:none">6729.95</td> <td class="xl65" style="border-top:none;border-left:none">6660</td> <td class="xl65" style="border-top:none;border-left:none">81</td> <td class="xl65" style="border-top:none;border-left:none">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">50</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> </tbody></table>
The above table is the original data. I have one more excel file stored in E Drive which contains the Lot Size values for each symbol but there is only one symbol reference for all the Three expiry dates because the lot size is same for Each contract be it First Month Contract (-I),Second Month Contract (-II) or Third Month Contract (-III)

Example of Lot size data

<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="xl65" 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="xl65" 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> </tbody></table>
Example of desired output in the original table

BANKNIFTY-I 39508*25
BANKNIFTY-II 640 *25
BANKNIFTY-III 0 *25
CNXIT-I 81 *50

Etc..

I want to multiply the Values of Contract Column in the orignial table with the Lot size values Referred to in another excelsheet.

Kindly give your suggestions

Regards,
Zaska
 
Sort by date Sort by votes
Sir,

When i copied the above formula and pressed enter the formula Changed (Double Reference to NSE Converter.xls ) to the following and returned #N/A

Code: 
=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[E:\Macros\[NSE Converter.xls]Sheet1]NSE Converter.xls]Sheet1'!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[E:\Macros\[NSE Converter.xls]Sheet1]NSE Converter.xls]Sheet1'!$B$1:$B$226)
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
zaska said:
Sir,

When i copied the above formula and pressed enter the formula Changed (Double Reference to NSE Converter.xls ) to the following and returned #N/A

...

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[E:\Macros\[NSE Converter.xls]Sheet1]NSE Converter.xls]Sheet1'!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[E:\Macros\[NSE Converter.xls]Sheet1]NSE Converter.xls]Sheet1'!$B$1:$B$226)

...
Click to expand...

Please do not use the CODE tag for this... Obviously, the double reference is wrong...

Recipe:

1. Open NSE Converter.xls.

2. Switch to the workbook housing the formula.

3. Go to the first formula cell and paste the following:

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[NSE Converter.xls]Sheet1'!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)
 
Upvote 0
Sir,

I got Syntax Error When The same formula is used in VBA


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"
[COLOR=Red]Range("H2:H" & LR).Formula =   F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[NSE Converter.xls]Sheet1'!$A$1:$A$228,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)[/COLOR]
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
Sir,

I got syntax error when used the working formula in the below VBA code

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" [COLOR=Red]Range("H2:H" & LR).Formula =   F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[NSE Converter.xls]Sheet1'!$A$1:$A$228,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)[/COLOR] 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

Kindly suggest the necessary corrections.

Thanks
 
Upvote 0
I think you probably need to wrap the formula in double quotes:

Code: 
Range("H2:H" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$228,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)"
 
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
619
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
574
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
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D

Forum statistics

Threads
1,224,532
Messages
6,179,388
Members
452,908
Latest member
MTDelphis

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