using the active.formula with variables.

[stephicohu]

Hi!

Question to ask you, if I have this formula: ActiveCell.Formula = "=iferror('5 month'!r7c34,0)" and I want to convert all the numbers to variables, how do I do that?

do I use a single tick or double ticks for the separation of numbers? for example is it activecell.formula = "=iferror("variable"!r"variable"c"variable",0)"? or something else?

Thanks

Stephanie

 

[Wookiee]

I would put it in a string, like

strFormula = "=IFERROR('5 month'!R" & rowVariable & "C" & colVariable & ",0)"

 

[stephicohu]

most of it is working, the only part not working is it is not putting in the IFERROR (Microsoft function) output in the spreadsheet. Any ideas that cause it not to work?

 

[stephicohu]

I have tried everything but have not been able to find out why my spreadsheet doesn't show the iferror function of 0. Can you Mr. Wookiee looked over my code and see why I have not been able to have the iferror function work in this function?

Function FillingColumnAvg(ByRef Month As String, ByVal TargetMonth As String, ByRef FirstRow1 As Integer, ByRef FirstColumn As Integer, ByRef TargetRow As Integer, _
ByRef TargetColumn As Integer)

    Dim StrFormula As String
   
    Sheets(Month).Activate
    Cells(FirstRow1, FirstColumn).Copy
    Sheets(TargetMonth).Activate
    Cells(TargetRow, TargetColumn).Activate
    ActiveSheet.Paste Link:=True
    StrFormula = "= IFERROR('" & Month & "'!R" & FirstRow1 & "C" & TargetColumn & ",0)"
   



End Function

the debug shows the StrFormula having the full equation. The output is just the Month, FirstRow1 and TargetColumn on the in a link output.

Thanks


Stephanie

 

[stephicohu]

Mr. Wookiee is not responding to my replies. What I did was to follow Mr. Wookiee's response and did what he suggestion. It kept failing to allow me to include the IFERROR formula in the paste-link cell.

I did a what if:

1. Add the following formula to the cell that I am copying from in the copy spreadsheet: =IFERROR(AVERAGE(C7:I7), 0)
2. In the code, I'd commented out the string formula that I had written on the suggestion of Mr. Wookiee. "= IFERROR('" & Month & "'!R" & FirstRow1 & "C" & FirstColumn & ",0)"
3. The code would now do this:
   1. It would copy the formula (see 1) from another spreadsheet
   2. Then paste-link the copied information into the target spreadsheet cell
4. I ran the code and it works!

The question I have is why can't I do the idea that Mr. Wookiee told me to do? If the answer is no you can't, I will accept that with a reason why? The code I used was the following:

Function FillingColumnAvg(ByRef Month As String, ByRef TargetMonth As String, ByRef FirstRow1 As Integer, ByRef FirstColumn As Integer, ByRef TargetRow As Integer, _
ByRef TargetColumn As Integer)

    Dim StrFormula As String
 
 
    Sheets(Month).Activate
    Cells(FirstRow1, FirstColumn).Copy
    Sheets(TargetMonth).Activate
    Cells(TargetRow, TargetColumn).Activate
    ActiveSheet.Paste Link:=True
'    StrFormula = "= IFERROR('" & Month & "'!R" & FirstRow1 & "C" & FirstColumn & ",0)" this was the section that Mr. Wookiee suggested to add.

End Function

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Since your new thread was really just a continuation of this one I have combined them - refer to #12 of the Forum Rules

Mr. Wookiee is not responding to my replies.

You can't expect volunteer helpers to be online 24/7 and it is weekend so please be patient.


You have used "Month" as an argument name in your function. That is not a good idea since Month is an existing function in vba and it could cause confusion/errors. You will see that I have changed that argument name below.

There is rarely any need to activate or select things in vba to work with them and doing so can slow your code and cause screen flickering.

Try this instead

