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]

QWD_SETUP

 

[Kenja824]

QWD-NEW-FORM

 

[Kenja824]

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.

 

[Kenja824]

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.

 

[Kenja824]

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?

 

[SkipVought]

Range.NumberFormat = "#.00"

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

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

 

[SkipVought]

You need to upload a workbook with your two modules.

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

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

 

[Kenja824]

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.

 

[SkipVought]

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

Here's your [highlight #FCE94F]problem[/highlight].

'
ActiveSheet.ListObjects.Add(xlSrcRange, Range("[b][highlight #FCE94F]$A:$J[/highlight][/b]"), , 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...

'
   Range("Table2[Gauge 1]").NumberFormat = "#.00"

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

 

[Kenja824]

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?

 

[SkipVought]

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

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

 

[SkipVought]

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

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

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

 

[Kenja824]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Please upload your raw .csv file.

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

 

[Kenja824]

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.

 

[Kenja824]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]