FormulaR1C1 Not Working

[JP2R]

Why am I getting the error
"Compile Error:
Expected End of Statement"

The formula works in the cell...

'-- Selecting Column to begin "Do Loop-Until" to insert formula --
    Range("AM2").Select
    ActiveCell.FormulaR1C1="=IF(AND(AH2="",AI2="",AJ2>=AE2),"1","")"
       
    Do
    ActiveCell.FormulaR1C1 = "=IF(AND(AH2="",AI2="",AJ2>=AE2),"1","")"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
'-- End Loop --

-- Monk

[Digita]

Replace this part:

ActiveCell.FormulaR1C1=

with:

ActiveCell.Formula =

[JP2R]

Ah...
Thanks - tried that - not working...

I did some other attempts and it appears that the "" (or null) is what seems to mess it up...

so was trying to do "null" another way to get it work...
<> didn't work
wonder if I should use "null" or something...

Your thoughts?

-- Monk

[JP2R]

I was able to get this to work:

'-- Selecting Column to begin "Do Loop-Until" to insert formula --
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<RC[-7],0,RC[-26])"
Do
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<RC[-7],0,RC[-26])"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
'-- End Loop --

but where "0" is in the formula is where I want "" - or null - or empty...
see what I mean?

-- Monk

[rbrhodes]

Hi Monk,

Double your quotes. Vba will choke on quote marks in a string. e.g

Range("A1") = "Dog" will work

Range("A1") = "Dog and a quote mark " " will not work. The VBE sees the second quote mark as the end of the string and chokes on the third one.

Range("A1") = "Dog and a quote mark "" " will work however as the VBE interprets the double quote mark as a request to put an actual quote mark in the string, the final one completes the string.

One more example:

Range("A1") = "Dog and 3 quote marks """ " will not work, needs to be paired with a fourth

Range("A1") = "Dog and 4 quote marks """" " will show up in the cell as:

Dog and 4 quote marks ""

HTH




[VBA]
Sub DamnQuotes
'-- Selecting Column to begin "Do Loop-Until" to insert formula --
Range("AM2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"

Do
ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

'-- End Loop --
End Sub

[/VBA]

[Bob Phillips]

Sub DamnQuotes
 '-- Selecting Column to begin "Do Loop-Until" to insert formula --
Range("AM2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
Do
    ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
'-- End Loop --
End Sub

Not a good idea to add an A1 style formula when you declare the formula type as R1C1, it tneds to output the cell references with single quotes around them.

[JP2R]

Awesome thanks for the reply - going to follow these examples and see if I can get it to work - will post 'solution' if I can follow your direction properly!

BTW
I know I mixed the ActiveCell.formula and the .formulaR1C1 - but I was tired last night and had messed with it for so long - it's a wonder I typed my name correctly...

Thanks so much!
-- Monk

[JP2R]

Awesome thanks for the reply - going to follow these examples and see if I can get it to work - will post 'solution' if I can follow your direction properly!

BTW
I know I mixed the ActiveCell.formula and the .formulaR1C1 - but I was tired last night and had messed with it for so long - it's a wonder I typed my name correctly...

Thanks so much!
-- Monk

[mdmackillop]

Dim Rng As Range
Set Rng = Range(Range("AM2"), Range("AM2").End(xlDown))
Rng.Offset(, -1).FormulaR1C1 = "=IF(RC[-2]<RC[-7],"""",RC[-26])"

[JP2R]

DR -

I was able to get this to work - I mean the quotations.
but now for some reason the loop won't fill down the column.

Column AH is labeled "Exemption Date" (Date Formated)
Column AI is labeled "Exemption Reason" (Text String)
Column AJ is labeled "Group Duration" (Decimal Number - example 3.3311111)
Column AE is labeled "Closure Due" (Whole Number - example 8)

The formula is to demonstrate that if if there is nothing in AH and AI - and yet exceeds the closure due time - I need a "1" - else null/empty is okay...

