Oh, I don't want you to give up on TextToColumns; I just didn't want to talk about it much until we'd covered some more basic things. Maybe I still don't; I'm thinking.



But you're pretty insistent, even to the extent of deleting your previous posts so I couldn't be distracted by them. You may be biting off more than you can chew just yet, but I gotta respect your determination, so let's give it a try. Start here: Do you have those links I gave you? One of them, this one, is for the objects, properties, methods and events (and other stuff) available to VBA in the Excel application. That's where you'll find official documentation of TextToColumns.

Now, the problem with this link is that it starts with just a list of objects: Worksheets, Error, Hyperlinks, Comments and tons of stuff that I never heard of. None of them are named "TextToColumns". If you ask that page to do a search for "TextToColumns", you'll find—at least I find—lots of articles that mention TextToColumns, but they vary in quality and sometimes I can spend all day trying to understand an article only to realize that it's for Visual Basic Studio or written for C# or something. Very frustrating. Nevertheless, a search is sometimes your only recourse.

But I won't make you search for the relevant article. (I'll just make you read it, once we find it.) I happen to know that TextToColumns is a "method" of the Range object. So start here: Go to that link, and scroll down until you find the Range object mentioned. Click on that. Don't click on the Ranges object, plural; we want the singular.

(Don't worry about which list of objects to use; the left and right are both the same. At least, I've never noticed any difference.)

Once you're there you'll see a choice of four links: "Range Object (Excel)", "Range Members (Excel)", "Properties" and "Methods". You should at least read the "Range Object (Excel)" article; it's short and gives background. But for TextToColumns I recommend you click on "Range Members (Excel)" and then look through the resulting list of Methods in the first section. They're alphabetically arranged, so you won't have any difficulty finding TextToColumns.

The resulting page shows the arguments you use to control the TextToColumns method and a very little about what it does. But it's the right place to start to understand how to do TextToColumns in VBA.

I'm not abandoning you here; I'll still answer questions, either about the documentation itself or about your attempts to make TextToColumns work in your program. But make as much sense as you can of that article, to start with. You seem to be pretty self-reliant, so I'm sure it'll help you some. Then ask some more.

...And as for the questions you already asked:The short answer is "no, it's a method". But maybe that's not very helpful at this early stage; I didn't know what "method" meant, when I was first starting out.

I gather you know the difference between a subroutine and a function. But both of them are alike in one way: They're available to use anywhere; you can give them any arguments and they'll work as designed. A method, on the other hand, isn't a general routine that you can call like this:Instead it's a subroutine or function that works only for one particular object—in fact the subroutine exists only within that one object, it was defined as part of that object.

To answer another part of your question, no; the TextToColumns method is more like a subroutine than a function, because it doesn't (so far as I know) return a result, it just performs the action.

As for your code:...there are probably more corrections to make, but it looks to me like you're on the right track but you need a syntax change. Assuming all these arguments are about right—I'm not assuming that, we'll have to look at them in detail and I haven't yet, but assuming—then the statement would look more like this:That's a long statement; it's better to use the continuation character like this:I'm not saying this is perfect, just that the syntax is more what you need. This looks like a function or subroutine, in that you call TextToColumns and provide the arguments—named arguments, in this case, rather than positional. That's why you use ":=" instead of just "=".

OK- so, I have the article you pointed me to and I will read through it. First, of course, I read what you responded with. So here is my questions about the long statement and the continuation character...

When I originally started to see how to perform TextToColumns, I first recorded my actions with record macro. It appeared exactly as you have it above (with the ":="'s and the "_", etc. However, when I copied what the recorded macro provided, and pasted into the code I am building for this export- VBA would get mad at the ":=" and error. If I remember correctly it said something along the lines of 'End statement expected' or something- I don't quite recall.
I've just attempted to recreate that error, and re-ran the macro... and, well, now I feel like an idiot. Because it did exactly what I wanted it to do- and that was perform the TextToColumns as I have wanted it to do all along...... *scratching my head*.

I can only imagine it was mad at me before because I wasn't selecting the data correctly. Damn, had I just plugged that in after I figured out the selection piece (because, the record macro was selecting E2:E264, and I wanted it to select E2 and then End(xlDown). So, that's what was wrong with my statement all along..)
Now, I just need to do that to 2 more columns, and then format all 3 to ddd, mmm dd- which I just did, and that is now all set.

