VBA "IF' statement

[boarder236]

I am currently working on a macro that will decode the cell above it with an "if" statement.
There are around 60 different possibilities, so the statement is long.

I typed the equation into a cell on excel.
From there I recorded macro, and entered it into all the needed cells in the workbook.

It is very large and I think that may be the problem.
How could I break this macro up so the different macros work together, meaning if I run 1 then 2, so 2 doesnt just cover up 1 if 1 was true.

Any help would be great.

Thanks

 

[SteveO59L]

Try posting your existing code

 

[Joe4]

Welcome to the Board!

It is really hard to understand what you are trying to do without seeing your code/formula or data.

Are you just looking up 60 different values? If so, creating a lookup table and using VLOOKUP may be a better way to go (see Excel's built-in help on VLOOKUP for details and examples).

Or if you have a complex calculation, it may be better to build a User Defined Function in VBA instead of a sub procedure macro. Then you can just feed it the variables, and it will do the calculation and return a value, like any other Excel function.

 

[boarder236]

Range("B3").Select
Windows("Book3").Activate
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-2]C=""AuftrAG"",""Actual Phase"",IF(R[-2]C=""dwtst01"",""Testing Variable 1"",IF(R[-2]C=""dwtst02"",""Testing Variable 2"",IF(R[-2]C=""dwtst03"",""Testing Variable 3"",IF(R[-2]C=""dwtst04"",""Testing Variable 4"",IF(R[-2]C=""ejector_pos"",""Ejector Position"",IF(R[-2]C=""faz"",""Force Main Cylinder"",IF(R[-2]C=""fez"",""Force Actuator Cylinder"",IF(R[-2]C=""f"& _
"""Net Pressing Force"",IF(R[-2]C=""fvez"",""Velocity Feedback"",IF(R[-2]C=""pakku"",""Akku Pressure"",IF(R[-2]C=""p_qaz"",""Multiplier From Working Cylinder"",IF(R[-2]C=""p_qez"",""Multiplier From Actuator Cylinder"",IF(R[-2]C=""sez"",""Position Ram"",IF(R[-2]C=""Vac_P_Sys"",""Vacuum System Pressure"",IF(R[-2]C=""Vac_P_Tool"",""Actual Valve Value Positioning WC2"",I"& _
"""vez"",""Speed Ram"",IF(R[-2]C=""vfaz"",""Output Force Controller Main Cylinder"",IF(R[-2]C=""vfmin"",""Output Min-Force-Controller"",IF(R[-2]C=""vfvor"",""Force Pre Control"",IF(R[-2]C=""vsez"",""Output Position Controller High Speed"",IF(R[-2]C=""w3vez"",""Input Precontrol Table High Speed"",IF(R[-2]C=""wfaz"",""Set Value Force Main Cylinder"",IF(R[-2]C=""wfez"","& _
"ue Force Velocity Cylinder"",IF(R[-2]C=""wsez"",""Set Value Position"",IF(R[-2]C=""wsp"",""Set Value Position Parallel Motion"",IF(R[-2]C=""wvaz1"",""Set Value Velocity"",IF(R[-2]C=""wvez"",""Set Value Velocity of the Ram"",IF(R[-2]C=""wvez_vor"",""Input Precontrol Table High Speed"",IF(R[-2]C=""wWzTmpT01"",""Set Value Temperature Table 1"",IF(R[-2]C=""wWzTmpT02"",""& _
"e Temperature Table 2"",IF(R[-2]C=""xp_Tool[1]"",""Tool Pressure Load Cell #1"",IF(R[-2]C=""xp_Tool[2]"",""Tool Pressure Load Cell #2"",IF(R[-2]C=""xp_Tool[3]"",""Tool Pressure Load Cell #3"",IF(R[-2]C=""xp_Tool[4]"",""Tool Pressure Load Cell #4"",IF(R[-2]C=""xp_Tool[5]"",""Tool Pressure Load Cell #5"",IF(R[-2]C=""xp_Tool[6]"",""Tool Pressure Load Cell #6"",IF(R[-2]"& _
"ol[1]"",""Tool LVDT #1"",IF(R[-2]C=""xs_Tool[2]"",""Tool LVDT #2"",IF(R[-2]C=""xs_Tool[3]"",""Tool LVDT #3"",IF(R[-2]C=""xs_Tool[4]"",""Tool LVDT #4"",IF(R[-2]C=""xWzTmpS01"",""Actual Value Temp Ram 1"",IF(R[-2]C=""xWzTmpS02"",""Actual Value Temp Ram 2"",IF(R[-2]C=""xWzTmpS03"",""Actual Value Temp Ram 3"",IF(R[-2]C=""xWzTmpS04"",""Actual Value Temp Ram 4"",IF(R[-2]C"& _
"S05"",""Actual Value Temp Ram 5"",IF(R[-2]C=""xWzTmpS06"",""Actual Value Temp Ram 6"",IF(R[-2]C=""xWzTmpS07"",""Actual Value Temp Ram 7"",IF(R[-2]C=""xWzTmpS08"",""Actual Value Temp Ram 8"",IF(R[-2]C=""xWzTmpS09"",""Actual Value Temp Ram 9"",IF(R[-2]C=""xWzTmpS10"",""Actual Value Temp Ram 10"",IF(R[-2]C=""xWzTmpS11"",""Actual Value Temp Ram 11"",IF(R[-2]C=""xWzTmpS1"& _
"ual Value Temp Ram 12"",IF(R[-2]C=""xWzTmp T01"",""Actual Value Temp Table 1"",IF(R[-2]C=""xWzTmp T02"",""Actual Value Temp Table 2"",IF(R[-2]C=""y205u"",""Output Voltage H1T"",IF(R[-2]C=""y205wPro"",""Set Value Velocity H1T"",IF(R[-2]C=""y210u"",""Output Voltage H1T"",IF(R[-2]C=""y210wp"",""Set Pressure H1T"",IF(R[-2]C=""y4"",""Set Value Valve Pressure hp-accu"",IF"& _
""yaz"",""Set Value Valve Working Cylinder"",IF(R[-2]C=""yaz1_1"",""Set Value Valve Working Cylinder Y1.1"",IF(R[-2]C=""yazIst1_1"",""Actual Value Valve 21-Y1.1"",IF(R[-2]C=""yez"",""Set Value Valve Actuator Cylinder"",IF(R[-2]C=""yezlst"",""Actual Value Valve 21-Y3.1"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))""& _
"ate
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:BN3"), Type:=xlFillDefault
Range("B3:BN3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub




This is the code, i tried breaking it down to 4 macros but didnt work.




What I am trying to do is this:

I have a press that outputs data for me. It gives the column headings in a code, I need to apply something that decodes what it says.
There can be different selections for what columns are what one time, and change the order next time. I need to have every possible decoder in all the cells.
Is there a better way to do this?


Thanks<!-- / message -->

 

[Joe4]

If you are just looking at values (that could be one of 60 different values), and want to return something else, follow my suggestion of setting up a lookup table and use the VLOOKUP function.

 

[boarder236]

Directly below this is what i want (the original is the bottom picture). I dont think vlookup will work because with my understanding is that it needs to be a vertical set up.
I need to add a row and then enter in the equations, right now i only have the if statment in an equation on excel, that will be posted at the bottom. I need excel to automatically do thiswhen this type of file is opened.

The excel "if" formula

 

[Joe4]

So, if I understand this correctly, you get a row of title "codes" in row 1 that you need to convert to more verbose descriptions, right?

If that is the case, I think you can still use VLOOKUP. The "vertical" the talk about in VLOOKUP is for the lookup table you create, not the layout of your data.

So, you would set up a lookup table like this in Excel:

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=257><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 width=102>Code</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=155>Descriptions</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>auftrag</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Actual Phase</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>dwtst01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Testing Variable 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>dwtst02</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Testing Variable 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>dwtst03</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Testing Variable 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>dwtst04</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Testing Variable 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Ejector_Pos</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ejector Position</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>faz</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Force Main Cylinder</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>fez</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Force Actuator Cylinder</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>…</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">…</TD></TR></TBODY></TABLE>

Then, highlight and name the range (Formulas -> Defined Names -> Define Name in Excel 2007). I named it "Headers".

Now, let's say you you insert a row under row 1 (your header codes), where you want to populate these more verbose descriptions.

So for cell B1, enter this formula in cell B2:
=VLOOKUP(B1,Headers,2,0)
and copy that all the way across the row for all your header values. That should do what you want.

Alternatively, if you insist on doing this in VBA, I would recommend creating a User Defined Function to do it. Basically, you are just creating your own Excel function to do that conversion. But since the details of the function are in VBA, you do not need a huge large formula in Excel. Also, it is much easier to maintain.

 

[Joe4]

OK, here is the VBA/UDF solution. Your User Defined Function would look something like this:

Function ConvertHeader(myCode As String) As String
 
    Select Case myCode
        Case "auftrag"
            ConvertHeader = "Actual Phase"
        Case "dwtst01"
            ConvertHeader = "Testing Variable 1"
        Case "dwtst02"
            ConvertHeader = "Testing Variable 2"
        Case "dwtst03"
            ConvertHeader = "Testing Variable 3"
        Case "dwtst04"
            ConvertHeader = "Testing Variable 4"
        Case "Ejector_Pos"
            ConvertHeader = "Ejector Position"
        Case "faz"
            ConvertHeader = "Force Main Cylinder"
        Case "fez"
            ConvertHeader = "Force Actuator Cylinder"
'       Continue on for the rest of your possibilities
        ...    
        Case Else
'       If not found, just return the code
            ConvertHeader = myCode
    End Select
 
End Function

Then you can use it in a few different ways. You could use it like any other Excel function. So if you had your code in B1, then you could enter this formula in B2:
=ConvertHeader(B1)

However, you can also use the UDF in other VBA code. For example, here is a script that does not require you to insert an extra row for the descriptions, It simply replace the codes found in row 1 with their matching descriptions:

Sub MyReplaceHeaders()
 
    Dim myLastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    
'   Designate row where headers to replace are found on
    myRow = 1
    
'   Find last column with data in row
    myLastCol = Cells(myRow, Columns.Count).End(xlToLeft).Column
    
'   Loop through all entries in row and replace code with verbose description
'   (if you want to start in column B, change the 1 to a 2)
    For myCol = 1 To myLastCol
        Cells(myRow, myCol) = ConvertHeader(Cells(myRow, myCol))
    Next myCol
    
End Sub

 

[boarder236]

When I try your vba code then enter =convertheader(Cell#) is doesnt recognize it.

 

[Joe4]

Where did you place the VBA code for the ConvertHeader UDF?
It needs to be placed in a standard module in your workbook.

If you turn on your Macro Recorder temporarily and then stop it, it you look in the VB Editor, that code will be added to a Standard Module. So you can put the UDF code there.