If function in Macro
 

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

dq is the double quote character:

Sub rick()
dq = Chr(34)
s = "=IF(NOW()+7H5,$J5," & dq & dq & ")"
Range("R5").Formula = s
End Sub

--
Gary''s Student - gsnu200798


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

Range("D1").Value = "=IF(NOW()+7H5,$J5,"""")"

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

Is the bottom of the selection always the same?

"Rick" wrote:

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

What is the dq referring to?

"Gary''s Student" wrote:

dq is the double quote character:

Sub rick()
dq = Chr(34)
s = "=IF(NOW()+7H5,$J5," & dq & dq & ")"
Range("R5").Formula = s
End Sub

--
Gary''s Student - gsnu200798


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

tihs will goto row 10
For i = 5 To 10
Range("R" & i).Value = _
"=IF(NOW()+7H" & i & ",$J" & i & ","""")"
Next

"Rick" wrote:

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

No, but I have a formula to indicate how far down to copy.

"Mike" wrote:

Is the bottom of the selection always the same?

"Rick" wrote:

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

Thanks this works well, in that it puts the formula into the cell but it
isn't showing the answer.

Basically it is saying if today's date plus 7 days is greater than the date
in H5 then show the value in J5, Well the date in H5 is a week ago so it
should be showing the J5 value but there is nothing showing in the cells,
even after I calculate manually.

Thanks
Rick

"Mike" wrote:

tihs will goto row 10
For i = 5 To 10
Range("R" & i).Value = _
"=IF(NOW()+7H" & i & ",$J" & i & ","""")"
Next

"Rick" wrote:

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get

If function in Macro
 

dq is a string variable. It contains the double quote character.
--
Gary''s Student - gsnu200798

If function in Macro
 

Here's an alternate way to do it

Sub Test()
Dim aWS As Worksheet
Dim myRange As Range
Dim lRow As Long

Set aWS = ActiveSheet
Set myRange = aWS.Range("H5") '<~~~~this is the starting range
lRow = myRange.End(xlDown).Row

'Defining range in column H to use in formula
Set myRange = myRange.Resize(lRow - myRange.Row + 1, 1)

'Defining range in r for formula

Set myRange = myRange.Offset(0, 10)
myRange.FormulaR1C1 = "=IF(NOW()+7RC[-10],RC10,"""")"
'Alternate???
'myRange.FormulaR1C1 = "=IF(NOW()+7=RC[-10],RC10,"""")"
End Sub

I think in the formula, you may need to change it to include an = sign.
--
HTH,
Barb Reinhardt



"Rick" wrote:

Thanks this works well, in that it puts the formula into the cell but it
isn't showing the answer.

Basically it is saying if today's date plus 7 days is greater than the date
in H5 then show the value in J5, Well the date in H5 is a week ago so it
should be showing the J5 value but there is nothing showing in the cells,
even after I calculate manually.

Thanks
Rick

"Mike" wrote:

tihs will goto row 10
For i = 5 To 10
Range("R" & i).Value = _
"=IF(NOW()+7H" & i & ",$J" & i & ","""")"
Next

"Rick" wrote:

How would I then copy this down to the bottom of the selection?

I was thinking that once the formula was done I could copy and paste but
with Range..., how this then work?

"Mike" wrote:

Range("R5").value = "=IF(NOW()+7H5,$J5,'')."


"Rick" wrote:

Hi,

This is the if statement in Excel,

=IF(NOW()+7H5,$J5,"").

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7RC[-10],$RC[-8],"")"

But I get and object defined error.

Is my problem with R1C1 reference style or something else?

Thanks
rick
When I run the macro I get