VBA Loop for Column select from input box

[Earlyfreak]

I have a input box to select the column with the dates thenwhere I want to put the time span into words.
I am trying to use a loop to enter formula down the column but keep getting error's

Sub DateSpan2Words()
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
On Error Resume Next
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, LocNumcol).End(xlUp).row
On Error Resume Next
For i = 1 To FinalRow
).VaCells(i, TimeSpanCollue = .FormulaR1C1 = _
        "=IFERROR(IF(DATEDIF(RC[-1],TODAY(),""y"")=0,"""",DATEDIF(RC[-1],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-1],TODAY(),""ym"")=0,"""",DATEDIF(RC[-1],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-1],TODAY(),""md"")=0,"""",DATEDIF(RC[-1],TODAY(),""md"")&"" days""),"""")"
  Next
Application.ScreenUpdating = True
End Sub
[CODE]

errors on the loop "Cells(i, TimeSpanCol).Value = .FormulaR1C1 = _"
any direction would be greatly appreciated

 

[dmt32]

Hi,
some points:-

this section of code

).VaCells(i, TimeSpanCollue = .FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC[-1],TODAY(),""y"")=0,"""",DATEDIF(RC[-1],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-1],TODAY(),""ym"")=0,"""",DATEDIF(RC[-1],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-1],TODAY(),""md"")=0,"""",DATEDIF(RC[-1],TODAY(),""md"")&"" days""),"""")"

both parts shown in RED are invalid as neither exist elsewhere in your code & the syntax is wrong

Perhaps you meant this

Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC[-1],TODAY(),""y"")=0,"""",DATEDIF(RC[-1],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-1],TODAY(),""ym"")=0,"""",DATEDIF(RC[-1],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-1],TODAY(),""md"")=0,"""",DATEDIF(RC[-1],TODAY(),""md"")&"" days""),"""")"
Next

this part

FinalRow = Cells(Rows.Count, LocNumcol).End(xlUp).Row

the variable shown in RED has not been declared or intialized.
If you place Option Explicit at top of your module, VBA will report missing variables.

You are Using

On Error Resume Next statements in your code - this only serves to mask errors and is generally considered bad practice.

Hope of some help

Merry Christmas

Dave

 

[Earlyfreak]

Dave

Thanks for the pointers and advice to get me in the right direction.

the final working code:

Sub DateSpan2Words()
    
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
On Error GoTo DateSpan2Words_Error
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, DateNumCol).End(xlUp).row
For i = 1 To FinalRow
Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC[-1],TODAY(),""y"")=0,"""",DATEDIF(RC[-1],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-1],TODAY(),""ym"")=0,"""",DATEDIF(RC[-1],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-1],TODAY(),""md"")=0,"""",DATEDIF(RC[-1],TODAY(),""md"")&"" days""),"""")"
Next
  
    On Error GoTo 0
    Exit Sub
Application.ScreenUpdating = True
DateSpan2Words_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateSpan2Words, line " & Erl & "."
End Sub

I also changed the error handling compliments of MZtools add-in

Thanks again for the education. Merry Christmas

 

[Earlyfreak]

Dave I spoke to soon. It works great if the dates are in Column A and I want to enter the formula into B RC formula RC[-1]

how ever I need to be able to change the locations from the input boxes so when I change to column G (7) it breaks

I tried direction and Changes RC[-1] to RC[-DateNumCol] but keep getting errors

Broken code when direction to other columns

Sub Test_DateSpan2Words()
    
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
On Error GoTo DateSpan2Words_Error
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, DateNumCol).End(xlUp).row
For i = 1 To FinalRow
Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC[-DateNumCol],TODAY(),""y"")=0,"""",DATEDIF(RC[-DateNumCol],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-DateNumCol],TODAY(),""ym"")=0,"""",DATEDIF(RC[-DateNumCol],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-DateNumCol],TODAY(),""md"")=0,"""",DATEDIF(RC[-DateNumCol],TODAY(),""md"")&"" days""),"""")"
Next
  
    On Error GoTo 0
    Exit Sub
Application.ScreenUpdating = True
DateSpan2Words_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateSpan2Words, line " & Erl & "."
End Sub

Thanks again for looking

 

[Fluff]

You need to separate out your variables like

