Add formula instead of constant

[YasserKhalil]

Hello everyone
I cell C15 I have constant string 'MyTest'
How can I convert the cell to be a formula to look like that

Code: Select all

=IF(AND(A15="",B15=""),"","MyTest")

Thanks advanced for help

[YasserKhalil]

I have figured it out. I was in trouble with the quotation marks .. and at last I could figure it out

Code: Select all

Sub Test()
    Dim s As String
    
    s = Cells(15, 3).Value
    Cells(15, 3).Formula = "=IF(AND(A15="""",B15=""""),"""",""" & s & """)"
End Sub

[Doc.AElstein]

Hello Yasser,

When building strings like you are doing here to either: …_
_ put in a spreadsheet cell
( or
_ put into a VBA Evaluate(“ “) ) ,
_.... then I find it very helpful to put the string first in a String variable, and then to look at that string in the Immediate window.
The reason that I find it helpful to do this is because: In the immediate window the string is displayed exactly as it would be put in the cell. You must get the string to be put in exactly as you would write it in manually: So the displayed string in the Immediate window must look exactly as that which you would manually write it in a cell.
So you can adjust the string in the string variable and run the code until the string looks just as it should do.
I find this way of doing it a bit easier than just adjusting the string until it puts the correct formula in the cell. I find that by doing it this way I can sometimes see immediately if the string is incorrect.
ImmediatelySeeImmediate.JPG : https://imgur.com/LVzUELY" onclick="window.open(this.href);return false; , https://imgur.com/okViKly" onclick="window.open(this.href);return false;

Another advantage of the immediate window, I find, is that I can copy the formula in English, or in my language. This gives me another way to do formula translations.
MyGermanTestie.JPG : https://imgur.com/6UkzZIx" onclick="window.open(this.href);return false;

Below a demo code, ( it must go in a normal code module ). **You must run it from in the VB Editor**


Alan

P.S. You don’t need to use .Formula property to “write” the formula in:
You can either use .._
Range("C15").Formula = "=IF(AND(A15="""",B15=""""),"""",""" & s & """)"
_.. like you did to assign the formula property, or you can “write” it in , ( in English ) using
Range("C15").Value = "=IF(AND(A15="""",B15=""""),"""",""" & s & """)"
If you want to get the formula out in English, then you must do
= Range("C15").Formula
( or if you want the formula in your language, then you can do
= Range("C15").FormulaLocal )

Here is a code : https://pastebin.com/BidcvtRW" onclick="window.open(this.href);return false;
It will give you an Immediate window like this:
Correct strings...
=IF(AND(A15="",B15=""),"","testie")
=IF(AND(A15="",B15=""),"","testie")
=IF(AND(A15="",B15=""),"","testie")

Range "values"
Range("C15").Value is testie
Range("C15").Formula is =IF(AND(A15="",B15=""),"","testie")
Range("C15").Formulalocal is =WENN(UND(A15="";B15="");"";"testie")

Bad strings...
=IF(AND(RngHey15.Address(rowabsolute:=False, columnabsolute:=False)="",B15=""),"","testie")
=IF(AND(A15=",B15=""),"","testie")

Code: Select all

Option Explicit ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31260
' **This code must be run from the VB Editor**
Sub ImmediatelySeeImmediate()
 Application.SendKeys Keys:="^g" ' Or manually, **when in the VB Editor**, Hit  Ctrl+g
 DoEvents: DoEvents
Dim StrVeryErhBall As String

' Correct strings...
 Debug.Print "Correct strings..."
 Let StrVeryErhBall = "=IF(AND(A15="""",B15=""""),"""",""" & "testie" & """)"
 Debug.Print StrVeryErhBall
 Let StrVeryErhBall = "=IF(AND(A15="""",B15=""""),"""",""testie"")"
 Debug.Print StrVeryErhBall
Dim RngHey15 As Range
 Set RngHey15 = Range("A15")
 Let StrVeryErhBall = "=IF(AND(" & RngHey15.Address(rowabsolute:=False, columnabsolute:=False) & "="""",B15=""""),"""",""" & "testie" & """)"
 Debug.Print StrVeryErhBall
Debug.Print:
' Range values   .Value    .Formula      .Formulalocal
Debug.Print "Range  ""values"""
 Let Range("C15").Value = StrVeryErhBall ' The default displayed, "seen" and "written" property is value, so this canbe used to "put in" a formula, just as if you wrote it manually
 
 Let RngHey15.Value = "Somefink": Range("B15").Value = "Somefink Else"
 MsgBox prompt:="Formula in cell is " & vbCrLf & Range("C15").Formula & vbCrLf & vbCrLf & "If you put that in a cell or inside Evaluate("" "") , then you get" & vbCrLf & Application.Evaluate(StrVeryErhBall) & vbCrLf & vbCrLf & "Note also, the formula in the local language is" & vbCrLf & Range("C15").FormulaLocal
 DoEvents: DoEvents
 Debug.Print "Range(""C15"").Value   is       " & Range("C15").Value
 Debug.Print "Range(""C15"").Formula  is      " & Range("C15").Formula & vbCrLf & "Range(""C15"").Formulalocal is   " & Range("C15").FormulaLocal
 Debug.Print

' Bad strings...
Debug.Print "Bad strings..."
 Let StrVeryErhBall = "=IF(AND(" & "RngHey15.Address(rowabsolute:=False, columnabsolute:=False)" & "="""",B15=""""),"""",""" & "testie" & """)"
 Debug.Print StrVeryErhBall
 Let StrVeryErhBall = "=IF(AND(" & RngHey15.Address(rowabsolute:=False, columnabsolute:=False) & "="",B15=""""),"""",""" & "testie" & """)"
 Debug.Print StrVeryErhBall

'_-----------------------------------------------------------
 On Error GoTo Bed
Application.Wait (Now + TimeValue("0:00:10"))
Application.OnTime earliesttime:=Now, procedure:="ClearImmediateWindow"
Application.OnTime earliesttime:=Now + TimeValue("0:00:01"), procedure:="TraSh_it"
Exit Sub
Bed:
 MsgBox prompt:="Good night"
End Sub
Public Sub ClearImmediateWindow()
 Application.SendKeys "^g ^a {DEL}"
End Sub
Public Sub TraSh_it()
 On Error Resume Next
 Application.VBE.Windows("Immediate").Close ' English Excel
 Application.VBE.Windows("Direktbereich").Close ' German Excel
'Application.VBE.Windows("§$?*?#$§%").Close ' HindDuWolly Excel
' On Error GoTo 0
End Sub
'
'
'
'
' https://www.excelcampus.com/vba/vba-immediate-window-excel/
' https://social.msdn.microsoft.com/Forums/office/en-US/da87e63f-676b-4505-adeb-564257a56cfe/vba-to-clear-the-immediate-window?forum=exceldev
'  http://dailydoseofexcel.com/archives/2004/06/09/clear-the-immediate-window/#comment-1045809