# Posts by Turbothorup

• ## Sum Function With More Than One Criteria

Dear Ozgrid

I have a large transactions table with 10 columns and more than 7,000 rows.

One of the columns names the country in which the transaction has taken place (array named "country")
Another names the year of the transaction (array named "year")
And a third one holds the transaction value. (array named "value")

What I would like to do is to sum the transaction value of one country for one specific year?
I.e. what was the aggregate transaction value for transactions in Sweden in 2005?
Could anyone help me? Preferably without VBA...

• ## Cross tabulating??

Hi everyone

What is the easiest way to cross tabulate in excel? I have a questionairy, which is filled in by men and women.
What I want to do as easy as possible is to show the results of the questionairy on both groups individually?

Can anyone help me with a function to do so.....

thanks, Lars

• ## range comparision

Re: range comparision

Unbelievable! Excelent solution! I owe you a big lunch
As i see, there is always a more elegant way to solve a problem. I have to admit, that i would need a couple of days (if not more) to solve the problem and probably my code would look like a novel :yikes:

thanx a lot again!
turbothorup

• ## range comparision

Re: range comparision

ok, below you have an excel spreadsheet with data i get. surely there are more columns, which i replaced with "other data". in Sheet2 i have placed a template, how i need the data to be sorted. In column A - portfolio names, in Column C Security Names, and column D - nominals/positions.

I have put some code into comments, i hope the code runs still after that

My code goes through all the portfolio-names (which i defined static, it's ok i find for now), and then finds the ranges and names them. The code of Batman is supposed to compare then the ranges to Master-Range (in column A =MF005) and find differences.

• ## range comparision

Re: range comparision

it works well! i have to replace some features, but the identification of the securities and portfolios works!

i have another question. if you could give a short tip, i would be very happy. in your code there is an avarRangeNames array. in my task the ranges are first identified and then named with VBA like this:

....and here i have my problems, i will pass the names of ranges to the avarRangeNames... and it does, but if i try to read the items of array (for example with msgbox avarRangeNames(1) it gives empty value

Code
``````ReDim avarRangeNames(ItemNum)
'ItemNum is 1 till here, i.e. an array with 1 item

avarRangeNames(ItemNum) = "Portf" & Port
ItemNum = ItemNum + 1``````

what is wrong here?

thanx in anticipation
turbothorup

edit: i have added code tags - jiuk

• ## range comparision

Re: range comparision

thanx for your answer... i'm going to double-check it tomorrow: have to go now. it seems to be the solution! have a nice weekend, Batman!
I'll contact you as soon i have tried out it. Thanx in advance!!!

• ## range comparision

Re: range comparision

thanx for reply! :thanx: ... i'm looking for VBA-Solution.
i have some portfolio of securities (called MF005) and other portfolios, which have to be matched to this portfolio..

I.e. if a portfolio has a security, which is not in MF005 - then we have to SELL this in order to match MF005 (we are not allowed to hold this, because it is not in MF005, MF005 - is the directive!),

and if the MF005 has a security which is not in portfolio, then we have to BUY this security in the portfolio (again in order to match MF005). The MF005 is a template, and all other portfolios have to be matched to it.

I have all the data in one excel-sheet, and it changes, i.e. MF005 is dynamic. Each time MF005 changes, we have to adjust all other portfolios as well. there are surely also things like absolut amounts and currencies, but for the first i need to filter security names.

thanx in anticipation

• ## range comparision

hi there, i need your help again!

i have a lot of ranges with names (Bond ISINs, contain also numbers) and one range to which i have to compare these other ranges. I have to detect all deviations to the "main" range (called MF005) and vice versa. The sizes of ranges are different, sometimes the main range MF005 can contain more and sometimes less names, than other ranges.

Example:
MF005
aaa
aab
aac
abb
abc

Range x
aaa
aac
axx
abc

First difference: MF005 has aab, abb more than Range x
Second difference: Range x has axx more than MF005

I have to detect these two kinds of differences. What would you suggest?

• ## make inputbox bigger?

Re: make inputbox bigger?

but how will i jump to next line.......would like to create line break in case the sentence gets too long?

• ## make inputbox bigger?

Hi....
Is there any way that i can make an inputbox bigger. The thing is that I will use the inputbox as bodytext for an email and it would be nice to see the whole content at the same time?

• ## Open a Lotus email from Excel?

Re: Open a Lotus email from Excel?

Hi Hanss

You have to explain me the two lines that I have highlightened below. I do not know how to send an email to another person. I can only send it to myself. Could you tell me how the syntax should look if I wish to send to a fictional email called "[email protected]".
Tried the one below, but without any luck.....think I don't fully understands....

• ## Open a Lotus email from Excel?

Re: Open a Lotus email from Excel?

Hi Hanss.....it looks very cool. Could you translate it for me. I am not very good in VBA and I would like to know where to put in the email adresss in the code if the code should be used to send an email to other clients than the user

• ## Open a Lotus email from Excel?

(same mail is in the email/excel forum, but dont know if it is the appropriate place to put it)

Hi...... I have created this worksheet in excel and would like to create a macro, that opened an email in Lotus Notes with a predefined recipient.?

When running the code there should the pop up a new mail - ready to be written? I will not need to enclose the workbook.....

Can anyone help me?

• ## subscript out of range?

it works now after I changed Worksheets("sht") to Worksheets(sht)

• ## subscript out of range?

Now it works......thanks..... Worksheets(sht)

• ## subscript out of range?

Of course.......here you go

I think this is what you need......

• ## subscript out of range?

Could you help me out here. It returns "Subscript out of range", when it reaches the part in bold.

• ## Change Menu Caption to bold?

what a shame.....but thank you - not that important, would just look :cool:

• ## Email (Lotus Notes) spreadsheet with VBA?

This code is really, really cool.....

• ## Change Menu Caption to bold?

have created this New Menu.......Is it possible to turn the caption into bold? Have tried with the action below, but nothing happens..... Is it not possible to manipulate with that?