set cell value with vba and get the formula in the Excel function bar

[MarieBocc]

Hello everyone,

I am trying to set the value of a cell by using vba. The thing is, this cell is in a column where all cells are set by the following formula :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(OR((H17<>"");(F17<>"");(E17<>""));SEARCH("zzz";B$9:$B17);"")

So when the user clicks on any cell, this is what the Excel function bar displays. But if I set my cell value with vba, how can I get my cell to display the same formula in the Excel function bar ?

To begin with, is it even possible ?

[/FONT]

Thank you for reading,

Marie​

 

[baitmaster]

My guess from reading this is that you want to change one or more of your inputs in order to achieve a specific outcome?

Otherwise you can't do this, you can either have a formula result from a fixed set of data/inputs or you can have a specific value, you can't have both

In VBA the cell value is range("A1").value. The formula as shown in the formula bar is Range("A1").formula. So yes, both things can be read or written by VBA - but only one can exist at the same time

If you want to manipulate your data to achieve a specific result then you would use Excel's Goal Seek feature, and would need to know which element you would be changing. Goal Seek, like everything else in Excel, can be used by VBA as well

 

[NimishK]

Marie

The following will give you some idea.

Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

'''''Suppose your formula is in B3 
With ws


.Range("B3").Formula = "=IF(" & "OR(" & "(" & "H17<>" & Chr(34) & Chr(34) & ");" & "(" & "F17<>" & Chr(34) & Chr(34) & ");" & "(" & "E17<>" & Chr(34) & Chr(34) & ")" & ");" & "SEARCH" & "(" & "zzz" & ";B$9:$B17" & ")" & ";" & Chr(34) & Chr(34) & ")"


End With

NimishK

 

[MarieBocc]

Thank you for your fast answer !

So if I understand what you are saying, instead of writing myCell.Value = "what ever" I could write myCell.formula =

IF(OR((H17<>"");(F17<>"");(E17<>""));SEARCH("zzz";B$9:$B17);"") ?
This way my cell will display the value processed by this formula and a left click on the cell will show this formula in the Excel function bar ?

I am not convinced Goal Seek apply to my specific case but I am actually programming with VBA with no basis on Excel so... Because even though I know what the value I want is, all in all I just want to put the formula in the bar. But maybe I am just so thick I don't realize this feature correspond to this action !

Thank you again !
​

 

[MarieBocc]

Marie

The following will give you some idea.

Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

'''''Suppose your formula is in B3 
With ws


.Range("B3").Formula = "=IF(" & "OR(" & "(" & "H17<>" & Chr(34) & Chr(34) & ");" & "(" & "F17<>" & Chr(34) & Chr(34) & ");" & "(" & "E17<>" & Chr(34) & Chr(34) & ")" & ");" & "SEARCH" & "(" & "zzz" & ";B$9:$B17" & ")" & ";" & Chr(34) & Chr(34) & ")"


End With

NimishK

OMG yes this is exactly the kind of thing I had in mind !
I'll try to put this into practice right away ! Thank you very much !

 

[NimishK]

Marie
Thanks for the compliments. Glad to Help

OMG yes this is exactly the kind of thing I had in mind !
I'll try to put this into practice right away ! Thank you very much !

 

[MarieBocc]

So I tried to put it into practice with the following piece of code :

[FONT=Verdana].Range(myCell,myCell).Formula = "=IF(" & "OR(" & "(" & "H17<>" & Chr(34) & Chr(34) & ");" & "(" & "F17<>" & Chr(34) & Chr(34) & ");" & "(" & "E17<>" & Chr(34) & Chr(34) & ")" & ");" & "SEARCH" & "(" & "zzz" & ";B$9:$B17" & ")" & ";" & Chr(34) & Chr(34) & ")"

But I get the following error : application-defined of object-defined error

Any idea ?_{^{<strike>
</strike>}}[/FONT]

 

[NimishK]

What have you coded for

.Range(myCell,myCell).Formula

Pl let me know where you have exactly posted the formula in which cell of the worksheet and values zzz in which range

 

[MarieBocc]

It goes like this :

Dim wholeData As Range
Set wholeData = Range(Cells(1, 1), Cells(lgnCount, colCount)) 'lgnCount and colCount are the last row and columns containing values

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim insertRow As Integer
insertRow = ... 'I have a method to get the row I want (It works)

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Range(Cells(wholeData.Rows(insertRow).Row, wholeData.Columns(1).Column), Cells(wholeData.Rows(insertRow).Row, wholeData.Columns(1).Column)).Formula = "=IF(" & "OR(" & "(" & "H17<>" & Chr(34) & Chr(34) & ");" & "(" & "F17<>" & Chr(34) & Chr(34) & ");" & "(" & "E17<>" & Chr(34) & Chr(34) & ")" & ");" & "SEARCH" & "(" & "zzz" & ";B$9:$B17" & ")" & ";" & Chr(34) & Chr(34) & ")"[/FONT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT]

 

[MarieBocc]

Pl let me know where you have exactly posted the formula in which cell of the worksheet and values zzz in which range

The formula goes in the first column of the row that the user choses to modify through an interface I coded.
Basicaly I insert an empty row in this position but the first columns id ruled by this formula that I am trying to write so I want to fill the first column of this row I just inserted with this formula.

Also to be honest I have no idea what this "zzz" stands for since this SEARCH function actually returns a string that is never zzz. But then again my understanding of Excel is very limited.