VBA- Write A Macro To Find The Similar Maturity, Compute The Returns
Feb 13, 2007
I am working with financial data and am exposed to a problem that excel formula cannot solve. I am very new to VBA and would like some assistance please. I have in one excel column the list of maturities in dates eg. EN02, EN03, EN04 etc (EN=January). I have in another column the corresponding prices for these maturities. What I would like to do is compute the returns on similar maturities and paste the returns in the returns column. I can use a formula for this when the consecutive maturities are the same. The problem arises when I want excel to find the previous similar maturity, which may be 5 cells or so before. How do I write a macro to find the similar maturity, compute the returns and place that returns value in the returns column.
View 10 Replies
ADVERTISEMENT
Nov 2, 2012
I need to write a script which returns all files and folders. The script should also work on a Mac pcs. The "Windows" script works without any problems but I have real problems with the Mac script:
Code:
Public Sub List_Files_Mac()
Dim sDir As String
Dim sFile As String
[Code]...
This script doesn't return the folders and long filenames are truncated.
View 4 Replies
View Related
May 24, 2008
If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.
The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.
View 9 Replies
View Related
Oct 5, 2011
I have the following macro to compute data using SUMMIFS
Range("E" & finalrow + 9).Formula = "=SUM(SUMIFS(E8:E" & finalrow & ",F8:F" & finalrow & ",{""PSP101"",""PSP611"",""PSP140",""7*""}))"
The last line up code is mot working as I am tying to using a Wildcard for the last criteria
""7*""
Where text starting with a 7 appears in column F for eg 76601G, 75106K etc appears it must be included in the criteria
Full Code-below
Sub Totals()
finalrow = Range("A65536").End(xlUp).Row
Range("A" & finalrow + 3).Value = "Total Movement"
Range("E" & finalrow + 3).Formula = "=sum(E8:E" & finalrow & ")"
[Code]....
View 1 Replies
View Related
Jan 9, 2013
I'm trying to make a macro that match table in sheet "Vocabulary" to the second table (columns Q,R,S,T) in sheet "Overview" and compute correct values in column U using weights from column F, sheet Vocabulary on values from a corresponding cell at column M, sheet Overview. The entries for every ISIN in the sheet Vocabulary are in no particular order and some positions from sheet Overview don't have to exist in Vocabulary at all (but I still need them to display in the table on the right to be able to make charts from summing these categories).
Portfolios.zip
How should the end result look like is displayed on the last sheet.
View 1 Replies
View Related
Jun 17, 2014
I need to ID various items by maturity bucket. each item has a maturity date and I need to put them in various buckets (1-9) based on date ranges.
the dates on these buckets would change depending on the initial date used (in this case I start at 3/31/2014), but i would update that date as needed, thusly the begin/end dates for these buckets would change.
I tried this formula which gives me an error (d2 is the maturity date of the first item, it would change as the list goes down):
=IF(AND(d12>=F1,d12=G1,D12=H1,d12=I1,d12=j1,d12=k1,d12=l1,d12=M1,d12N1),"Bucket 9",0)))))))))
F
G
[Code].....
View 6 Replies
View Related
Mar 6, 2014
Problem is Findstring finds cells that contain "SS1" AND "SS1 EXT". These are separate grades.
I need Findstring to only find SS1. Using xlWhole doesn't work for me, as the cells also contain other data. For example: " SS1 48 /"
CODE:
FindString = "SS1"
Findstring1 = "SS1 EXT"
With Sheets("Sheet1").Range(Rng4)
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
[Code] ........
View 1 Replies
View Related
May 2, 2014
Cell A1 = 78-36
This is a win/loss ratio so to have it as a percentage I would have to divide the first number by the sum of the two numbers so 78/114 = 68.4% How would you do this with a formula?
I came up with this that isn't quite right, because RIGHT and FIND don't work together.
=LEFT(A1,FIND("-",A1,1)-1)/(LEFT(A1,FIND("-",A1,1)-1)+RIGHT(A1,FIND("-",A1,1)-1))
There must be a smarter way of doing this. TY
View 8 Replies
View Related
Jan 17, 2007
Daily We have to search the similar numbers in a enormous database (around 35000 - 45000). Base will vary daily.
The Numbers in 2nd Row of 'wish sheet' has to be compared with 'base sheet' (B,G & L columns in Base sheeet) and the similar numbers has to be copied down from corresponding 3rd row. single character variants and double charcter varients need to be found. box is made to differentiate single and double variants. The test file may be useful to identify my problem.
It is very difficult to use find Numbers manualy. We have been helped by the experts on various circumstances in the past. So, We again seeking help from our senior brothers here.
View 9 Replies
View Related
Apr 29, 2006
I use macros, where in DialogSheet EditBox i entered new sheet name and add it.
How to find, exist this new sheet name or not in this workbook?
View 9 Replies
View Related
Feb 17, 2007
I need help creating a formula to find data in one column that starts with different letters and counts them up.
For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND
In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.
The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.
View 9 Replies
View Related
Mar 19, 2008
I have two different lists that have some similar text and some irrelevant text. What I need to do is move the matching words from column B to the same row in column a and leave blank the rows that have no similar words. Column A will not move ...
View 7 Replies
View Related
Jul 7, 2014
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
View 3 Replies
View Related
Jan 19, 2010
At attachment when we enter value to f11 and h11 together, certificate number must be come up in cell of f13
View 14 Replies
View Related
Sep 25, 2008
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345
Hello,
What I am trying to do is
Search the number written at ColumnB in ColumnA and if found write YES to column C
if not write No
View 9 Replies
View Related
Nov 12, 2006
My formula =IF(OR( FIND("Bugs",E2),FIND("Daffy",E2)),"/wkly",0). Always returns #VALUE! error. I can understand if neither value occurs in the string, what confounds me is if one of the two does occur in the string If I shorten to
=IF(FIND("Bugs",E2),"/wkly",0)
Then the expected output is returned
View 2 Replies
View Related
Mar 25, 2012
I have a file called "registrantExport" with "_great north", "_new england", "_new england", etc. I'm trying to sort them by Division and then by Last Name. Here is the macro I've tried to make, however it doesn't work properly, as it only applies to great north. How could I make so that it works for any worksheet I apply the macro to?
Code:
Sub macro1()
'
' macro1 Macro
'
'
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Add Key:=Range( _
[Code]...
View 7 Replies
View Related
Mar 11, 2014
My .find function is only finding the 2nd value in a cell example If i look up the term " HOSE"
5/8 hose water Will return
5/8 heater hose will not
Basically what I am making is something I am calling a "what" button. It queries my database and returns all the part numbers with the term i put in and i thought it was working fine till i was testing it, did i do something to select this? I've tried xlpart and xl whole. Neither is working.
Here is the code below
Sub COPYPARTNUMBERINFO()Dim parttofind As String
Dim partcell As Range
Dim partref As Range
ActiveSheet.Unprotect Password:="*******"
Range("t6:T10000:u6:U10000").Select
Selection.ClearContents
parttofind = InputBox("What is the number or discription")
[code].....
View 1 Replies
View Related
Mar 17, 2009
This code (partial) was written to interrogate a database. Cell "A2" of another tab contains a Part Number to be sought.
Target = Range("A2")
Sheets("Kanban Data").Select
Dim Nrow As Range
Dim N As Integer
On Error Resume Next
With Sheets("Kanban Data")
Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If the Find expression (as pulled down from the Edit menu) is empty, then inserting a Part Number in A2 and executing the macro finds the correct record and returns the its correct row number (N, as defined afterwards in the code). Then, changing the Part Number in cell A2 and executing a new search returns the SAME row as the previous search, despite Target being equal to the new sought after value.
View 9 Replies
View Related
Sep 29, 2006
I have 2 worksheets, one called Summary and the other called LNB. Column A in both worksheets contain account numbers.
I am trying to tell excell to look at the LNB worksheet and find the account number that is on the summary worksheet. If the account number is found, then tell me the row, on the Summary worksheet, that the account was found.
The function returns "Empty" even though I know the accounts do exist on both sheets and I dont know why.
Here is the code ....
View 9 Replies
View Related
Aug 6, 2014
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies
View Related
Jun 24, 2009
For some reason, it seems that this following line returns the value its supposed to be searching for instead of the range where it finds the value.
View 5 Replies
View Related
Feb 22, 2013
Is it possible to see why this code returns a date that is almost correct? Searching for 01.01.2005 I get 01.11.2005 as result. I would like the code to only show the correct date, and if it doesn't exist it should end up showing "nothing".
Code:
Set fstdate = wsOBX.Range("B1:B" & lr1).Find(what:=startdate, _
LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
View 3 Replies
View Related
Jun 30, 2013
I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.
View 9 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Aug 2, 2014
I have created a calculated column in PowerPivot and inserted a formula that worked in a normal excel spreadsheet however, this formula does not work in PP anymore.
=find("green",Table1[Name],1)
Name column:
NAME: Bag green
it should find "green" in the name and return the position. Hoever it alway returns the error, that the find function could not find the string.I have attached an example spreadsheet.
View 1 Replies
View Related
Feb 26, 2014
I am trying to create a macro, that ultimately does the reverse of one written a while back.
I have created a macro that exports "Roster_Data" to "envision_Roster" in .csv style formatting. What I want to achieve is reverse engineer the macro to return the data back to a similar state.
I am trying to transpose column D into rows that correspond with dates in column c in a sheet called "OutputView" this in essence is similar if not exactly the same as the original worksheet "Roster_Data"
I have split the macro I am working on into three separate modules.
Module 1 - Initial Export of "Roster_Data" to "envision_Roster"
Module 2 - Format and output worksheet to find MAX date and MIN date and produce top rows of data
Module 3 - Analyse, Undertake Logical Tests of data, and transpose to suit (Replicate the initial "Roster_Data" view)
I'm having trouble visioning this altogether so I've been starting with basic code to output basic stuff, but I still can't relate this back to my data.
[Code]....
View 2 Replies
View Related
Mar 4, 2012
I have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub
Even assigning value to variable does not work:
Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
how to restrict the macro to change the value of current/active cell only without effecting other cell values?
View 4 Replies
View Related
Mar 23, 2007
how can i create a macro to open the page
[url]
and download all the form into excel automatically?
View 9 Replies
View Related
Dec 14, 2009
I have a CSV file that contains data in columns A-Q. Some cells may be blank.
I'm having trouble opening this CSV file because when I re-save it after editing it, Excel removes any quotes that were around the data.
Here is the criteria that the data must be formatted like:
If the value in column A = "H", the following columns need quotes around the cell - A-F, H-I, K-M, P-Q. The values in the rest of the columns do not need quotes.
If the value in column A = "D", the following columns need quotes around the cell - A-D, F, I-O, Q. The values in the rest of the columns do not need quotes. Data is only in columns A-Q.
Can somebody write a macro that will format the data as list above and save?
View 9 Replies
View Related
