FormulaR1C1 returning nothing when using a variable

[Zem32619]

Hi All.

I have a code that works perfectly fine for users with the US excel and windows setting. I'm stuck why FormulaR1C1 doesn't work as it should when ran in a German setting.
Here is the line of code that works for US setting.

Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " ))*RC[-1]"

But it returns nothing, as in empty when ran in a German setting. I'm thinking if it is the variable thing "ACoS".

Would really appreciate any help. Thank you so much in advance.

 

[StephenCrump]

But it returns nothing, as in empty ....

It's not clear what the problem is. What is "it" that is returning nothing?

The line as posted: Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " ))*RC[-1]" has one too many ")", so it will error and not put a formula in L2. Are you using On Error Resume Next and hence missing this coding error?

If you are actually using: Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " )*RC[-1] the code will error if your code hasn't set a value for ACos. If you can replace ACos with a number and make it work, I'm guessing this is what is happening. Again, if you're using On Error Resume Next, you'll miss the coding problem.

Or perhaps you mean the code is working, but putting a formula in L2 that gives an unexpected result?

 

[Zem32619]

Hi Stephen.
Thank you for your reply. What I mean in returning nothing is the code works but don't give any result, just blank. You're right maybe it's because of the On Error Resume Next.
Here is the code that I have, and when I replace the variable ACoS with a number, the formula works.

Range("L1").FormulaR1C1 = "New Bid"
    Range("L2").FormulaR1C1 = "=(1-(RC[14])- & ACoS& ) *RC[-1]"
    Range("L2").Select
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    On Error Resume Next
    Selection.AutoFill Destination:=Range("L2:L" & lr)
    Range("L2:L" & lr).Select
    Columns("W:W").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 

[StephenCrump]

You've changed the code line that you've posted. This syntax is wrong:

Range("L2").FormulaR1C1 = "=(1-(RC[14])- & ACoS& ) *RC[-1]"

What I mean in returning nothing is the code works but don't give any result, just blank.

Sorry, I'm still not following. If the code works, it will put a formula in L2. Is it putting a formula in L2? If so, what is the formula?

What is "it" that's isn't giving a result? The formula? What does "blank" mean?

 

[StephenCrump]

What I think you want is:

Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & ACos & ")*RC[-1]"

which is what you originally posted.

But I suspect you've actually coded various subtly different incorrect versions?

 

[Zem32619]

Yes you're right, I've been changing the code for the hope to get it right.
Here is what I meant when I say just blank (pls see attached), the process was completed but it is not putting the formula.
I turned it back to the original formula I posted.

 

[StephenCrump]

Try deleting cell L1, then change your code to include:

Range("L1").Value = "New Bid"
Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & ACos & ")*RC[-1]"
MsgBox "ACos is: " & ACos
Stop

and run it again.

What's the value of ACos in the message box? And when the code hits the Stop line, what formula is in L2?

 

[Zem32619]

Tried the code, and when it hits STOP line, there is no formula in L2.
Also, ACoS value will be entered by the user, so this time I entered 30% and the value that appears in the message box was 0.3

 

[StephenCrump]

If ACos is 0.3, the code should populate L2: =(1-(Z2)-0.3)*K2. But 17 posts in, and we don't see to be making much progress.

I turned it back to the original formula I posted.

1. Please try it with the code exactly as I have posted it in #16. Copy and paste it, don't retype.
2. If you have any On Error Resume Next lines earlier in your code that you haven't closed out with an On Error GoTo 0, please comment out these lines.

What happens now if you run the code, i.e. with the MsgBox and Stop lines?

If it still doesn't work, please post your complete code.

There are other possibilities, e.g.

1. You have an On Error Resume Next earlier in the code, L2 is locked and the sheet is protected.
2. There is Worksheet_Change code clearing L2.

 

[Zem32619]

Hi Stephen.

Sorry for the late reply, I tried 1 & 2, copied and pasted the your exact code and commented out On Error Resume Next.
What I got now is an error "Run-time error '1004': Application-defined or object-defined error", and the highlighted line is below:

Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & ACoS & ")*RC[-1]"

Here is my complete code for your reference. Thank you.

Sub call_Products_Calibration()
'
 Application.ScreenUpdating = False
 Dim ACoS

' Input statement
 ACoS = (Application.InputBox(Prompt:="Enter Target ACoS.",  Type:=1) / 100)
 If ACoS = False Then
 Exit Sub
 End If
 
 On Error Resume Next
 lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row 'Last row with data

    Application.DisplayAlerts = False
    Sheets("Sponsored Brands Campaigns").Delete
    Sheets("Sponsored Display Campaigns").Delete
    Application.DisplayAlerts = True
    Sheets("Sponsored Products Campaigns").Select
 