I think the only thing that is left at this point in time is now including Conditional Formatting. Then, my export is officially complete!

I hate it when that happens; it doesn't work, later I try it again and it does work and I don't know why. I can't really trust it after that, until I figure out what I did wrong the first time.

If I'm reading you right, you think what went wrong is trying to do the End(xlDown) and using that for your TextToColumns. I don't think that can be it; there may have been something wrong with that range, but not that you used End(xlDown) to select it. The macro used a hard-coded range because the macro recorder is stupid—necessarily stupid, I mean, not a fault of the designers. But when you're writing your own code you can use the best way to select your range, and End(xlDown) sounds like the best way to me. If you still have the code that it objected to, post it and I'll take a look at it.

As for the rest — a triumphant grin while I lean back and put my hands behind my head — I love it when somebody learns something. Looks like you're well on your way. Holler again, any time.

Oh, and as for conditional formatting, post a question over on the other forum. Chances are someone there will beat me to an answer; I use conditional formatting some, but I get the impression there are others who know it better.

Hooray!

but yes, I think it had to do with selecting the cells because this was the problem originally before you decided not to persue showing me TextToColumns

Set cz = Range("E2").End(xlDown)
SelectRange = cz
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))
TrailingMinusNumbers = True

So multiple problems. The setup to select the cells and the way the statement was set up.

What say ye 'ol wise one? haha

You're not quite done with me yet.

Conditional Formatting: I've attempted, and so far, failed. I threw the statement in after a simple Autofit statement I plugged in, and just before the section that starts the definition of items pertaining to Grouping. I'll include a couple of rows before and after so you can see where I've inserted it. But just so you know, by me inserting this statement it did the following:
It formatted only certain cells that met the criteria (criteria: =$B2=1). For whatever reason, it left some rows unformatted even though they met the criteria.


Here is the Code:

Cells.Select
Cells.EntireColumn.autofit
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Strikethrough = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
Selection.FormatConditions(1).StopIfTrue = False
rz = Range("L1").End(xlDown).Row
Dim rStart, rEnd
rEnd = 1

And just so you are aware- I retrieved the above in red by recording my actions, and then inserted it into the equation.

P.S. - I think I know what part of the problem is: It is Applying the formula to the ENTIRE spreadsheet (=$1:$1048576). When it should only be applying to =$A2:$L264. Or, perhaps I could assign the "Applies to" to be something similar to what we used to color our Phase/Sub-Phase lines:

Set cz = Range("A1").End(xlDown)
For jr = 2 To cz.Row
Set org = Range("A" & jr & ":L" & jr)

Just not sure how i would incorporate it into Conditional Formatting.

Well, here are a number of disconnected comments. I don't expect they're exhaustive; there's probably more to talk about. But they're what I see off the top of my head:

1) You say it applied the conditional formatting to the whole spreadsheet. I can tell that from here: You applied it to Selection, and you set the selection to Cells.

Here's one of those fundamentals I want you to understand: When you name an object, if you don't name its parent you subject yourself to Excel's notions of defaults. As long as you know what the defaults are, that's ok; you're doing it with your eyes open.

Cells, for example, means all the cells in a particular worksheet. (That is, Cells by itself means that. I usually specify a particular cell by referring to, eg, Cells(2, 5); that means the cell in row 2, column 5, ie E2.) But which worksheet? I believe the default is ActiveWorksheet, though someone else here may contradict me. I say "I believe" because in practice I almost always specify.

ActiveWorksheet, fine—but of which workbook? Again, I believe it's ActiveWorkbook, though it may be ThisWorkbook.

So in my own programs I almost always start out like this:...and so on, making sure that from then on instead of saying Cells I can say owsFrom.Cells or owsTo.Cells and not worry about whether I've munged up something. For example, if I get in the habit of saying just Cells, and then I do something that changes the active worksheet, my program will happily go on doing its thing to the new worksheet, which may make me very unhappy...or, worse, I may not notice until later.

Anyway, back to the subject: You said "Cells.Select", which set the selection to all the cells in the active worksheet, and then started working with Selection.FormatConditions. So naturally what you did applied to the whole worksheet. If you wanted to set the formatting for just A1:L<whatever>, then set rz before you do the formatting:...and so on.

