vba inserting @ symbol into R1C1 formula

[drkjz]

I'm using the following VBA code to insert a formula into a range

    dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).FormulaR1C1 = _
        "=max(if(R[-" & MAWindow - 1 & "]C[" & colDiff + 2 & "]:RC[" & colDiff + 2 & "]<0,R[-" & MAWindow - 1 & "]C[" & colDiff + 1 & "]:RC[" & colDiff + 1 & "]))"

I want the resulting formula to look like:
=MAX(IF(I3:I23<0,H3:H23))

but the code is producing:
=MAX(IF(@I3:I23<0,H3:H23))

which does not return the correct "H" for rows in which column "I" has a positive value.

I'm at a loss to know how to remove the "@" symbol from the formula created by the vba code.

 

[MARK858]

Does the below produce the correct results for you?

dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).Formula2 = "=MAX(IF(I3:I23<0,H3:H23))"

or

    dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).Formula2R1C1 = _
        "=max(if(R[-" & MAWindow - 1 & "]C[" & colDiff + 2 & "]:RC[" & colDiff + 2 & "]<0,R[-" & MAWindow - 1 & "]C[" & colDiff + 1 & "]:RC[" & colDiff + 1 & "]))"

 

[drkjz]

Your suggestion worked but I would like to keep the relative references in there. Based on your suggestion, I was able to get this work but it seems like there should be an easier way.

    dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).FormulaR1C1 = _
        "=max(if(R[-" & MAWindow - 1 & "]C[" & colDiff + 2 & "]:RC[" & colDiff + 2 & "]<0,R[-" & MAWindow - 1 & "]C[" & colDiff + 1 & "]:RC[" & colDiff + 1 & "]))"
    
'    dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).Formula2 = "=MAX(IF(I3:I23<0,H3:H23))"
    
    Dim x As Integer
    Dim tempString As String
    
    For x = MAWindow To lastRow
        tempString = CStr(dataRng.Columns(4).Range("A" & x).Formula2)
        tempString = Replace(tempString, "@", "")
        dataRng.Columns(4).Range("A" & x).Formula2 = tempString
    Next

 

[MARK858]

Did the 2nd code with the relative references not work?

 

[drkjz]

Yes, this one worked. I just thought it could be done with something simpler than write, read, edit, write again.

thanks

 

[MARK858]

No I meant the 2nd code in post number 2.

 

[drkjz]

I missed the 2nd part... did you just edit your post?? Anyway, that worked perfectly.

Many thanks

 

[MARK858]

No I edited it at 07:43 my time, it is now 15:13

Anyway happy it is working for you

 

[drkjz]

It's morning here so I don't think my coffee had kicked in yet