vb formula

[Kenja824]

I have this bit of code to find one of several texts inside a cell and return the one it finds.
Currently it finds the text at the end of the string of text in the cell. How can I adjust this to find the text anywhere within the cell?

Currently the cell may be AA150_R01_B03 and so it returns the B03 at the end.
What if the cell contains... AA150_R01_B03_Common but I still want to return the B03. How do you write the formula to look for the string fo text anywhere in the cells data?

Public Function Xtract(t As String) As String
Dim Area(1 To 48) As String
Dim s As String
Area(1) = "B01"
Area(2) = "B02"
Area(3) = "B03"
Area(4) = "B04"
Area(5) = "B05"
Area(6) = "B06"
Area(7) = "B07"
Area(8) = "B08"
s = ""
For i = 1 To 8
If InStr(1, t, Area(i)) > 0 Then
s = s & ", " & Area(i)
End If
Next i
Xtract = Right(s, Len(s) - 1)
End Function

 

[Kenja824]

On top of that, is it possible to write this code so that if it does not find any of the listed strings, it will then return the word "Common"?

 

[davidbeach]

The second one is easy: If s="" then s="Common". InStr does look anywhere in the string and returns the position, so I'm not sure what the original question is trying to ask.

 

[Kenja824]

Hmmmm ...... I tried copying sections of the code and the functions and manipulating them to add two more columns and return particular words from particular columns. It didnt work and my first thought was the piece of code at the end of the function..... .Offset(2, 1).Formula = "=Xtract2([@OP '#])" ..... It looked so similar to the formula =Right, that I thought that is what it was doing.

In the code, I originally had this......

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 = "Zone2"
.Offset(2, 1).Formula = "=Xtract([@Number])"
End With
End If
End With


With this function at the end.....

Public Function Xtract(t As String) As String
Dim Area(1 To 48) As String
Dim s As String
Area(1) = "B01"
Area(2) = "B02"
Area(3) = "B03"
Area(4) = "B04"
Area(5) = "B05"
Area(6) = "B06"
Area(7) = "B07"
Area(8) = "B08"
s = ""
For i = 1 To 8
If InStr(1, t, Area(i)) > 0 Then
s = s & ", " & Area(i)
End If
Next i
Xtract = Right(s, Len(s) - 1)
End Function

I am attaching the file I have going, with this code duplicated and manipulated.
New stuff is lines 116 through 142
New Functions are lines 473 thru 501

The end result should have been to give me two more columns called "Model" and "Style". With specific texts returned in the cells of those columns.
Instead it only added one more column called "Model" and didnt return anything in the cells.

I am not good with code, so you will have to forgive it if it is all sloppy looking the way I put all of the different codes together. I got a lot of help from here but adding pieces together I did it until I got it to work and was good with it after that. lol

 

[SkipVought]

You may be mixing metaphors, so to speak.

You are passing a STRING, which implies the values from ONE CELL...

Public Function Xtract(t As String) As String
    Dim Area(1 To 48) As String, i
    
    Area(1) = "B01"
    Area(2) = "B02"
    Area(3) = "B03"
    Area(4) = "B04"
    Area(5) = "B05"
    Area(6) = "B06"
    Area(7) = "B07"
    Area(8) = "B08"
    
    For i = 1 To UBound(Area)
        If t Like "*" & Area(i) & "*" Then
            Xtract = Area(i)
            Exit For
        End If
    Next i
    If xtract = "" Then xtraxt = "common"
End Function

BTW, if this function is stored in a standard module in your workbook, then simply use ON THE SHEET, as you would any other spreadsheet function.

Also I'd store this data B01, B02... and R01, R02... and any others in a sheet that you might label Factors. Name the ranges for each and then simply loop thru the range rather than an array. DATA is best stored in a table than to be burried in code, IMNSHO.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

Hey Skip

Yeah, my terminology is probably worse than my code. lol

I fixed my functions to work like yours and it ran through without errors, but in the end it had the same results. In my code (newest attached) there are three functions at the end that are all similar to this one. Actually one of them is this one and the other two are what I derived from using this one.

