VBA to set a Cell's contents to a value, without changing the visual representation of the cell?

[Strider89]

It's a long-winded title, but here's what I'm looking to do:

• I want to set specific text strings to a number, such as CPT is 17 if CPT is in column C, 25 in column E, etc.
• Users should be able to go through the sheet and type in "CPT", where vba will check the column CPT appears in, and then treat CPT as a corresponding number. For instance,

​

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]Prod[/TD]
[TD="align: center"]MH[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]0.5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]CPT[/TD]
[TD="align: center"]=A3*B3*C3[/TD]
[TD="align: center"]CPT[/TD]
[TD="align: center"]=A3*E3[/TD]
[/TR]
</tbody>[/TABLE]

The formulas in D and F need to INTERPRET CPT in Column "C" as 17, and CPT in Column E as 25, but not CHANGE the CPT into the number, as in, I don't want it looking like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]Prod[/TD]
[TD="align: center"]MH[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]0.5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]=A3*B3*C3[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]=A3*E3
[/TD]
[/TR]
</tbody>[/TABLE]


Is this possible? I have a slew of data I need to be able to refer to and reference differently depending on the columns they appear in, so I can't simply set the values to static numbers, and I need to be able to plug different "Codes" into columns C and E, otherwise I would simply set the formula to multiply by the Variable.

 

[MickG]

If you had a series of named range like "CPT" with a value of say 17 then you could use the formula

=A3*B3*Indirect(C3)

 

[Strider89]

Okay, but what if I want to set CPT to equal 17 in Column C and 25 in Column E? Is that possible? I realize it's setting a single variable to multiple values, but I only want those values to exist in specific ranges.

 

[Rick Rothstein]

Okay, but what if I want to set CPT to equal 17 in Column C and 25 in Column E? Is that possible? I realize it's setting a single variable to multiple values, but I only want those values to exist in specific ranges.

We can pretty much make VB code almost anything you want, but you have to tell us what it is you want (otherwise we won't know how to write the code). If you show us all of the codes and their values for each column they can appear in, we will try to write something for you.

 

[Strider89]

I don't have access to the full range of codes yet, but the examples I was given are:

CPT = 17 in C, 25 in E
MTL = 33 in C, 45 in E
CPT1 = 33 in C, 65 in E
MSN = 19 in C, 66.98 in E
RFR = 25 in C, 45.58 in E
CLAB = 17 in C, 45.54 in E

 

[Rick Rothstein]

I don't have access to the full range of codes yet, but the examples I was given are:

CPT = 17 in C, 25 in E
MTL = 33 in C, 45 in E
CPT1 = 33 in C, 65 in E
MSN = 19 in C, 66.98 in E
RFR = 25 in C, 45.58 in E
CLAB = 17 in C, 45.54 in E

For the code designations and values you show above (I think you should be able to extend the code below for your new code designations), running this macro will physically change the value in the cells to the indicated numbers, but still display the code designations themselves.

Sub ReplaceCodesWithValuesKeepDisplay()
  Dim Cell As Range
  For Each Cell In Intersect(Range("C:C,E:E"), ActiveSheet.UsedRange)
    Select Case Cell.Value
      Case "CPT"
        Cell.Value = IIf(Cell.Column = 3, 17, 25)
        Cell.NumberFormat = """CPT"""
      Case "MTL"
        Cell.Value = IIf(Cell.Column = 3, 33, 45)
        Cell.NumberFormat = """MTL"""
      Case "CPT1"
        Cell.Value = IIf(Cell.Column = 3, 33, 65)
        Cell.NumberFormat = """CPT1"""
      Case "MSN"
        Cell.Value = IIf(Cell.Column = 3, 19, 66.98)
        Cell.NumberFormat = """MSN"""
      Case "RFR"
        Cell.Value = IIf(Cell.Column = 3, 25, 45.58)
        Cell.NumberFormat = """RFR"""
      Case "CLAB"
        Cell.Value = IIf(Cell.Column = 3, 17, 45.54)
        Cell.NumberFormat = """CLAB"""
    End Select
  Next
End Sub

 

[Strider89]

If I am understanding this code right, I can add multiple columns to the "For Each" line, and then the commas in the subsequent "Cell.Value" lines represent the value if it appears in that column, correct? As in, the code checks to see if and only if it is in column 3 (C), then the value is 17, otherwise it is 25? How would I be able to expand this to include multiple columns?

 

[Rick Rothstein]

If I am understanding this code right, I can add multiple columns to the "For Each" line, and then the commas in the subsequent "Cell.Value" lines represent the value if it appears in that column, correct?

Yes to the first part but no to the second part. The IIF function only works for two values (the Column C and E you told me about). If you have more columns to process, I will have to rewrite the code to handle them. By the way, this is why I said in Message #4 , "If you show us all of the codes and their values for each column they can appear in"... so that I could (hopefully) write the code only once for your actual situation instead of writing code for what you don't have and then having to rewrite it when you tell us what you actually have.

 

[Strider89]

Sorry, I'm still new to coding, so I was hoping to get a line of code that I could mold and play with from veteran and professional VBA experts. There are a total of 6 columns, some values (Such as CPT) can appear in all columns, others, such as MTL, will only appear in one or two. It would look something like this (The Columns are actually D, E, H, J, N, and P. The example was, as stated previously, aimed at getting a snippet of code I could experiment with.)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K
[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prod[/TD]
[TD]MH[/TD]
[TD]Rate[/TD]
[TD]Total[/TD]
[TD]Unit Price[/TD]
[TD]Total[/TD]
[TD]Prod[/TD]
[TD]Hours[/TD]
[TD]Rate[/TD]
[TD]Total[/TD]
[TD]Unit Price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.5[/TD]
[TD]5[/TD]
[TD]CPT[/TD]
[TD]=F2*G2*H2[/TD]
[TD]CPT[/TD]
[TD]=G2* J2[/TD]
[TD]9.6[/TD]
[TD]2[/TD]
[TD]MTL[/TD]
[TD]=G2*N2[/TD]
[TD]9.0[/TD]
[TD]=G2*P2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]MTL[/TD]
[TD][/TD]
[TD]CPT1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CLAB[/TD]
[TD][/TD]
[TD]MSN[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Column P will never contain a MTL Code
Column H will never contain a RFR code


Something to this effect.