INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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. clown 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....

RE: VBA Code Help Please

(OP)
I just realize I could have just added them to the excel file before I loaded it. Sorry. Dont know where my brain is this week.

RE: VBA Code Help Please

(OP)
I have figured out that this bit of the code is the key. Yeah I know. Obvious to you. lol

'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

(OP)
Sorry to keep adding to this, but I found another thing I dont know how to fix.

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

Range.NumberFormat = "#.00"

I've been out and about all day. Will look at your new code/data this evening.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

You need to upload a workbook with your two modules.

The my downloader wants to RUN these files rather than save them.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
Here ya go Skip

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

Quote:

I dont know how to make the table grow to accept all of the rows that are in the original sheet.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
Its not that more rows will be added. What happens is that every different job I do I am given this file unformatted. Like the one I attached originally. I will have no idea how many weld spots will be in a job. I am currently working on one that has over 3500 spots.

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

So is what you're saying that the Gauge columns are strictly informational and will never be used to do math?

CODE

Range("Table2[Gauge 1]").Value = Format(Range("Table2[Gauge 1]").Value, "#.00") & "-G" 

Quote:

I am currently working on one that has over 3500 spots.
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
Each spot gets its own row. All of the info across an entire row pertains to the spot number listed in that row.

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. clown

RE: VBA Code Help Please

Yes, use an IF() & ISBLANK().

Am I correct in assuming that the .csv column structure is fixed?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
Not positive what you mean by fixed. The order of the columns? Not necessarily. I checked into this and if they run the program correctly, it should come out the same every time. All the same columns and in the same order. 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.

RE: VBA Code Help Please

I always tried to get to the source data that the IT guys use to gen the .csv. But I was an IT guy and knew some tricks. Often the corporate db is rehosted by another db (my experience at LTV, Northrup Grumman, Lockheed Martin, Bell Helicopter) which may have day-old or hours-old data.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

Please upload your raw .csv file.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
Hi Skip

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

(OP)
Actually let me clarify that. In the original post is the original file I can give you. Unfortunately I am not allowed to put my real file on here due to GMs privacy rules. Not willing to gamble my job on the slightest chance someone from GM finds this, I had to change a lot of info.

RE: VBA Code Help Please

Surely do not want that.

Gonna try to get you a sample MASTER with what you've provided. But getting ready for church now. Later today perhaps.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
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. clown Hope its a good service.

RE: VBA Code Help Please

(OP)
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.

RE: VBA Code Help Please

(OP)
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?

RE: VBA Code Help Please

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

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

CODE

[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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

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/

RE: VBA Code Help Please

(OP)
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. clown

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

On 1) we'll go to eMail. Send me a COMMENT at FAQ766-2001: Make My Code Run Faster. 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

...and some additional comments.

Quote:

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
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. clown

RE: VBA Code Help Please

(OP)
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?

RE: VBA Code Help Please

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

Quote:

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.)

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code Help Please

(OP)
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

RE: VBA Code Help Please

(OP)
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

RE: VBA Code Help Please

BTW, responded to your hotmail

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close