I have a table that looks like this 1

[BDNH]

I have a table that looks like this.

PC OD WT
B36 12.000 STD
B36 14.000 STD
B36 12.000 STD
B36 12.000 STD
B36 12.000 STD
B36 16.000 XS
B36 16.000 XS
B36 16.000 XS
B36 16.000 XS
B36 24.000 40
B36 24.000 40
B36 24.000 40
B36 24.000 40
B36 16.000 XS
B36 16.000 XS


I want the "WT" to fill in automatically depending on what I input for "PC" and "OD"

I need to be able to change the criteria for different jobs.
The criteria for this particular table is as follows:

PC B36

WT
3-14 = STD
16-20 = XS
24 = 40
30 = 750

What if I need to be able to use different PC's? for Example:

PC B35

WT
3-14 = 40
16-20 = XXS
24 = 60
30 = 80

Could I still have the "WT" column fill in automatically depending on the "PC" and "OD" input?

This is all part of a larger table and this is an effort to reduce possible input errors

Thanks for all the help with this and previous questions

 

[Zathras]

I suggest a User-Defined Function (UDF) would be the easiest way to go:

Put this in a code module:

Option Explicit

Function WT(PC, OD)
  Select Case PC
    
    Case "B36"
      Select Case OD
        Case 3 To 14: WT = "STD"
        Case 16 To 20: WT = "XS"
        Case 24: WT = 40
        Case 30: WT = 750
        Case Else: WT = "?"
      End Select
      
    Case "B35"
      Select Case OD
        Case 3 To 14: WT = 40
        Case 16 To 20: WT = "XXS"
        Case 24: WT = 60
        Case 30: WT = 80
        Case Else: WT = "?"
      End Select
  
  End Select
End Function

Then use the WT() function like any built-in Excel function. E.g.:

C2: =WT(A2,B2)

 

[BDNH]

Zathras

I have no idea how this works but it works great!

