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

 

[boarder236]

Yeah, that is where I placed it. I am not sure why it is not working

 

[Joe4]

Did you copy my code, exactly as is?
I did not complete all the entries, just the first few.
Note that part that says "...". You really do not want "..." in there.

Did you try compiling the VBA code to make sure there are no errors (from the Debug menu in the VB Editor, select "Compile VBAProject". If there are any errors, it should let you know.

 

[boarder236]

I didnt enter all the data yet because I want to make sure it works first.

The "..." was taken out and I did compile errors. Everything is good, but still comes up as #NAME? upon entering =convertheader(cell)


When I type in =convertheader should an excel drop down come up recognizing it?

 

[Joe4]

When I type in =convertheader should an excel drop down come up recognizing it?

Yes.

Are macros enabled in your workbook?
And you have it written as a Function and not a Sub, right?

Another test you can do, is right in the module where you have placed the code, add this macro and run it:

Sub MyCheck()
   MsgBox ConvertHeader("auftrag")
End Sub

It should return a message box saying "Actual Phase". If it doesn't, there is something wrong with how you have written the function. Try pasting it back here.

 

[boarder236]

It sends back the actual phase.

I entered into a blank module and first pasted the function you showed.
But still nothing comes up when =convertheader

Macros are enabled because I have a good amount running

THanks for all your help

 

[Joe4]

Can you post the exact and complete function (that is not working), as you have written it on the Excel sheet?

Please also confirm the exact name of the Module that the UDF is stored in.

 

[boarder236]

Location: Module5 of VBAProject




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

 

[Joe4]

Go to the sheet where you are doing this replacement.
Open the VB Editor window, and resize it so it is about 1/4 the size of your screen so you can see your sheet and the VB Editor window at the same time.

In the VB Editor, click in front of the first line of your macro (the one that reads "Sub MyReplaceHeaders()". Now, if you press F8, it will step through your macro one line at a time (each time you press F8). And if you hover over any variable that you pass in your VBA code, it will show you its current value.

Armed with this knowledge, you can "debug" your code but stepping through it line-by-line and seeing exactly what it is doing (by watching your spreadsheet and checking out your variables as you pass them). Using this, it should hopefully reveal to you what the problem is.

 

[boarder236]

i am starting to understand it better and I think I can get it working.

If instead of replacing the row of coded names, I want to keep those names in it and insert below the uncoded names.

How would i change the code for that

 

[Joe4]

Just modify the code like this (changes highlighted in red)

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

[COLOR=red]' Insert a blank line at row 2 to put new titles[/COLOR]
[COLOR=red]    Rows(2).EntireRow.Insert[/COLOR]

' 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[COLOR=red]+1[/COLOR], myCol) = ConvertHeader(Cells(myRow, myCol))
    Next myCol

End Sub