#46
|
||||
|
||||
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: Quote:
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: Code:
x = MyFunction(Cells("E2"), 3.14159) 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: Code:
Selection.TextToColumns Destination = Range("E2") DataType = xlFixedWidth FieldInfo = Array(Array(0, 9), Array(3, 3)) Code:
Selection.TextToColumns Destination:=Range("E2"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(3, 3)) Code:
Selection.TextToColumns Destination:=Range("E2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 9), Array(3, 3)) |
#47
|
|||
|
|||
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! Last edited by EC37; 06-24-2014 at 06:01 AM. Reason: thought I was done, but not quite! |
#48
|
||||
|
||||
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. |
#49
|
|||
|
|||
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 |
#50
|
|||
|
|||
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. Last edited by EC37; 06-24-2014 at 10:43 AM. Reason: P.S. |
#51
|
||||
|
||||
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: Code:
Set owb = ThisWorkbook Set owss = owb.Worksheets Set owsFrom = owss("Worksheet name #1") Set owsTo = owss("Worksheet name #2") 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: Code:
rz = Range("L1").End(xlDown).Row Range("A1:L" & rz).Select Selection.EntireColumn.Autofit Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1" 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: Code:
rz = Range("L1").End(xlDown).Row Set org = Range("A1:L" & rz) org.EntireColumn.Autofit org.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1" 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: Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1" Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=RC2=1" Code:
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority But then you go on to work with the first conditional format: Code:
With Selection.FormatConditions(1).Font . . . Selection.FormatConditions(1).StopIfTrue = False 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? |
#52
|
|||
|
|||
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! |
#53
|
|||
|
|||
I've got it!
I set everything up properly this time. I went with the following: Code:
cc = Range("L1").End(xlDown).Row Set org = Range("A2:L" & cc) org.EntireColumn.autofit org.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1" org.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With org.FormatConditions(1).Font .Strikethrough = False .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.349986266670736 End With org.FormatConditions(1).StopIfTrue = False 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. Last edited by EC37; 06-25-2014 at 07:54 AM. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formatting contents after Tab of continuous lines or formatting specific area of word | pawii | Word | 1 | 05-12-2014 05:24 AM |
macros | stebrownsword | Word VBA | 0 | 08-28-2013 01:16 AM |
How to do Formatting Using Macros | anju16saini | Word VBA | 1 | 03-11-2013 04:15 AM |
Formatting with macros | WaltR | Word VBA | 8 | 05-15-2012 06:28 PM |
Macros | nore | Outlook | 0 | 06-01-2011 04:39 PM |