If function in Macro

R

Rick

Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
 
G

Gary''s Student

dq is the double quote character:

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

Rick

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 said:
Range("R5").value = "=IF(NOW()+7>H5,$J5,'')."


Rick said:
Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
 
M

Mike

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

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


Rick said:
Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
 
M

Mike

Is the bottom of the selection always the same?

Rick said:
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 said:
Range("R5").value = "=IF(NOW()+7>H5,$J5,'')."


Rick said:
Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick

What is the dq referring to?

Gary''s Student said:
dq is the double quote character:

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

--
Gary''s Student - gsnu200798


Rick said:
Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
 
M

Mike

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

Rick said:
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 said:
Range("R5").value = "=IF(NOW()+7>H5,$J5,'')."


Rick said:
Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick

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

Mike said:
Is the bottom of the selection always the same?

Rick said:
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 said:
Range("R5").value = "=IF(NOW()+7>H5,$J5,'')."


:

Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick

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 said:
tihs will goto row 10
For i = 5 To 10
Range("R" & i).Value = _
"=IF(NOW()+7>H" & i & ",$J" & i & ","""")"
Next

Rick said:
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 said:
Range("R5").value = "=IF(NOW()+7>H5,$J5,'')."


:

Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
Click to expand...
 
B

Barb Reinhardt

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()+7>RC[-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 said:
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 said:
tihs will goto row 10
For i = 5 To 10
Range("R" & i).Value = _
"=IF(NOW()+7>H" & i & ",$J" & i & ","""")"
Next

Rick said:
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?

:

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


:

Hi,

This is the if statement in Excel,

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

This is how I have it in the macro,

Range("R5").Select
ActiveCell.FormulaR1C1 = "=if(now()+7>RC[-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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine 2 columns in a dynamic range into one 7
Geometric progression in VBA 10
Menu Macro Not Executing - Need Help 1
Data samples 3
Finding the right style field in VBA 0
macro to copy rows that contain a cell with a certain number 3
Macro File path problem 0
Macro 3

Top