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


Write Script Which Returns All Files And Folders?

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:

Public Sub List_Files_Mac()
Dim sDir As String
Dim sFile As String


This script doesn't return the folders and long filenames are truncated.

View 4 Replies View Related

How To Write A UDF That Returns Variable Length Of Data

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

Macro To Compute Data Using SUMMIF

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

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 & ")"


View 1 Replies View Related

Macro To Match 2 Tables And Compute Values

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


How should the end result look like is displayed on the last sheet.

View 1 Replies View Related

Putting Items In Maturity Buckets?

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


View 6 Replies View Related

Using Find For Similar Searches

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


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

FIND From Right To Left Or Similar?

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.

There must be a smarter way of doing this. TY

View 8 Replies View Related

Find Similar Content

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

How To Find, Exist Similar Sheet Name

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

Formula To Find Similar Data In Columns

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:

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

Find Similar Words Between 2 Columns & Move To Same Row

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

Formula To Find Data And Return Other Column Value (similar To VLookup)

Jul 7, 2014

Sheet 1
Sheet 2


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

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

Find Returns #VALUE! Error

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


Then the expected output is returned

View 2 Replies View Related

Macro Sorts Similar Worksheets

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?

Sub macro1()
' macro1 Macro
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Add Key:=Range( _


View 7 Replies View Related

Find Method Only Returns 2nd Value In Cell

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:="*******"
parttofind = InputBox("What is the number or discription")


View 1 Replies View Related

FIND Returns Previous Data

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

Find Method Returns Nothing To Variable

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

Concatenated IF And FIND Formula Returns Only First Condition And Not The Other

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

Find Method Returns A String Instead Of A Range

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

Find Function In VBA Returns A Date That Is Almost Correct

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

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

Find Replace To Insert Carriage Returns

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

Using Vlookup Or Similar Function To Find Missing Values Compared To Base Values

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.


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

Find Function In Calculated Column In PowerPivot Always Returns Error?

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.


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

Reverse Engineer Macro To Return Data Back To Similar State?

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.


View 2 Replies View Related

Macro To Set Current Time In A Cell Without Changing Similar Values In Other Cells

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

How Can I Write A Macro To Download

Mar 23, 2007

how can i create a macro to open the page


and download all the form into excel automatically?

View 9 Replies View Related

Macro To Write CSV File

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

Copyrights 2005-15 www.BigResource.com, All rights reserved