I only have 1 module right now (the one I just created. What happens if I create more modules - how does the WT(A2,B2) relate back to this one module?

Can I somehow relate the criteria to certain cells?
for example instead of Case "B36" have Case "CellA22"
Instead of Case 3 To 14 have Case Cell A23 to Cell A24

In other words can I make this module criteria dependent on input or will I need to change the module?

Thanks Again

Sorry for all the questions I dont know anything about VBA
Thats why Im so suprised I got your code to work.
You know you have explained it well when even I can get it to work

 

[Zathras]

You can create multiple modules or you can put multiple functions and subs in one module. It all depends on what you need to do. The WT() function is independent of anything else you code, except that you can use it inside of other functions and subs if you want to. The only restriction is that you can't name another sub or function WT or the compiler will complain.

You can modify the function by inserting additional cases as required. Just follow the pattern as shown.

I'm not sure I understand what you mean by Case "CellA22" or "Case Cell A23 to Cell A24" -- VBA aside, the concept of a function is a "black box" into which you can put 1 or more inputs and receive an output. In this case there are 2 inputs "PC" and "OD" and the output is "WT." To follow on your example, the formula in C2 can be copied into C3 thru however many rows you have data.

Did I completely mis-read you? Do "B35" and "B36" in your initial post refer to the contents of cells B35 and B36, or are they codes? I took them for codes since if you meant cell contents I would have expected to see "=B35" and "=B36"

If the criteria are so variable that it is not practical to encode them in a function, then perhaps what you really want is to use the VLOOKUP function and not use VBA at all.

What do you really need?

 

[BDNH]

Zathras,

I think you may have answered my question with your question. I guess what I meant to say is this:

Instead of using predefined criteria in the code such as

Case 3 To 14: WT = “STD”

Can I change the 3 and the 14 to make these ranges dependent on my input in certain cells like this

Case “=A22” To “=A23” = “STD”

You understood just fine the first time B35 and B36 did not refer to cell content in my first post

Sorry for the confusion and thanks for your help

BDNH

 

[Zathras]

You can make the function as complex as you want, but do you really want to?

But, you didn't answer my question: What do you really need? Are the conditions relatively constant or not? If the conditions (range of values and resulting codes) are subject to frequent change, I would use VLOOKUP which puts the values in the spreadsheet where you (or anyone else) can easily modify them.

However, to answer your question: You can add optional parameters to allow dynamic over-ride of the case, but it gets kind of messy and I'm not sure I would ever do it this way in production:

Option Explicit

Function WT(PC, OD, Optional SpecialCaseFrom = 0, _
    Optional SpecialCaseThru = -1, Optional SpecialCaseResult = "QQ")
  Select Case PC
    
    Case "B36"
      Select Case OD
        Case SpecialCaseFrom To SpecialCaseThru
                WT = SpecialCaseResult
        Case 3 To 14: WT = "STD"
        Case 16 To 20: WT = "XS"
        Case 24: WT = 40
        Case 30: WT = 750
        Case Else: WT = "?"
      End Select
      
    Case "B35"
      Select Case OD
        Case SpecialCaseFrom To SpecialCaseThru
                WT = SpecialCaseResult
        Case 3 To 14: WT = 40
        Case 16 To 20: WT = "XXS"
        Case 24: WT = 60
        Case 30: WT = 80
        Case Else: WT = "?"
      End Select
  
  End Select
End Function

Then when you need a special case, you can use this version of the function in the spreadsheet:

  =WT(A2,B2,A22,A23,"STD")

Note: VBA follows the same convention as GWBASIC in allowing multiple statements on one line separated by a colon. This is not usually a good idea since it tends to make the code hard to read in most cases. However, I make an exception in Case structures. I think using the colon to put the two code lines on the same physical line makes the code easier to read in this instance.

 

[BDNH]

Zathras,

Here is my situation,

These are just 3 columns of information inside a larger table.
These particular columns refer to Piping Class (PC) Outside Diameter (OD) and Wall Thickness (WT)

The piping class refers to a set of construction parameters specified by the client. In this example Piping class B36 specifies that all pipe 3”-14” OD must have a STD Wall Thickness, 16”-20” OD must have XS Wall thickness and so on. These piping classes also specify the material to be used, certain construction methods, and other information

Each row in my table represents a section of pipe to be constructed on the job. I theory different sections could have different Piping Classes with their respective construction parameters.

What I had thought of doing is creating a “Piping Class” sheet where I could enter the different piping classes and their specifications. On my table when I enter a section with Piping Class B36 (or any other piping class) and OD of 18” it will fill in The WT (as well as other information) according to the information on my Piping Class Sheet. I only need certain columns of information to fill in automatically not all of them

I need to be able to change the piping class names and parameters at any time as different clients have their own internal names and specifications for different piping classes.

I want to be able to enter the appropriate information only once (piping class sheet) instead of making sure the correct information is manually entered on each row.

Thanks for your patience

BDNH

 

[Zathras]

A "piping class" sheet is definitely the way to go.

If you set up a sheet like this:

A1: 'B36
A2: 0
A3: 3
A4: 14.01
A5: 16
A6: 20.01
A7: 24
A8: 24.01
A9: 30
A10: 30.01
B2: =NA()
B3: 'STD
B4: =NA()
B5: 'XS
B6: =NA()
B7: 40
B8: =NA()
B9: 750
B10: =NA()

Then you can use this formula in the first sheet, in the column next to the OD column:

  =VLOOKUP(B2,Sheet2!$A$2:$B$10,2,1)

Change the sheet name to whatever you are using and copy the formula down as far as needed for PC code "B36"

You can use a similar structure for code "B35"

C1: 'B35
C2: 0
C3: 3
C4: 14.01
C5: 16
C6: 20.01
C7: 24
C8: 24.01
C9: 30
C10: 30.01
D2: =NA()
D3: 40
D4: =NA()
D5: 'XXS
D6: =NA()
D7: 60
D8: =NA()
D9: 80
D10: =NA()

And use this formula in the first sheet, in the column next to the OD column:

  =VLOOKUP(B2,Sheet2!$C$2:$D$10,2,1)

You should use range names, not the hard-coded $C$2:$D$10 type of notation that is here just for illustration.

Hope this helps.

 

[BDNH]

Zathras

I've become real busy and didn’t want you to think that I was ignoring you or was ungrateful. As soon as I can try your VLOOKUP (probably Monday) I'll let you know how it went

Thanks again