VBA Code Help Please

[Kenja824]

With your help, I created code to run in a document that would set everything up automatically. Put it all in a table, hide rows format other rows add formulas in columns etc...

Being sick of the privacy issues, I took a document I would receive and changed a ton of info in it. This has in turned caused some of the columns to not fill in with the formulas but you can ignore that, as they work in the real documents.

Attached is the excel file. I do not know how to attach more than one file, so I will reply twice to add the others.

The QWD-SETUP3.bas file works fairly well. You may see that it could be done much cleaner but at least it works. ) This will format cells, hide columns and add formulas to the table it creates as well.

The QWD-New-Form.bas file is the one I am trying to figure out.

Where I am having a problem is when I run the New Form code, I dont know how to make the table grow to accept all of the rows that are in the original sheet. (Keep in mind that every time I get a new file, the amount of spots listed (rows) will be different. Some are many times longer than this one. ....... However, even if the table grows to the same length, another thing I would want to do is make it so that when we print, the print range will be only what is showing after we filter it. Not sure if that part is possible or not.

Once I get this NEW FORM vba figured out to work right, I will just be adding it to the original code so one file does it all. I may have a question on that when the time comes. Hopefully not. lol

I will attach the other files....

 

[Kenja824]

I wouldnt wish to press you to get this done either way, but no matter how important it was to me, I would definitely say God comes first. Always glad to hear of someone going to Church. ) Hope its a good service.

 

[Kenja824]

I just had a thought.... Perhaps I am going about this all wrong.

Currently I have CODE1 that formats a CSV file. It hides columns, re-orders columns, colors rows, creates columns and adds formulas to columns. After running it, I save the CSV file to a macro excel file.

I should make a whole new code file now that does the same thing but to the new formatting I need, but instead of running it on the same file, I just re-open the original CSV file and run it then save it as again.

So instead of keeping it all in one file and dealing with this trouble of making the second table always appear as long as the first table, I just have these on separate files. Re-using the same CSV file for the second code.

I dont know why I never thought of that.

 

[Kenja824]

I was trying to create new code to do as I mentioned above. I ran into a problem and will end my day giving up on this one. lol

I have the following portion of code....

With ActiveSheet
If Not bHasListObject Then
'make active sheet table a Structured Table
.ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table" & iCnt + 1
With .Cells(1, 1).End(xlToRight)
'put formula in a new table column to the far right
.Offset(0, 1).Value = "Check"
.Offset(1, 1).Formula = "=IF([Number]=[ROB '#],""."",""XXX"")"
End With
End If
End With


With ActiveSheet
If Not bHasListObject Then
'make active sheet table a Structured Table
' .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table" & iCnt + 1
With .Cells(1, 2).End(xlToRight)
'put formula in a new table column to the far right
.Offset(0, 2).Value = "Check2"
.Offset(1, 2).Formula = "=IF([Number]=[ROB '#],""."",""XXX"")"
End With
End If
End With

I find that instead of inserting new columns at the end of the table as I thought it was supposed to do, it is writing over columns. Can you see what I have wrong?

 

[SkipVought]

1) Service, great. Romans 3:21-26. WOW!

2) Adding columns to your Structured Table, assuming Table1 is name...

[Table1[#Headers]].Cells(1, 1).End(xlToRight).Offset(0, 1).Value = "Your New Heading"

3) How does a Material 1 value like KJS4K-ST-S-CR3-HD58K58K-U become a Mat 1 value of XG or G?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

I haven't followed the full thread, but looking at your code, Offset is indexed from the top left of a range, starting at (0,0), so if:
With .Cells(1, 2).End(xlToRight)
takes you to the right hand column of your table, then .Offset(0,2) would leave a blank column between the table and the new entry, so it wouldn't extend the table.

I suggest adding:
.Offset(1,1).Value = .Address
to check where
With .Cells(1, 2).End(xlToRight)
is taking you to.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Kenja824]

IDS - Thanks for the input. Sorry, I forgot to say I had been messing with that area of the code trying to get it to work and just trying different things.

