vba inserting @ symbol into R1C1 formula

drkjz

New Member
Joined
Jul 12, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I'm using the following VBA code to insert a formula into a range

VBA Code:
    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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Does the below produce the correct results for you?
VBA Code:
dataRng.Columns(4).Range("A" & MAWindow + 2 & ":A" & lastRow).Formula2 = "=MAX(IF(I3:I23<0,H3:H23))"
or
VBA Code:
    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 & "]))"
 
Last edited:

drkjz

New Member
Joined
Jul 12, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
    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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Did the 2nd code with the relative references not work?
 

drkjz

New Member
Joined
Jul 12, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

thanks
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
No I meant the 2nd code in post number 2.
 

drkjz

New Member
Joined
Jul 12, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Many thanks
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
No I edited it at 07:43 my time, it is now 15:13 ;)

Anyway happy it is working for you :cool:
 

drkjz

New Member
Joined
Jul 12, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
It's morning here so I don't think my coffee had kicked in yet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,782
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top