The end result I am looking for is these three functions in connection with 4 sections of code I have near the beginning (lines 89 - 141) that uses these functions, should be adding four columns at the end of the table named "Zone", "Zone2", "Model" and "Style". Each column should be extracting text from different columns.

The end result I am getting is the code adds 3 columns only and only adds results to the first two columns. It does not put the formula in the 3rd column and does not even add the fourth column. I cant figure out where I am going wrong.

In my code, all functions are placed at the end, beginning around line 150.

 

[Kenja824]

I didnt see the message below your code before. To be honest, most of that was another language for me. Except the last part but I thought I was working with a table.

 

[SkipVought]

I thought I was working with a table.

NO!

B01 thru B08 are not in a table.
Short, Long, ... are not in a table.
GMC, Chevy are not in a table.

Each of those LISTS, ought to be in a separate table. Much easier to maintain a table than to maintain data in code.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

This is where I am getting lost now. lol

In the beginning of the code, it puts all of the data on the sheet into one large table. Can you have tables within tables?

What you refer to (B05, B06, Short, Long, Etc...) seems to be the functions at the bottom of the code. To my understanding these are just like formulas I add that can be called and added to cells. In this case, these are meant to extract these parts of strings from cells in columns with particular headers.

Early in the code, there are bits of code that add columns to the end of the table and give them specific headers, then places code with those functions inside the cells to those columns.

I am not understanding what you mean much less how to even do it.

I really hate this privacy crap with GM. I was trying to make a file that had any important info deleted but its near impossible without having to completely redo all the code too.

I apologize if this gets frustrating.

 

[SkipVought]

Here's an example in the attached workbook.

Notice the lists/tables on sheet Factors
Notice the Named Ranges in the worbook
Notice the ONE function/VBA for both lists
Notice the formulas in the master table

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

I'm starting to see, but this looks like something you have to add the factors tab in to set it up.

I am given a simple unformatted .CSV excel file that is automatically produced by another program. Which information and columns will be in it will depend on the job and who runs it. I need to do several things to these files before I can use them.

Most importantly is the function for translating material code into a material letter like X, G, XG, B etc... This info gets placed next to each guage column so my eyes can easily pick out what the stackups are for each weld.

I then break down other columns that have two to four pieces of info that I need to sort the files by. I break them into different columns to make sorting/filtering easy. I then have to hide a bunch of useless columns and make a couple of them bold for my eyes to pick them out quicker and then color code rows according to R01 R02 R03 etc....

I do all of this just so I can manually enter the info into a program GM insists we use but hasnt been updated since 2003 because GM also doesnt support it anymore. Makes sense huh?

All of this wasnt a problem while I was the only one doing it. Now I have a couple people who I am teaching this program to and I decided it was time to just develop a module for excel to import and run and let it do all of this automatically. This way we all use the same info with the same formatting and its easy to help each other out when its needed.

So it would defeat the purpose if I have to create a second tab with all of the info we might use each time. ....... Though I suppose I could create code that will do that as well.

Here is the other problem. I have put a ton of hours digging in and building what I do have. It looks like what you are doing is a complete overhaul. Which I am sure is a better more efficient way, but I dont think I can spend so much time all over again. I thought it was done already and running good until I got a file with multiple styles together and I need to filter it all down more. Thats why I was just trying to add these two more columns at the end I cant get to work.

Now if doing this is a lot more difficult than its worth, I am fine with that. You guys have helped me immensely and I really appreciate it. I have this thing working enought to still help us for now and in this case I will just have to add a couple more columns manually. Not a problem for the occasional file. ) Then when I get more time to delve further, or maybe I can slowly work on it here and there to build the better code.

 

[SkipVought]

You are running this code from a workbook, right? Not the workbook you're creating, I understand.

So in the workbook that has the running code, THAT'S the place for Factors and lists.

However, you might be putting the vb module into the new workbook, in which case you could COPY the Factors sheet to the new workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Yes, its an enhancement. You're already using arrays.

Here's the trade-off. If at some time in the future, you or someone else needs to add values to search for, you or someone else needs to change the code for a greater number or fewer number of values and change the limit, which ought to be ...

For i = 1 to [b]Ubound([i]arrayName[/i])[/b]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IFRs]

Is the MID$ function available?