Posts by hgus393

Hello all,
I am sorry for the general topic of this thread - what is specifically bugging me is the following:

I am selecting a range by the following:

Sub data()
    Range(Selection, Selection.End(xlDown)).Select
End Sub

The problem is that sometimes the file that I am running this macro on has null values (ie nothing) which leads to an erroneous selection ie it stops eventhough there is alot more cells with values after the null value. I cannot choose the entire column as the entire range! Does anybody have a clue how to get by this?
//Robert

Re: Messagebox sum

:thanx: :thanx: :thanx:
Thank you very very much to the both of you!!
//Robert

Hello everybody,

I am a newbie to Vba and I wonder if someone can help me with a little problem? I would like a message box that will display the sum of all the items in a selection I have come thus far:

Sub SumAmount()


'
Dim summ As Range
Set summ = Selection
For Each summ In Selection
'?
'?
Next summ
Msgbox summ
Set summ = Nothing
End Sub

Happy for any help :thanx:

Re: Naming ranges

Thank you all for your help, I have taken the same approach with the same range name as sheet name but I manipulate the data from Access so that it delivers the date field as text ie 20060120 works like a charm.

Again thank you all!

:thanx:
Robert

Re: Naming ranges

Quote from Aaron Blood

So... I think what I'm hearing is that for each sheet you want a named range; perhaps we'll call it "MyRange". And you have maybe a macro that always operates on "MyRange" but you maybe want "MyRange" to represent a different sized range for each sheet. Is that it?

I've done stuff like that before. If you want to create a "Sheet Specific" named range, you have to assign the name and use the sheet name as a prequalifier. Like so:

Code

ActiveWorkbook.Names.Add Name:="Sheet1!MyRange", RefersTo:="=Sheet1!A1"

However, I find that this type of naming is required only in the rarest of occasions. I would think very carefully before proceeding down that path... I guess if you have the same formula (maybe a lookup) that keys on MyRange it might be useful to have that be a different size for each sheet.

Edit: But remember, the definition of MyRange on the sheet that you enter the lookup formula on is going to be the only definition of MyRange that can be seen by formulas on that sheet. You won't be able to have a formula on a sheet and have MyRange mean different things to different formulas on the same sheet. Am I making sense? Multiple instances of the same name can also be confusing and misunderstood by those trying to audit your work.

Also worth noting that it's certainly not difficult to add sheet specific named ranges manually. Just need that sheetname prequalifier in the name.

Display More

Well yes and no, what I am trying to do is to use the cell where the sum is and transport it to a seperate sheet by using the named range. The thing is that the file has no other useful identifier than the date that I can use for the summed cell. I know that this is not a lot of work to manually change the name of a range but I have about 100 workbooks where I have to repeat the procedure so I am trying my hardest to do as little work as possible ;o)

Cheers

Robert

Re: Naming ranges

Ok let me explain a bit more.

The workbook contains one month of worksheets ie 30 or 31 sheets per month. The summed amount in every sheet should be represented in a summary worksheet with the date and the result of the summed amount for that date. So what I am trying to do is to create a VBA that will post the date and the summed amount in this summary sheet. The problem is that the file is dynamic in the amount of rows per day so the cell address will unlikely to be the same the next day which leaves out using a simple reference formula. I might be going about this in an erroneous fashion but my VBA skills are limited so I am learning by doing....

Robert

Re: Naming ranges

:thanx: Badger!

But as you believed there are some naming issues. A range can not have the same format as a date, any ideas how to get round this? I name the sheet by looking at a field that always has the same address but updates with regards to the date.

Cheers

Robert

Re: Naming ranges

Right, sorry for being a bit fuzzy!

What I have is a file that is generated everyday with new figures and this new file is moved to Excel from Access. What I do then is to sum a column in a cell whose address changes due to the amount of rows in the file, this cell where the sum is, is what I would like to have the same name as the worksheet. Say if for example the sheetname is 2006-01-18 then the cell where the sum is should also have the range name 2006-01-18.

Robert

Hi all,

I wonder if anybody can help me with this?

I'm trying to name a range by VBA so that the name of the range corresponds to the name of the worksheet does anybody have any idea how to do this?

Thanx

Robert

Re: Inserting right format for a date

:thanx: :thanx:
and thank u

Robert

Hello everyone,

Does anybody have an idea for VBA code for the following problem:

I am working with another system that for some reason leaves out the dash between the year, month and day, this of course causes some problems when I want to do some date calculations. So if the date is for example:
20060116 how can I convert this to 2006-01-16 by VBA?

Robert

Re: Derivative

Right, Thank you all very much, especially you Andy!

Cheers :thanx:

Hello,

Wonders if anybody knows if there is a function (formual) for finding the first derivative of a function?

:thanx:

Re: Printing Macros to word

Quote from royUK

Do you want to print your macro code?

Ah! Never mind I found the print menu for the whole project! Thank you for answering!

Hello everyone,

Is there somebody who knows if there is a way to transfer macros :duh: (text only) to word by using VBA?
:question:

Re: Text to Columns

I will give that a try!
Thx

Hello,

I have a problem when I import text files. I have created a procedure that extracts the information from a text file. That procedure works fine but when I want to convert the Text to Columns there is no suitable delimitator. The text file has a similar appearance as below:
Account Acount name
123 Abc Def Ghi

The problem is that I want to have the account name as one column but as there are spaces between the full account name I cannot use Space as a delimitator. I cannot use Fixed width as the information is dynamic (ie account number is incrementing). However, the account name cannot be longer than 22 characters (including spaces). Does anyone have an idea how to do this that is not by manually intervention?

Re: Correcting dates

Thank you very much!
:thanx:

Hellu,

I wonder if someone has any idea of how I can solve this little problem. I work against a main frame computer and this main fram delivers dates in the following format: 20051121.
Does anyone know how to convert this date into a standard date format i.e. 2005-11-21 through VBA?

:thanx:

Re: Interpolation values from a curve data

Hello,

try this site, it has many mathematical and financial functions http://www.vbnumericalmethods.…le=Linear%20Interpolation