Format column

**shaunsul** · 05-04-2011, 02:06 PM

Hello all
Again thanks to all who have helped in the past

I have a new issue with the same process I have been working with

The code provided by tigeravatar works wonderfully.

The only issue (and I think this comes from how the log files are being generated) is that in the number in minutes for each test run comes in as a text format and not a number format. I need all information in the TTR column (Column J) to be in number format with no decimal places.

Oddly enough some of the entries are numbers while others are not. As you can see with 1300 plus entries going back in a formating by hand would be a real pain.

Can any help me with getting some VBA code that will force the J column to see the entries as numbers.

Please note I have tried creating and recording a macro and formating a column and then modifying it and adding that to the existing VBA. This does not work. I have set the column to view all as a number, which also does not work.

I am actually starting to wonder if I have stumbled onto a bug.
Attached is the file I am working with so far.

Thanks

Shaun

**snb** · 05-04-2011, 02:56 PM

Please Login or Register  to view this content.

**shaunsul** · 05-04-2011, 06:55 PM

Sadly this did not work, it is still importing the data from the individual CSV files as text, and will not change them to a number format.

**JBeaucaire** · 05-04-2011, 08:09 PM

The reason some values are numbers and some are not are because of the hidden character in some of the cells. Look at cell J3, for instance, it is actually 240(char 13).

Try adding this line of code at the end somewhere, or run it separately:

Please Login or Register  to view this content.

**keyston** · 05-05-2011, 12:54 AM

The code that you apparently got from tigeravatar worked "wonderfully" - except that it landed you with a serious problem. In looking at the code it became apparent that it was written by someone with a great deal of ingenuity, but not very much depth of knowledge of VBA. For example, instead of using the 12 line SearchString(criteria, source) function, you could use the built-in VBA function: InStr(1, source, criteria, 1) which does exactly the same thing. And in fact you do not need this function at all if you use the built-in VBA Split() function to split your csv file into lines and then into line elements, as in the following revision of your code:

Please Login or Register  to view this content.

The use of vbNewLine as a separator between log lines (instead of Chr(10)) avoids the problem pointed out by JBeaucaire of the Chr(13) that gets left behind with the code you were using.

I could not test the above code with your files, but I did test the section between the first If and the corresponding End If with a csv file I had on hand.

**snb** · 05-05-2011, 03:19 AM

or using VBCrLf and Texttocolumns:

Please Login or Register  to view this content.

**keyston** · 05-05-2011, 09:54 AM

Hi snb,

A model of compact code!!

One thing it does not do, compared to the original code, is to apply the Trim() function to all cells. However, I am not sure whether this was a requirement, a precaution or a misunderstanding of what Trim does or does not do.

**shaunsul** · 05-05-2011, 02:05 PM

Keyston and Snb,
Excellent code and an answer to prayer all the way around.
Thanks much, now I just have to figure out how to get this all to execute from a command line using powershell, populate a chart and create a JPG......LOL not asking you all to help with that....YET...I want to try to figure it out on my own first, it is the best way that I learn.
Shaun

**snb** · 05-06-2011, 03:32 AM

@Keyston,

on 'trimming'

approaching:

Please Login or Register  to view this content.

or 100% trimming:

Please Login or Register  to view this content.

**keyston** · 05-06-2011, 04:38 PM

100% trimming - code suggested by snb is:

Please Login or Register  to view this content.

Again, very compact code - but unfortunately it does not work, at least not for me. I get a compile error: "Expected function or variable", with the highlight on the left side of the second line. I tried to understand how this one-liner for trimming all cells in a range was intended to work, but I failed. And I did find a couple of web sites where it was stated explicitly that it could not be done with a one-liner - but perhaps the authors were not smart enough.

In any event, what does work for me is:

Please Login or Register  to view this content.

**shg** · 05-06-2011, 05:44 PM

Try this, after selecting the range of interest:

Please Login or Register  to view this content.

Note that this willl replace any formulas with their (trimmed) values.

**keyston** · 05-06-2011, 10:14 PM

In fact the following does work:

Please Login or Register  to view this content.

as does your code, shg, and the following shortened variation:

Please Login or Register  to view this content.

However, I do not know why, in these two cases, if I substitute [snb] for Range("snb") I get the error: Expected function or variable.

And then there are the equivalent one-liners:

Please Login or Register  to view this content.

Mind you, I have no idea how or why they work. If you have a reference to some details on this subject, I would be interested.

**snb** · 05-07-2011, 05:33 AM

You could consider a 'named range' to be an object.
You can use namedranges (or names) in an Excel-fromula, because the Excel -compiler checks whether the name you use in the formula is defined in the collection of 'names' (Ctrl-F3). If it is, then the 'name' will be replaced by it's equivalent.

By using the brackets [ and ] we trigger the same 'checking' mechanism (the brackets are an equivalent for the method 'application.Evaluate'). Using brackets is a shorthand code for Application.evaluate.

So if we do

Please Login or Register  to view this content.

we can use Range("snb") to indicate the usedrange of sheet(1)

The shorthand writing of Range("A1") is [A1]
The same can be applied to Range("snb"): [snb]
On my system [snb] = gives no error

Now the second part:
[trim(snb)] is like an Excelformula without "= "
'snb' will be recognised (evaluated) as a name range
trim will only be applied to the first cell of the range; (what happens if you use a formula instead of a arrayformula)
that's not what we intend.
so we have to indicate that the whole range is meant
we can use 2 methods: using 'if' or using index
with 'index' we can indicate which row or which column we want to use from the range: row5: index(---,5); colum 7: index(--,,7),
if we do not specify any column or row, the whole matrix will be indicated; in this way we turn the formula into an arrayformula.

So what we want can be achieved by:

Please Login or Register  to view this content.

**JBeaucaire** · 05-07-2011, 11:33 AM

I had to edit the last example to remove a couple quotes to get it to work, but other than that this is great stuff!

Please Login or Register  to view this content.

**keyston** · 05-07-2011, 08:37 PM

Many thanks, snb, for your detailed explanation, although it does not cover the If (Row(),.. of shg - but I can live without that!.

I have found out why the [snb] gave me an error. In your 100% trimming code example, which I copied and pasted, you gave the same name, snb, to the Sub. Looking through the collection of names it was understandably the Sub that was found first!

**snb** · 05-08-2011, 05:34 AM

@Jerry,

Thanks for correcting my code. I had too little patience to test that code, but it also illustrates why I prefer the shorthand code. Because I'm not that good in counting (

) most of the time in this kind of case I build a string like this in this way: Instead of " I use ~ and replace them by chr(34).

Please Login or Register  to view this content.

But on second thought we don't even need this:

Please Login or Register  to view this content.

@keyston
Nice you found out yourself. I wouldn't have been able to detect that from a distance. But I also learned that you should avoid names for named ranges that have 'reserved names', be it by Excel or by yourself introducing macro/functions etc.

The code shg gave:

Please Login or Register  to view this content.

In fact he gives the 'longhand' version of 'evaluate':
He uses 'if( , )' to gain the arrayformula result. But as the 'if'-construction is purely introduced to convert the formula into an arrayformula a condition must be found that is always true for all cells in the range. Shg chooses row(), but it could also be column() or 12 or you name what.
In such a case I'd prefer the testing condition 'True', because that's the least confusing one.

For the whole usedrange:

Please Login or Register  to view this content.

To illustrate that a namedrange is an 'object', you can achieve the same result using an object-variable, for instance:

Please Login or Register  to view this content.

Format column

LinkBack

Thread Tools

Rate This Thread

Display

Format column

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Formating a stubborn column the way I want

Re: Format column

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions