Need a VBA coding

[talkvinith]

Hi

Can anyone please help me in providing a VBA macro for the below formulas

1. This formula starts from AB2 column

IF(Y2>0,Y2,X2)

2. This formula starts from AC2 column

AB2-Today()

3. This formula starts from AD2 column

IF(AC>0,""2.Past", IF(AC2<30,""1.0.30",IF(AC2<60,"3.31-60","4.60+")))

I want the macro needs to run until the active cell.

Thanks

[patel]

Why not copy formulas ? attach please a sample file

[arangogs]

Hi Talkvinth,

Am I correct in thinking within Y2 & X2 you have dates?
You want to return a number in AC2 and then within AD2 return a value of either 2.Past or 1.0.30 or 3.31-60 or 4.60+

Why would you want a Macro for this, when It looks like you have a formula doing what you are looking for?

[talkvinith]

Thanks all for your response.

Each time I need to type the formula or need to copy and paste from other sheet, where the sheet contain a large amount of data it will be really helpful if I have a macro to execute.

[arangogs]

Hi Talkvinth,
I hope this is what you are looking for or at least a start.

Add in a Macro or module to your workbook and copy the following code in. The shortcut I have added is ctrl+c

Sub DateValidator()
'
' DateValidator Macro
' VBAExpress response
'
' Keyboard Shortcut: Ctrl+c
'
Dim i As Integer

For i = 2 To Selection.Row
If Range("Y" & i).Value = "" Or Range("X" & i).Value = "" Then
MsgBox "No numeric value found!"
Else

If Range("Y" & i).Text > 0 Then
Range("AB" & i).Value = CDate(Range("Y" & i).Text)
Else
Range("AB" & i).Value = CDate(Range("X" & i).Text)
End If


Range("AC" & i).Value = CInt(Range("AB" & i).Value - Date)

Select Case Range("AC" & i).Value
Case Is < 0
Range("AD" & i).Value = "2.Past"
Case 1 To 30
Range("AD" & i).Value = "1.0.30"
Case 31 To 60
Range("AD" & i).Value = "3.31-60"
Case Is > 60
Range("AD" & i).Value = "4.60+"
End Select
End If

Next i
End Sub

[Bob Phillips]

Public Sub AddFormulas()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "Y").End(xlUp).Row
.Range("AB2").Resize(lastrow).Formula = "=IF(Y2>0,Y2,X2)"
.Range("AC2").Resize(lastrow).Formula = "=AB2-Today()"
.Range("AD2").Resize(lastrow).Formula = "IF(AC2>0,""2.Past"", IF(AC2<30,""1.0.30"",IF(AC2<60,""3.31-60"",""4.60+"",""""))))"
End With
End Sub