By the way, I wouldn't use Select, either; I'd set a variable. That way I can't accidentally change the Selection and have it change the way my program works. Like this:...and so on.

2) I don't think the problem is that you selected the whole worksheet for your conditional formatting. Oh, it was a mistake, but only in the sense that you didn't mean to do it; the conditional format should still have worked. Must be something else. You say it worked for only some rows, so let's take a look at the statements you used. First:I'm not sure how this should work, because I never use A1 notation. I would have used R1C1 notation, and the same statement might have looked like this:R1C1 notation expresses cells in the form "RnCn" where the two numbers are row and column respectively. So "RC2" means "this row, column 2"; whereas "$B2" means "second column, and the row that is 2 in relation to this row" (whatever this row is at the moment). I don't know how that works in conditional formatting.

The first object is Selection, and of course you know what that is. FormatConditions is a collection of FormatCondition objects belonging to the current Selection. ("A collection" means there may be more than one of them.) When you refer to FormatConditions(n), you're saying you want the nth one in the collection; when you specify FormatConditions.Count, that means the number of items in the collection, ie the last one. So whatever SetFirstPriority is, you're doing it to the last conditional format in the Selection, ie in the worksheet.

But then you go on to work with the first conditional format:If there's only one FormatCondition object in the whole sheet then 1 and FormatConditions.Count are the same. But I don't know that it's true. That may be part of your problem. It's definitely a problem in the sense that assuming they're the same will leap up and bite you unexpectedly one day; whether it's already done so I don't know yet.

To investigate why your conditional formatting wasn't applied to all the relevant rows, I suggest modifying your code match your new understanding...whatever new understanding I've managed to convey to you :-)...and then, if it still doesn't work, I'll need to know exactly which rows didn't work. I'll probably need to see the worksheet itself, to be sure; can you upload it, or are there classification issues?

I started to read this last night from home, and immediately thought "I didn't select ALL the cells!" .. but looking at it more closely this morning, I clearly did. While I was intending to select all cells in order to autofit the columns, like you siad, my very next statement for conditional formatting started with 'Selection.FormatConditions'... So it was working off of the whole worksheet.

I received these statements simply by 'recording' my actions with record macro- per usual. Then I try to apply it to my master TimelineExport code (by the way, I even took my code and copy/pasted it into a NotePad for safe keeping so I don't screw it up some day . Especially because I had it 100% working smoothly, and now I'm trying to incorpoarte one more thing to it- so I figured I would save the GOOD version somewhere safe!).

Anywho, yes, with my new understanding I will now try to apply to the code and see if it works. I'll let you know how it turns out!

I've got it!

I set everything up properly this time. I went with the following:

I tried your "RC2=1" suggestion, but it did not recognize it.
Anywho- You'll see that I had to specify the Range for org to be A2 rather than A1. Somehow, in doing this, it was affecting the results of the conditional formatting. I troubleshooted by running the macro, and then referring to the Conditional Formatting- as if I intended to set it up manually- and reviewed what the macro did exactly.
It set it up just as we told it to via code. So the Rule appeared as:
Formula: =$B2=1 | Applies to: =$A$1:$L$264 (or L1.End(xlDown)).

But, when compared to how I recall setting it up previously- I was selecting only the area containing the information- i.e. less the header row. So it should have appeared as =$A$2:$L$264.
I manually edited it to appear as such to see if it would correct the formatting- but it didn't. I opened up the rules again to have another look, and noticed, by changing it to $A$2, it then changed my Formula to =$B3=1. So, I edited it back to $B2, hit apply and presto magnifico- it worked.

So, I started over. Pasted the timeline back into Excel, and reran the macro with my new edits- changing org to be Range("A2:L" & cc). And it works perfectly now.

I literally tried changing things several times before i even thought to just review what the macro set up for the conditional formatting rule and make sure it appears exactly as I expected it to. Troubleshooting for myself, helps me to understand what I'm doing wrong- unless I am stumped and then I must call on the master known as Bob Bridges!

Thanks again. I think you may be able to finally set our 4 page 50+ post thread to 'SOLVED' lol.