Skip -
1)Interesting verse. I had to read this one a couple times to get it to sink in what it was saying for some reason. If I get the gist of it, it is saying how we are saved through our faith. That everyone is the same in that we all sin. All of us do, and it is the reason he sent Jesus to us to die on the cross. So that we can be saved and sins forgiven, through our faith. ... At least that is what I am reading in it. I know this isnt a site for this discussion. I always love discussing Christianity and religion when I can though. )

2) I figured out what was happening with my columns. Sort of. In a weird way, I just had that section of the code in the wrong place. When I looked where it was placed in the original code, I moved it and it started working. I had to change the offsets back to how the original code was too. lol Working.... sort of. Im having a weird problem still. I will explain at the end here.

3) GM gives each metal a material spec. Its one long piece of code. I had a real long list of all of the codes where I could look up a code and find out what spec it was. Whether it was G (Galvanized), B (Bare), XG or XB (the same but high strength) etc... One day I got sick of looking them up and I started comparing them and found there were just simple bits in the code that told me what the spec was. So I got someone who used to work here help me make a VBA function that would automatically look for those bits and tell me what they were. I got to where I could look at the code and know, but when you get a CSV file with a thousand spots it is a pain to go through them. So now I add the function to the CSV file, and run the formula down the entire thing and I have a column giving me the spec for each spot. The function is listed near the end of these codes I am making if you want to see it. Its called "Material_Code".

-------

As for this code I attached and the problems I am having....
1) I got the columns to be created and it changes the order of all the columns. However, the column "Style/Option" should be coming out at the end and it ends up with the columns "Gundes" and the three new columns to the right of it for some reason. I can figure out why.

2) I recorded myself creating a new Table Style and then selecting that new style for my table. I got that working but it gives to problems. It should have a thin border around all cells and for some reason it doesnt keep those. Also, if I run the code on the file a second time, it errors out because the table style is already created. Is there a way to have it skip creating the table style if it already exists?

I still need to make it re-order the rows, which shouldnt be too hard. I need to decide what the order will be in which columns and such yet.
Once this is done, we will be able to run this and have everything formatted to how GM wants it. Then the worker will only need to filter it to see only the weldguns or robot numbers he wants to show and print it out as a deliverable. A problem I can see it having coming up is when he needs to print, I think he will need to keep changing the print range. One rob# may have only 10 spots. The next may have 30 spots. (as a reminder, each spot is a row). Not sure if there is anything that can be done about that or not.

I just learned as I was writing this out that there was an excel program a GM guy gave us to get this info a while back. All of this work may have been for nothing more than my learning purpose. lol I saw the program once a while back and hated it and forgot all about it. I am gonna push to still use this one. Gotta hope they dont put up a stink about it. (sigh)



Attched is the code for what I have so far

 

[SkipVought]

On 1) we'll go to eMail. Send me a COMMENT at faq766-2001. Then I will eMail you.

I'm uploading a new version of your workbook. Has sheets to replace your sheets

IMPORT: The raw .csv data
Weldgun: A query from data in the IMPORT sheet
Formats: Data used to format the columns
HeadFmt: Data to format the heading row

Module3: 3 procedures Load_form, RefreshQuery, ApplyFormats

Not sure what to do with the three GAUGE columns.

Finally, you would COPY the Weldgun sheet to a New Workbook.

This is a much more streamlined process.




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and some additional comments.

I got the columns to be created and it changes the order of all the columns.

However, I know I have had some different than others. I think it is due to using more or less info before running the program that spits out the CSV file. THey have to select a bunch of attributes I think and sometimes they dont need all of the same ones, so sometimes the columns can be different.

I would say dont expect the columns to always be in the same place or even always to be present.

So how do you square this? Don't you have too much uncertainty in order to program a solution?

However if your users ALWAYS expect certain columns in a certain order, and you can be assured that those columns are always present, this then would appear to be certain or more nearly certain.

Regarding table styles. What if you were to think in terms of having a MASTER workbook that has the columns/styles/formulas already in a table, and all you're going to do is map the .csv column data to the existing columns. Could you envision such a process?

Print Range is no problem if you make the Print Range the range of the table.

