VBA Code Help Please
VBA Code Help Please
(OP)
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....
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.
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....





RE: VBA Code Help Please
RE: VBA Code Help Please
RE: VBA Code Help Please
RE: VBA Code Help Please
'Creates a Table
Range("A1:J1").Select
Range("A1").Activate
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$J"), , xlYes).Name = _
"Table2"
Range("Table2[#All]").Select
Originally I had a full range listed and so it created the table to that range. When I tried it like this, with just A:J listed in the third line, it gave me an unending table. I just cant figure out how to get the table to end when the info ends.
RE: VBA Code Help Please
After this runs, the Guage columns need to have 2 digits to the numbers.
2 needs to be 2.00
1.4 needs to be 1.40
How do I get them to read with 2 places after the period?
RE: VBA Code Help Please
I've been out and about all day. Will look at your new code/data this evening.
Skip,
for a NUance!
RE: VBA Code Help Please
The my downloader wants to RUN these files rather than save them.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
This one was saved after the QWD-SETUP was already ran. You will notice there are a bunch of unfinished or unformatted cells at the top. This is only because the file was already missing info or had the wrong info in certain cells of a column. I didnt delete these ahead of time because I need the New-Form code to work around this rather than erroring out.
Not sure where to put the Range.NumberFormat = "#.00" code you mentioned above. I tried a few things but they didnt work so I am figuring I didnt place it right.
RE: VBA Code Help Please
Here's your problem.
CODE
' ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$J"), , xlYes).Name = _ "Table2"You defined the table as ALL ROWS in columns A:J. Your table cannot get any more rows than that!
Suppose you describe to me the process of how rows will be added to the table over time, forgetting this process. So you start off with this 650 rows of data and 1 row of headings. What might get added to row 652 under what circumstances? Where does this data come from?
BTW, on the number format, for instance...
CODE
' Range("Table2[Gauge 1]").NumberFormat = "#.00"Skip,
for a NUance!
RE: VBA Code Help Please
Not knowing how large or small it will be and not knowing how large one might be in the future, I sort of need this to make the table according to how many rows are currently in the file. If there are 500 spots, then it will make the table 500 rows to fit all of the data in the table. If it has 2000 spots, then it will make the table 2000 spots.
There is no real worry of having spots added to it because if they do add spots, they should be giving me a new updated file and I will run this code on the new file.
Originally I had it defining something like 7 rows and it wasnt enough, obviously. lol So not knowing how to make it adjust accordingly, I just tried the A:J, hoping without a number it would stop making rows when there was no data to add. I didnt think it would work but I was tossing in a hail-Mary and hoping for the miracle.
......
I see what you were saying about the #.00 code now. The problem I see however is that it is currently a number value and this would just make it a 2 place value. However, when I use the concatenate to get 2.00~G (Lines 111 thru 115 in my NEW-FORM code, it will change it back to text and it ends up 2~G. I still need it to say 2.00 in the concatenate results. I am going to mess with it and see if I can add that code in the concatenate code some how. Maybe that can be done so the concatenate function will take it as a two place decimal.
Or am I seeing it completely wrong?
RE: VBA Code Help Please
CODE
Range("Table2[Gauge 1]").Value = Format(Range("Table2[Gauge 1]").Value, "#.00") & "-G"Does that relate to over 3500 rows?
What I am trying to determine is does your ongoing process for any one excel table involve adding additional data and if so what is that data source.
Skip,
for a NUance!
RE: VBA Code Help Please
No spots or rows will be added to a file. Or should ever be. If any spots are added, I will get a new file altogether. There is always a possibility of realizing one spot is missing from the info and deciding to manually add it, but if that rare case comes up, I can always take the original document they gave us and add it and re-run this code on it. (When I run this, I always save it from a CSV file to a Macro excel file.)
I was looking up how to format it and found this code worked to a point...
ActiveCell.FormulaR1C1 = "=TEXT(Table1[@[Gauge 1]],""0.00"")&""~""&Table1[@[Matl 1]]"
However, there was one problem that came up. There will always be a MATL1 and MATL2, and always a GUAGE1 and GUAGE2, but the MATL3 and GUAGE3 is hit or miss whether it will exist. Often the cells in these columns are blank. But when I used my code, it still placed a "0.00~" in the cell. I would need it to stay blank.
I will try your code. Maybe that will be different. I dont expect it will though. I think I may have to add an IF function to this some how.
Once again, as always, I am very appreciative of all the help.
RE: VBA Code Help Please
Am I correct in assuming that the .csv column structure is fixed?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
I would say dont expect the columns to always be in the same place or even always to be present.
RE: VBA Code Help Please
Short of that, I'd create a MASTER workbook.
IMPORT the .csv data.
Use a query to grab the columns you want on the weld sheet, that can simply be refreshed once new data has been imported. This would be a Structured Table containing all the formatting (done one time) and formulas (done one time)
Once you import, refresh the query and change whatever columns need to be changed, then SaveAs to a new file for your production use. The MASTER file remains for the next .csv.
This would be a very simple and small process.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
The original CSV file I receive is attached to the original post. It has no VBA code in it yet. The VBA files are in the first two replies.
RE: VBA Code Help Please
RE: VBA Code Help Please
Gonna try to get you a sample MASTER with what you've provided. But getting ready for church now. Later today perhaps.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
RE: VBA Code Help Please
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.
RE: VBA Code Help Please
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?
RE: VBA Code Help Please
2) Adding columns to your Structured Table, assuming Table1 is name...
CODE
3) How does a Material 1 value like KJS4K-ST-S-CR3-HD58K58K-U become a Mat 1 value of XG or G?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
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/
RE: VBA Code Help Please
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
RE: VBA Code Help Please
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,
for a NUance!
RE: VBA Code Help Please
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,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
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.
RE: VBA Code Help Please
RE: VBA Code Help Please
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
CODE
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code Help Please
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
RE: VBA Code Help Please
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
RE: VBA Code Help Please
Skip,
Just traded in my OLD subtlety...
for a NUance!