| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If function in Macro
dq is a string variable. It contains the double quote character.
-- Gary''s Student - gsnu200798 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
| macro or function | Excel Programming | |||
| Macro run from if then function | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Run macro from IF function | Excel Programming | |||
Linear Mode
