×
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!
  • Students Click Here

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

Students Click Here

Jobs

VBA code help please.

VBA code help please.

VBA code help please.

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

RE: VBA code help please.

Hi,

Quote:

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

CODE

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,

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

RE: VBA code help please.

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

RE: VBA code help please.

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

Skip,

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

RE: VBA code help please.

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

RE: VBA code help please.

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

Skip,

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

RE: VBA code help please.

GUAGE or GAUGE (A and U postponed) ??

RE: VBA code help please.

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,

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

RE: VBA code help please.

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

RE: VBA code help please.

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

RE: VBA code help please.

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

CODE

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,

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

RE: VBA code help please.

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

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