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]

Replied to this thread in the VBA forum.

 

[SimonDavis]

Does that answer solve the problem, or do you want the answer here?!

The way to 'grab' and manipulate a value is to use a variable. The code you would use for your example above would be (let's say for example you want to add 2 days to the original value and send it back to the empty cell):

dim date1, date2 as variant
date1 = Range("f2&quot.Value
date2 = date1 + 2
range("g2&quot.value = date2

This is an absolute answer, working on two specific cells. You might want to look at 'offset', where you would pick up a value from one cell, and place the result in the cell next to it, regardless of where the original cell is.

Also, it is not particularly efficient to declare a variable as a 'variant', but that will allow different types of data to be worked with. Look at variables in help for more info, or if that doesn't help, post back.

 

[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

 

[JVFriederick]

Err . . .why not use the formula :

=INT(NOW()-A2)

where A2 is the first record of the effective date.
Copy the formula for all records required.

You can divide by 365 for years or 30.42 for months.

 

[Tronsliver]

JVFriederick,

This will not work for what I want the program to accomplish thanks anyway.

 

[mymou]

Try using a function if possible. This will appear in the user defined functions - and is more flexible than using loops.

Public Function CalcDates(EffectiveDate, TheDate)

TheDate = DateDiff(&quot;m&quot;, EffectiveDate, Now)

If TheDate >= 12 Then
TheDate = Left(Str(TheDate / 12), 4)
CalcDates= TheDate + &quot; Yr&quot;
Else
If TheDate < 12 Then
TheDate = Str(TheDate)
CalcDates = TheDate + &quot; Months&quot;
End If
End If

End Function

 

[SimonDavis]

I think the last answer is probably the elegant way to do this, but I am also guessing you want this to repeat itself, in which case you can use the offset function to move the cursor, the syntax to move from one cell to the one below is something like:

ActiveCell.Offset(1, 0).Activate

This will move the cursor down 1 row, and accross zero columns. You can adjust the numbers, including negatives to move back/up as desired. I would maybe put that into a for/next loop around the code above (there might be a more modern way, but I don't know it!)

 

[mymou]

You might be able to use something like

Range(&quot;A1&quot.AutoFill Destination:=Range(&quot;A1:A9&quot

Once the function is in place (A1).

This is easy if you know where everything is going to be.


Stew

 

[Tronsliver]

I used the function provided by JVFriederick and it works great. When ever I update the &quot;EffectiveDate&quot; cell it automatically updates the offset. Thanks to all.

Tronsliver

 

[JVFriederick]