The one time I was able to get the column to fill - it put the same formula in each cell - vice incrementing.

If you need a spreadsheet sample I can provide a line or two - it's a big file.

-- Monk

[rbrhodes]

Hi Monk,

I missed the formula value too. Anyways here's some examples for you:

Puts SAME formula in each cell, Not what you want I don't think

Option Explicit
'Your version

Sub Same()
'-- Selecting Column to begin "Do Loop-Until" to insert formula --
    Range("AM2").Select
'not needed
    'ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
Do
        With ActiveCell
   .Formula = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
   .Offset(1, 0).Select
        End With
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
'-- End Loop --
End Sub
 
Sub ColumnSame()
'My version
Dim cel As Range
Dim rng As Range
Dim LastRow As Long
    'Get last row of data Column AN
    LastRow = Range("AN65536").End(xlUp).Row
'Create object
    Set rng = Range("AM2:AM" & LastRow)
'//Same formula in each cell
    'Use For Each (faster)
    For Each cel In rng
        cel.Formula = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
    Next cel
'-- End Loop --
'Destroy objects
     Set cel = Nothing
     Set rng = Nothing
End Sub

Puts progressive formula in each cell

Option Explicit
 
Sub Diff()
'Yours
Dim counter As Long
     '-- Selecting Column to begin "Do Loop-Until" to insert formula --
    Range("AM2").Select
'not needed
    'ActiveCell.FormulaR1C1 = "=IF(AND(AH2="""",AI2="""",AJ2>=AE2),1,"""")"
'Init
    counter = ActiveCell.Row
Do
        With ActiveCell
   .Formula = "=IF(AND(AH" & counter & "="""",AI" & counter & "="""",AJ" & counter & ">=AE" & counter & "),1,"""")"
   .Offset(1, 0).Select
   counter = ActiveCell.Row
        End With
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
'-- End Loop --
End Sub
 
Sub ColumnDifferent()
'Mine
Dim cel As Range
Dim rng As Range
Dim counter As Long
Dim LastRow As Long
    'Get last row of data Column AN
    LastRow = Range("AN65536").End(xlUp).Row
'Create object
    Set rng = Range("AM2:AM" & LastRow)
'Init counter
    counter = 2
'//Same formula in each cell
    'Use For Each (faster)
    For Each cel In rng
   cel.Formula = "=IF(AND(AH" & counter & "="""",AI" & counter & "="""",AJ" & counter & ">=AE" & counter & "),1,"""")"
   counter = counter + 1
    Next cel
'Destroy objects
     Set cel = Nothing
     Set rng = Nothing
End Sub

[JP2R]

Thanks DR -

Going to take this example and try it out.
I am very grateful for your assistance...

-- Monk

[JP2R]

DR -

Awesome - that did it!

I'm working this through this project step-by-step - not the cleanest piece of work - but I'm learning.

Thanks so much for all the support and help from everyone...

-- Monk

[mdmackillop]

While loops are fine with small ranges, I still consider FormulaR1C1 the better solution. It can be applied to the whole range in one line of code.

[JP2R]

I'm horribly self-taught (that is with the biggest of question marks - I might add) - and I was just trying to automate as best as I could.

My brain thought about looping through what I was trying to put in place - I don't understand what you mean to "consider FormulaR1C1 the better solution" and would LOVE to learn.

Point me in the right direction...
You should see how long this code is that I've done so far - it's sad - but works - and I'm afraid to remove anything for fear that I will break what works...

Most graciously
Monk

[mdmackillop]

see post #9

[rbrhodes]

Hi,

Yeah I'm 'self-taught' as well. Started with a book, moved to the forums, still learning. Should probably be ashamed of my earlier attempts but that was the learning process.

Monk if you want someone to look at your code, feel free to PM or email me. Maybe I can give you some pointers... maybe not! Debugging isone of my favourite hobbies.