If function in Macro

  • Thread starter Thread starter Rick
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
dq is a string variable. It contains the double quote character.
 
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...
 
You must log in or register to reply here.

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

Data samples 3
Geometric progression in VBA 10
macro to copy rows that contain a cell with a certain number 2
Help with Error 4
Combine 2 columns in a dynamic range into one 7
Macro that deletes and pastes filterd rows and then some 2
Macro: Copying to all possible rows 2
Trouble with Cutting and Inserting a Range 2

Back
Top