VBA Loop for Column select from input box

Earlyfreak

New Member
Joined
Jan 31, 2017
Messages
16
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


Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi,
some points:-

this section of code

Rich (BB 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


Rich (BB code):
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

Rich (BB code):
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
 
Upvote 0
Dave

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

the final working code:

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
 
Upvote 0
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

Code:
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
 
Upvote 0
You need to separate out your variables like
Code:
"=IFERROR(IF(DATEDIF(RC[-[COLOR=#ff0000]" &[/COLOR] DateNumCol [COLOR=#ff0000]& "[/COLOR]],TODAY(),""y"")
 
Last edited:
Upvote 0
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
Code:
(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

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


what I have so far

Code:
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
 
Upvote 0
In the formula you have a typo on the first DateNumCol (you're missing the N)
& if I understand correctly try it like this
Code:
"=IFERROR(IF(DATEDIF(RC" & DateNumCol & ",TODAY(),y"")
(ie get rid of the [- & ])
 
Upvote 0
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)


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

thanks for continued help and sticking with me
 
Upvote 0
You've got to many quotes it should be like
Code:
"=IFERROR(IF(DATEDIF(RC[COLOR=#ff0000]"[/COLOR] & DateNumCol &[COLOR=#ff0000] "[/COLOR],TODAY(),""y"")
 
Upvote 0
I removed the extra " though you needed them
but
get the same error code


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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top