Function FillingColumnAvg(ByRef SourceMonth As String, ByRef TargetMonth As String, ByRef FirstRow1 As Integer, ByRef FirstColumn As Integer, _
                          ByRef TargetRow As Integer, ByRef TargetColumn As Integer)

  Dim StrFormula As String

  StrFormula = "=IFERROR('" & SourceMonth & "'!R" & FirstRow1 & "C" & FirstColumn & ",0)"
  Sheets(TargetMonth).Cells(TargetRow, TargetColumn).Formula = StrFormula
End Function

 

[stephicohu]

Thanks Peter the function now works. I am new for Mr. Excel site and VBA coding. I only been been doing this about three months, learning most of this on the fly. I want to apologized that I was impatient on solving this problem. I sometimes I have a one track mind and this was bugging me to figure out why it wasn't working. Again, I apologize and my wrong in this matter.
I will do your things you mentioned. I thought that I had already placed the version I what platform I am using. I will read the rules, guidelines, FAQ and mark as solution. BTW, do you by any chance know of a place that has all the words that is not suggested to use? That would be handy to have so I don't make the mistake of using month by itself.

Thanks

Stephanie....

 

[Peter_SSs]

Thanks Peter the function now works.

You're welcome. Glad it works for you now.

I am new for Mr. Excel site ..... I want to apologized

No problem, you will 'learn as you go' on the site - just like we all did at the beginning.

, do you by any chance know of a place that has all the words that is not suggested to use?

I'm afraid that I don't, but again, you will learn as you go. Still this would be a reasonable place to start: MS Excel: VBA Functions - Listed by Category


BTW, thanks for updating your version/platform details.

 

[RogerP]

I hope you can help. I am trying to generate a formula in one cell which has two (or more) evaluations.

For example:
=(IF(V50=V52,"T","F")&" is the comparison of V50=V52" & CHAR(10)
&(IF(W50=W52,"T","F")&" is the comparison of W50=W52"))

If I code the above formula directly into the cell, the results appear on two rows within on cell, as you see below. However, I am trying to use a VBA macro to build the "ActiveCell.Formula =" and have the macro display these two evaluations on two separate rows in one cell. Here's enough of the code to give you an idea of what I am doing.

i = 50
k = 52
Range("X" & i).Select
Stop
ActiveCell.Formula = "=(IF(V" & i & "=V" & k & ",""T"",""F"")" & "&"" is the comparison of V" & i & "=V" & k & """" _
& Chr(10) & _
"&(IF(W" & i & "=W" & k & ",""T"",""F"")" & "&"" is the comparison of W" & i & "=W" & k & """))"

After hours (and hours) of trying to build this instruction, I finally figured out the CORRECT number of double quotes and ampersands that I need. However, the output prints on one row with word wrap. I'm trying to have the output appear on two separate rows with a carriage return and it is THIS carriage return that I cannot get to work. I'd appreciate any help you can provide. Thanks.

The first cell below is what I get from the above coded portion of my macro.

The second cell is what I need from the VBA macro and I am ONLY able to generate it if I hardcode the cell with the formula appearing at the top of this note.

Roger

 

[Peter_SSs]

and it is THIS carriage return that I cannot get to work.

Yes, you are putting the new line, Chr(10), in the formula in the formula bar, not CHAR(10) in the cell's formula result.

After hours (and hours) of trying to build this instruction, I finally figured out the CORRECT number of double quotes and ampersands that I need.

Can I suggest that for the future you consider an approach more like I have done below. With this approach, instead of trying to individually insert every variable separately into the formula, use placeholders in the formula and then at the end replace those placeholders with the relevant variable value(s). That way the formula being built in the vba (red code below) looks considerably more like the final formula that you are after.
Also note that selecting the cell(s) and then using ActiveCell makes your code slower and there is no need to actually select the cell to insert its formula (or do anything else with it)

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Anyway, see if something like this would help.

i = 50
k = 52
With Range("X" & i)
  .Formula = Replace(Replace( _
    "=IF(V@=V#,""T"",""F"")&"" is the comparison of V@=V#""&CHAR(10)&IF(W@=W#,""T"",""F"")&"" is the comparison of W@=W#""" _
      , "@", i), "#", k)
  .EntireColumn.ColumnWidth = 35
  .WrapText = True
End With