With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = ","
        .UseSystemSeparators = False
    Columns("K:K").Replace What:=",", Replacement:="."
    Columns("T:T").Replace What:=",", Replacement:="."
    Columns("U:U").Replace What:=",", Replacement:="."
    Columns("V:V").Replace What:=",", Replacement:="."
    Columns("X:X").Replace What:=",", Replacement:="."
    Columns("Y:Y").Replace What:=",", Replacement:="."
    End With
   
    ActiveSheet.Range("$A$1:$AB$" & lr).AutoFilter Field:=2, Criteria1:="=Keyword", Operator:=xlOr, Criteria2:="=Product Targeting"
    ActiveSheet.Range("$A$1:$AB$" & lr).AutoFilter Field:=4, Criteria1:="<>*auto*", Operator:=xlAnd
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    'ActiveSheet.Range("$A$1:$AB$" & lr).AutoFilter Field:=25, Criteria1:=">=" & ACoS & "", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$AB$" & lr).AutoFilter Field:=25, Criteria1:=">= " & Replace(ACoS, ",", ".")
   
'Adding new sheets for Calib Pre and pasting data from main sheet
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Calibration KW Pre"
    ActiveSheet.Paste
   
'Adding new sheets for Calib Post and pasting data from Calib Pre sheet
    Sheets("Calibration KW Pre").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Calibration KW Post"
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
' New Bid

    'Range("L1").FormulaR1C1 = "New Bid"
    'Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " ) *RC[-1]"
    'Range("L2").Select
    'lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    'On Error Resume Next
    'Selection.AutoFill Destination:=Range("L2:L" & lr)
    'Range("L2:L" & lr).Select
    'Columns("W:W").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L1").FormulaR1C1 = "New Bid"
    Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & ACoS & ")*RC[-1]"
    MsgBox "ACos is: " & ACoS
    Stop
' CPC
    Range("W1").FormulaR1C1 = "CPC"
    Range("W2").FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("W2").Select
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    On Error Resume Next
    Selection.AutoFill Destination:=Range("W2:W" & lr)
    Range("W2:W" & lr).Select
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
' Litmus Test
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Litmus Test"
    Range("M2").FormulaR1C1 = "=IF(RC[-2]>RC[11], ""Calibrate"", ""Leave"")"
    Range("M2").Select
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    On Error Resume Next
    Range("M2").AutoFill Destination:=Range("M2:M" & lr)
    Range("M2:M" & lr).Select
   
'Filter to Calibrate
    Range("M1").AutoFilter
    On Error Resume Next
    ActiveSheet.Range("$A$1:$AE$" & lr).AutoFilter Field:=13, Criteria1:="Calibrate"
       
' Limit and Limit Test
    Sheets("Calibration KW Post").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Select
    ActiveSheet.Name = "Calibration Post Limit"
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'Limit Calculation

    Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N1").FormulaR1C1 = "Limit"
    Range("N2").FormulaR1C1 = "=RC[11]*0.8"
    Range("N2").Select
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    On Error Resume Next
    Selection.AutoFill Destination:=Range("N2:N" & lr)
   
    If Range("N2") = 0 Then
    Range("N2").ClearContents
    Else
    End If
  
'Limit Test
    Range("N2:N" & lr).Select
    Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("O1").FormulaR1C1 = "Limit Test"
    Range("O2").FormulaR1C1 = "=IF(RC[-3]>RC[-1],""Keep"",""Limit"")"
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    On Error Resume Next
    Range("O2").AutoFill Destination:=Range("O2:O" & lr)
   
    If Range("N2") = 0 Then
    Range("O2").ClearContents
    Else
    End If
   
   
' If Limit Test says "Limit" will copy the Limit bid as New Bid
    Range("O2:O" & lr).Select
    Cells.Select
    Dim ws As Worksheet
    Dim lngMyRow As Long
    Application.ScreenUpdating = False
    Set ws = Sheets("Calibration Post Limit")
    For lngMyRow = 2 To ws.Cells(Rows.Count, "O").End(xlUp).Row
        If StrConv(ws.Range("O" & lngMyRow), vbProperCase) = "Limit" Then
            ws.Range("L" & lngMyRow).Value = ws.Range("N" & lngMyRow).Value
        End If
    Next lngMyRow
    Application.ScreenUpdating = True
   
   
' Copy Calibration Post Limit sheet to a new workbook
   
    Sheets("Calibration Post Limit").Copy
    ActiveSheet.Name = "Sheet1"
  
' Copy new bid to Max bid column

    Application.ScreenUpdating = False
    Range("K2:K" & lr).Value = Range("L2:L" & lr).Value
    Application.ScreenUpdating = True
 
'Delete all formulaic columns
  
    Columns("L:O").EntireColumn.Delete
    Columns("V:V").EntireColumn.Delete
    Range("A1").Select
   
    MsgBox "Sponsored Products Calibration process Completed."
   
    Application.ScreenUpdating = True
   
End Sub

 

[Joe4]

Heed what MichaelM said back in post number 9. ACos is the name of an existing function.
You should NEVER, NEVER, NEVER used reserved words (the name of existing functions, properties, methods, etc) as names of your variables, procedures, and user defined functions.
Doing so can cause errors and unexpected results. You should change it immediately.

Even if that isn't the sole root of your problem, it isn't helping. Better to remove ANY complications that situation may be causing so you can narrow down the problem.