VB in Excel

[Tronsliver]

OK this is new to me. What I want to accomplish is this. Build a function in the VB wookbook that takes a date value from a cell on an active worksheet and do some calculations and then send the result back to another blank cell. For example:

Effective Date Time in Position
04 Dec 00
01 May 01
01 Jul 95
01 Sep 99
01 Oct 99

The first date listed would be cell F2 (04 Dec 00) How can I get this date into the VB function where it can be manipulated? Then how do I get the result back to cell G2 (Time in position)?

 

[dsi]

This should get you started.

Public Sub TweakDates()
    Dim dDate As Date
    Dim NewValue
    
    dDate = Range("F2").Value
    
    'Calculate NewValue
    
    Range("G2").Value = NewValue

End Sub

 

[Tronsliver]

OK This is what I've done (Remember I'm working from an Excel spreadsheet). Again my intent is to compare a date that an employee was put into a position with todays date and then put the time differential in another cell in the form of months or years. This is the test code:


Public Sub CalcDates()
Dim EffectiveDate, TheDate As Variant

EffectiveDate = Range("F6&quot.Value
TheDate = DateDiff("m", EffectiveDate, Now)

If TheDate >= 12 Then

TheDate = Left(Str(TheDate / 12), 4)
Range("G6&quot.Value = TheDate + " Yr"

Else

If TheDate < 12 Then

TheDate = Str(TheDate)
Range(&quot;G6&quot.Value = TheDate + &quot; Months&quot;

End If
End If
End Sub

Now this works fine for one person but how do I buid a loop that goes throguh and runs the procedure for numerous employees? Here is how the spreadsheet is layed out

A B

1 4 Jan 00

2 4 Dec 01

3 5 Feb 00

Now what I need the procedure to do is go thru and calculate B for each date listed and then put the resulting string in B1 thru B3. I assume it would be a For Next loop of some sort but I don't know how to rotate thru the loop and access the cells.

Thanks

 

[innmedia]

Hi,

Here is some general code to loop through a column of data on a spreadsheet. Note the difference between offset(0,1) and offset(1,0):



cells(1,1).activate ' put your start cell row,column here
do until activecell=&quot;&quot; ' loop until no more values
''''''
EffectiveDate = activecell
'put answer one column over
activecell.offset(0,1) = DateDiff(&quot;m&quot;, EffectiveDate, Now)

' now go down one row and activate
activecell.offset(1,0).activate
loop

'done