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





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

[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.