Macro to Fill with Formula, but Hide Formula to User

**Gard5096** · 12-20-2012, 12:34 PM

I would like to populate the F column with the following formula pulling from the same row in column E, but cant have the formula visible to the user. How can I create a macro to have the corresponding value fill in for the data set? Should I use a loop?

Any help is much appreciated.

Formula:

=IF(E2="BLANK","BLANK",IF(E2<6,"< 6 Days",IF(AND(E2>5,E2<11),"6-10 Days",IF(AND(E2>10,E2<16),"10-15 Days",IF(AND(E2>15),">15 Days")))))

Thank you,

Nick

**Gard5096** · 12-20-2012, 01:17 PM

I would like to fill column F with the resulting value from the following formula:

Formula:

=IF(E2="BLANK","BLANK",IF(E2<6,"< 6 Days",IF(AND(E2>5,E2<11),"6-10 Days",IF(AND(E2>10,E2<16),"10-15 Days",IF(AND(E2>15),">15 Days")))))

The only catch is that the formula cannot be visible from the worksheet.

Thank you for taking a look.

**Jack7774** · 12-20-2012, 01:37 PM

I am new here but i think you don't want the formula to be seen if the cell E2 is blank right? If yes then change your formula to this

=IF(E2="","",IF(E2<6,"< 6 Days",IF(AND(E2>5,E2<11),"6-10 Days",IF(AND(E2>10,E2<16),"10-15 Days",IF(AND(E2>15),">15 Days")))))

**Norie** · 12-20-2012, 01:39 PM

What do you mean the formula can't be visible from the worksheet?

**Gard5096** · 12-20-2012, 01:59 PM

Norie,

In column F I want the values created by the formula to show. When you click on the cell, I want the value to show up in the function bar, rather than the formula being visible. Any ideas on how to make a macro perform this?

**martindwilson** · 12-20-2012, 02:06 PM

you could just protect the cell and hide the formula then protect the sheet

**Norie** · 12-20-2012, 02:15 PM

So you want the code to put the formula in then replace it with values?

With Range("F2", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
    .Formula = "=IF(E2=""BLANK"",E2,LOOKUP(E1,{0,6,10,15},{""<6"",""6-10"",""10-15"","">15""})&"" Days"")"
    .Value = .Value
End With

**6StringJazzer** · 12-21-2012, 10:44 AM

There are two approaches to this. One is to use a loop to provide the result of this formula to all cells in column F, one time. This is only a good idea if you know the data in column E will not change. Another way is to detect any time the data in column E changes, and then update the cell in column F. Maybe you even need both; here they are.

Option Explicit

' Provide value in column F for all values in column E
Public Sub test()

   Dim r As Long
   
   For r = 2 To Cells(Rows.Count, "E").End(xlUp).Row
      Cells(r, "F") = ResultValue(Cells(r, "E"))
   Next r
   
End Sub

' Update the value in column F any time a value in column E changes
Public Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column = 5 Then ' column E
      Cells(Target.Row, "F") = ResultValue(Target)
   End If

End Sub

Public Function ResultValue(InValue As Variant) As String

      If InValue = "BLANK" Then
         ResultValue = "BLANK"
      ElseIf InValue < 6 Then
         ResultValue = "< 6 Days"
      ElseIf InValue > 5 And InValue < 11 Then
         ResultValue = "6-10 Days"
      ElseIf InValue > 10 And InValue < 16 Then
         ResultValue = "10-15 Days"
      ElseIf InValue > 15 Then
         ResultValue = ">15 Days"
      Else
         ' What happens if there is a bad/unexpected value in column E?
         ResultValue = "???"
      End If

End Function