variable inside an if formula vba help!@ easy fixi

[jordanburch]

HI All,

I have the below. It seems like an east fix I just can figure out the syntax. Can anyone help?
Its the variable parts that wrong and I cant figure it out. I keep trying different combinations with the variable inside the formula but can get it right.

Any ideas?

Jordan


Range("h2:h10000").SpecialCells(xlCellTypeVisible).Formula = "=+IF(RC[-6]=""" & Right(ThisWorkbook.Worksheets("Variables").Range("A10").Value, 2) & "/" & Left(ThisWorkbook.Worksheets("Variables").Range("A10").Value, 3) & """ , RC[-1],(RC[-1]*-1))"

 

[offthelip]

The easy way to find out where the error is when you are writing formula with vba is to remove the first equals sign so that you are just writing text to the cell. Then after you have run the vba, select the cell and put the equals sign back in, Excel will then tell you where the error is

 

[jordanburch]

The easy way to find out where the error is when you are writing formula with vba is to remove the first equals sign so that you are just writing text to the cell. Then after you have run the vba, select the cell and put the equals sign back in, Excel will then tell you where the error is

I tried that and it just errors out right before the first =. It does not show like I thought it would

 

[offthelip]

your problem is ( or at least the one I have spotted) is that you have got your double quotes wrong. this is a problem when you are writing formula ( which are basically text) when you want to include double quotes in the formula, excel treats them as the start and end of the text string. To get around this and to make the final string easier to differentiate between the uses of the double quotesd i always use the ascii chr(34) function to put double quotes into the string. I have started to change your code doing this to show you what the problem is and how to solve it:

Range("h2:h10").Formula = "+IF(RC[-6]=" & Chr(34) & " & Right(ThisWorkbook.Worksheets(" & Chr(34) & "Variables" & Chr(34) & ".Range"

 

[Fluff]

What is the final formula meant to be? As that code works for me & I get

=IF(B31="23/abc", G31,(G31*-1))

 

[jordanburch]

your problem is ( or at least the one I have spotted) is that you have got your double quotes wrong. this is a problem when you are writing formula ( which are basically text) when you want to include double quotes in the formula, excel treats them as the start and end of the text string. To get around this and to make the final string easier to differentiate between the uses of the double quotesd i always use the ascii chr(34) function to put double quotes into the string. I have started to change your code doing this to show you what the problem is and how to solve it:

Range("h2:h10").Formula = "+IF(RC[-6]=" & Chr(34) & " & Right(ThisWorkbook.Worksheets(" & Chr(34) & "Variables" & Chr(34) & ".Range"

 Range("h2:h10000").SpecialCells(xlCellTypeVisible).Formula = "=+IF(RC[-6]=" & Chr(34) & " & Right(ThisWorkbook.Worksheets(" & Chr(34) & "Variables" & Chr(34) & ".Range("A10").Value, 2) & "/" & Left(ThisWorkbook.Worksheets("Variables").Range("A10").Value, 3) & """ , RC[-1],(RC[-1]*-1))"

So like the above? its still not working for whatever reason? Thanks for your help!

 

[jordanburch]

What is the final formula meant to be? As that code works for me & I get

=IF(B31="23/abc", G31,(G31*-1))

HI Fluff its meant to be

IF(B31="05/2021", G31,(G31*-1)

Thanks

Jordan

 

[Fluff]

Then in what way isn't it working for you?

 

[jordanburch]

Theres

Then in what way isn't it working for you?

something wrong with the quotes or double quotes.

Then in what way isn't it working for you?

I could not figure out the syntax for an array. I have the below. Can you please help? I simplified it.

ActiveSheet.Range("$A$1:$BA$10000").AutoFilter Field:=2, Criteria1:=Array( _
         (Right(thisworkbook.worksheets("Variables").Range("A10").value, 2)-2), (Right(thisworkbook.worksheets("Variables").Range("A10").value, 2)-1), (right(thisworkbook.worksheets("Variables").Range("A10").value, 2)), Operator:=xlFilterValues

This part above I cannot figure out.



this part below works

  Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H489").Select
    ActiveCell.FormulaR1C1 = "=+-RC[-1]"
    Range("H489").Select
    Selection.FillDown

Any help is appreciated thanks Fluff!

 

[Fluff]

That is a totally different & so needs a new thread. Have you got the formula sorted out?