VBA code help please.

[Kenja824]

I have a column with metal thicknesses in it. Unfortunately GM now has some product that is not constant and depending where it is welded the guage can be different, so they are adding some info to another column and in the usual column it will show 0.

If it matters, this is in a table
Columns headers where guage should be = "Guage 1", "Guage 2" & "Guage 3"
In a varying part, these will show 0 and there will be info under header "GM_WELD_COMMENT"
Information in GM_WELD_COMMENT will be something like this..... <VT>VT2-2.175</VT>

I have a .bas file with code that does a lot of arranging columns, formatting columns and adding formulas to set up an excel file I receive from another department. I get the excel file from them and run this .bas file and it sets everything up to make my job easier. I am attaching the .bas file in case it helps. Some of you helped me put chunks of this together in the past.

What I want to do is have this file look at the cells under the Guage 1, Guage 2 and Guage 3 columns and if it has a number larger than 0, it ignores it. If the cell has "0" it will replace it with the extracted thickness from the text in the same row under column GM_WELD_COMMENT.

1) The guage is the numbers between the "-" and the "</"
2) It would help if it also rounds that number to 2 places, but that's not important.

I hope you dont need the actual excel file for this as I would have to take an old one and change a bunch of info in it to protect GM's privacy issues. But if necessary, I can do that.

I was gonna show what I was trying to get to work but decided not to make you laugh too hard.

If you can help, it would be appreciated.

 

[SkipVought]

Hi,

The guage is the numbers between the "-" and the "</"

Function GetGauge(rng As String) As Single
    Dim tmp, i
    
    tmp = Split(rng, "-")(1)
    
    i = InStr(tmp, "</")
    
    GetGauge = Left(tmp, i - 1)
End Function

You can use this on your spreadsheet of in code where you reference the cell with the embedded thickness

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

 

[Kenja824]

Hi Skip

Thanks for the help. I can put this in that file and it allows me to use it as a function to extract the guage, but what I am looking to do is keep this .bas file with all the code fully automatic. I get the excel file from the other department and all I have to do is import this file and run it.

I cant seem to come up with a code that says.... if cell in column "GUAGE 1" is larger that 0, do nothing, but if the cell = 0, replace the 0 with "GetGuage".

And for that to work, I think the GetGuage function you gave me would need to automatically extract from the cell in the same row it is in, but under the "GM_WELD_COMMENT" column.

 

[SkipVought]

If [GUAGE 1] = 0 Then [GUAGE 1] = GetGauge(Ref)

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

 

[Kenja824]

Hey Skip

Well.... I must be doing something wrong.

Every time I run this file, it stops at this IF statement with a.... run-time error 13 Type Mismatch


My guess is that it has something to do with me not knowing where to place it correctly or something. That tends to be a part of most of my problems with code. lol

 

[SkipVought]

Please post your actual code. My notation [GUAGE 1] is a shorthand for a valid Excel reference.

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

 

[robyengIT]

GUAGE or GAUGE (A and U postponed) ??

 

[SkipVought]

Rob, the user has Gauge misspelled in his workbook, hence [GUAGE 1] in actuality.

BTW, Ken, on your Weldgun Info Sheet is where they are misspelled.

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

 

[Kenja824]

LOL Yeeeaahhhh..... For some reason I have always naturally wanted to spell it "GUAGE" instead of "GAUGE". I dont have any idea why. lol But in looking at the code I had, I only needed to fix it three times in only one line and that had the " ' " in front of the line, so in the end, this was not causing a problem anywhere other than causing confusion here. lol

Skip, this is gonna sound stupid but I look at the post about it being misspelled in the Weldgun Info Sheet and I cant think of what you mean by that. "Weldgun Info Sheet" sounds so familiar but I cant find anything that has that name. I must have changed the name of something at one point and dont remember it now or something. lol

You will all have to excuse me. Its Monday and its me. This kind of ridiculousness comes natural for me. lol


Skip,
Your other comment made me wonder if that was the problem. It made me wonder if excel was getting GAUGE 1 confused because in our excel files GAUGE 1 is just the header title for a column. I tried messing around and placing quotations around the name and that killed the error from happening, but I think it killed the whole function as well because it didnt seem to do anything. It would just run through the entire macro and do everything else but it wouldnt fix the problem at the end with the new gauge.

I am attaching a file that already has the module saved into it with all the code. The function you gave me was placed at line 601. The code to call the function finally ended up at 569 but I was trying it in different places hoping to get lucky with placement. lol

You will see in the excel file that it is not formatted at all and when you run this module it will do all the formatting for me. There are three columns, Gauge 1, Gauge 2 & Gauge 3 that have all the metal thicknesses in the cells. Each column has some cells that are "0". Where there is a zero, to the right somewhere there will be a column "GM_WELD_COMMENT" that will have some code in it that will look like "<VT>VT1-1.598</VT>" Only the numbers between - and </ need to be extracted and put in place of the "0" in the gauge column. You will find I tried connecting the code you gave me to that column. I probably screwed that up as well. lol This code is hard enough for me to grasp for some reason but because I work on it for a bit then work on other things for a couple months and then have to go back to it, it has a harder time sticking.

I do appreciate the help with this everyone.

 

[Kenja824]

Oh yeah, if possible, when it replaces the "0", I need the gauge to be rounded to 2 places.

This is not important at all. Once this is done, we only use this info to look at and manually type the info into another program all together. This formatting just makes it much easier to follow it and do the typing. Myself I will round this off in my head faster than I can type it, but there are others who do this as well and I am just trying to head off problems with a new person who isnt used to rounding numbers.

Thanks again

 

[SkipVought]

Okay, remove the [gauge 1]... statement in your QWD_SETUP procedure.

On your sheet, make the table a Structured Table named tQWD.

Add this procedure that loops through the Gauge 1 data...

Sub ReplaceZerosInGauge1()
    Dim r As Range
    
    For Each r In [tQWD[Gauge 1]]
        With r
            If .Value = 0 Then
                .NumberFormat = "#.00"
                .Value = GetGauge(Intersect(.EntireRow, [tQWD[GM_WELD_COMMENT]]))
            End If
        End With
    Next

End Sub

You can call this in your QWD_SETUP procedure.

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

 

[Kenja824]

Awesome. Thanks Skip

Ran into a small problem when I set it up to run for Gauge 2 and Gauge 3 because it didnt like that the Gauge 3 column had empty cells. Due to some welds are for 2 metals and some are for 3 metal stack ups. We just added .... If .Value <> "" Then .... in with the code and it worked fine.

I do appreciate the help from everyone here.

Gonna have to come up with a new project soon. ;oP