"=IFERROR(IF(DATEDIF(RC[-[COLOR=#ff0000]" &[/COLOR] DateNumCol [COLOR=#ff0000]& "[/COLOR]],TODAY(),""y"")

 

[Earlyfreak]

Fluff
That works now if my dates are in column A (column 1) and I want to put time span in column G (column 7)
the formula does update with

(RC[-" & DateNumCol & "],TODAY(),""y"")

which references F1, but dates are in A1


my problem, miscalculation is the date is column 1 I wand to put the dates in column 7 the formula should be

(RC[-6],TODAY(),""y"")

what I have so far

Sub DateSpan2Words()
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, DateNumCol).End(xlUp).row
For i = 1 To FinalRow
Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC[-" & DateumCol & "],TODAY(),y"")N""=0,"""",DATEDIF(RC[-" & DateNumCol & "],TODAY(),""y"")&"" years "")&IF(DATEDIF(RC[-" & DateNumCol & "],TODAY(),""ym"")=0,"""",DATEDIF(RC[-" & DateNumCol & "],TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC[-" & DateNumCol & "],TODAY(),""md"")=0,"""",DATEDIF(RC[-" & DateNumCol & "],TODAY(),""md"")&"" days""),"""")"
Next
Application.ScreenUpdating = True
End Sub

close just have to figure how to reference the first input box where the dates are

thanks for looking at it and you help

 

[Fluff]

In the formula you have a typo on the first DateNumCol (you're missing the N)
& if I understand correctly try it like this

"=IFERROR(IF(DATEDIF(RC" & DateNumCol & ",TODAY(),y"")

(ie get rid of the [- & ])

 

[Earlyfreak]

I changed as per your suggestion (makes sense) but now I get error code.
"Error 1004 (Application-defined or object-defined error) in procedure DateSpan2words, line 0


Here is what I have so far Dates in column A attempting to populate span in Column G (or any column directed from input box)

Sub FluffSolutionDateSpan2Words()
    
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
On Error GoTo DateSpan2Words_Error
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, DateNumCol).End(xlUp).row
For i = 1 To FinalRow
Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC"" & DateNumCol & "",TODAY(),""y"")=0,"",DATEDIF(RC"" & DateNumCol & "",TODAY(),""y"")&"" years "")&IF(DATEDIF(RC"" & DateNumCol & "",TODAY(),""ym"")=0,"""",DATEDIF(RC"" & DateNumCol & "",TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC"" & DateNumCol & "",TODAY(),""md"")=0,"""",DATEDIF(RC"" & DateNumCol & "",TODAY(),""md"")&"" days""),"""")"
Next
  
    On Error GoTo 0
    Exit Sub
Application.ScreenUpdating = True
DateSpan2Words_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateSpan2Words, line " & Erl & "."
End Sub

thanks for continued help and sticking with me

 

[Fluff]

You've got to many quotes it should be like

"=IFERROR(IF(DATEDIF(RC[COLOR=#ff0000]"[/COLOR] & DateNumCol &[COLOR=#ff0000] "[/COLOR],TODAY(),""y"")

 

[Earlyfreak]

I removed the extra " though you needed them
but
get the same error code

Sub FluffSolutionDateSpan2Words()
    
Dim DateNumCol As Integer
Dim TimeSpanCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Application.ScreenUpdating = False
On Error GoTo DateSpan2Words_Error
MyCol = ActiveCell.Column
DateNumCol = Application.InputBox(Prompt:="If not this column, then Enter Column Number where Date is In", Default:=MyCol, Type:=1)
TimeSpanCol = Application.InputBox(Prompt:="Enter Column Number where Time Span to be entered in", Default:=MyCol, Type:=1)
FinalRow = Cells(Rows.Count, DateNumCol).End(xlUp).row
For i = 1 To FinalRow
Cells(i, TimeSpanCol).FormulaR1C1 = _
"=IFERROR(IF(DATEDIF(RC" & DateNumCol & ",TODAY(),""y"")=0,"",DATEDIF(RC" & DateNumCol & ",TODAY(),""y"")&"" years "")&IF(DATEDIF(RC" & DateNumCol & ",TODAY(),""ym"")=0,"""",DATEDIF(RC" & DateNumCol & ",TODAY(),""ym"")&"" months "")&IF(DATEDIF(RC" & DateNumCol & ",TODAY(),""md"")=0,"""",DATEDIF(RC" & DateNumCol & ",TODAY(),""md"")&"" days""),"""")"
Next
  
    On Error GoTo 0
    Exit Sub
Application.ScreenUpdating = True
DateSpan2Words_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DateSpan2Words, line " & Erl & "."
End Sub