Existing program. Yes, I've acquired a bunch that in almost every case I have rewritten to make it 1) more readable for maintainability, 2) to better understand the process 3) to improve the coding. So your having done this will no doubt aid you in the long run even if you abandon your code for the existing program.

BTW, a lot of engineers know how to code, but it does not mean that their code is well designed and works well, in the case of Excel or some other application, with that application's object model. I'm sure that my code could be written better. Although I would consider my code generally good and maybe above average, I'm always open to concepts that would improve what I've done. I continue to increase my knowledge here at Tek-Tips & Eng-Tips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Hey Skip

I tried to leave a comment on that page. It keeps giving me an error. Shows a window with all kinds of info I have no idea what it is.I have an old email address I dont mind giving up here. If I posted it and you emailed me there, I could give you my real email from that one if that would work.

--------

I havent had a chance to check out your files yet. Im attaching what I have done to mine so far. It seems to run all the way through and do everything I need now. It has all of the correct columns, it sets the print area for the table and if I filter the table to show one robot number only, the print area is set to show it on one page.

The only things I cant figure out is why the STYLE/OPTION column wont come out at the far right end. (this is the only important one to me.)

Though I added borders to all the cells when I created the Table Style, it never seems to record it. The next time I run the program, there are no cell borders.

-------

The files I get are just files I get. I just have to deal with it. Mostly they are the same but I do get some that have a few different columns. The columns I need are always on them, I just dont know for sure they will be in the same position. If someone sends me one with an attribute that others never select, it will shift all the columns after that over one. Working with the Table has really helped me get around that because the code is looking for headers of columns instead of column numbers.... or letters I guess.

BTW, a lot of engineers know how to code, ---- Yeah, I would not go as far as to say I know how to code yet. I understand only a portion of what I put in this code. Most of it has come from either you or looking up how to do something and making adjustments to what I find on-line. You say you are above average? Sounds like you're being humble to me. lol If you get from the stage of "Glad I can just make it work" to "How can I make this cleaner?" You are definitely already above average. lol I used to think I was good with excel because I was fairly decent with formulas. In the last year I have learned a ton more and realized I didnt know as much as I thought and that has nothing to do with coding. lol I feel like an excel hack these days. lol Which I dont mind because it makes me want to learn more. And I do know a ton more than I did a few months ago at least. )

 

[Kenja824]

Well, I opened your file, and I have no idea what I am looking at really. Mostly at least. I see you have the format tab where you can set the column widths, but I have no idea what the H-alignment is V-alignment is. Horizontal and vertical alignment for something. lol I guess the HeadFmt tab make sense. Trying to put these things with the sSQL code? Beyond me at this point. Im thinking we are opening a whole new can of worms here. I didnt even know there was SQL code in Excel. I thought that was used for Websites to use databases or something. .... Which is definitely something I need to learn for my personal use. lol Which makes me curious. How uch do you know about building websites?

 

[SkipVought]

When you respond use a comma to delimit your address from the server and leave off the com unless something other.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

The only things I cant figure out is why the STYLE/OPTION column wont come out at the far right end. (this is the only important one to me.)

[Table1[#Headers]].Cells(1,1).End(xlToRight).Offset(0,1).Value = "Style/Option"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Not sure what you mean for the comma. This should do the job though. I think you will know what I mean here.

Kenja824, just like my user name and I use Hotmail. I will respond when I get settled in at home and give you the better address to use.



Not sure how to use that code (your last post) in this case. lol

 

[Kenja824]

As for the order of columns... I have this code in there that was given to me. I would have expected this or set the order directly. It seems to work for all of the other columns but that one. Unfortunately it will have to wait for me to play with it more. Tomorrow morning I have more important matters to deal with for another job.



Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer

arrColOrder = Array("SPOT #", "Material 1", "Material 2", "Material 3", "ROB #", "GUNDES#", "Gauge #1", "Gauge #2", "Gauge #3", "Style/Option")

counter = 1

Application.ScreenUpdating = False

For ndx = LBound(arrColOrder) To UBound(arrColOrder)

Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If

Next ndx

Application.ScreenUpdating = True

 

[SkipVought]

BTW, responded to your hotmail

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]