Offset

  • Thread starter Thread starter Dave S
  • Start date Start date
D

Dave S

Hello Gurus and newsgroup users.

Your kind assistance please.


I'm relatively new to Excel VBA so please bear with me if
the question is a little vague.

I have the formula below in Cells I3: I53 (each row
signifies a specific record)

=IF(A3="a","Done",IF(H3=TODAY(),"Today",IF(H3=" ","",IF
(A3="s","On Hold",IF(H3<TODAY(),"Late",NETWORKDAYS(TODAY
(),H3))))))

Also the same range has conditional formatting associate.

For a couple of reasons I want to convert this formula
into VBA.

I think I need to use a Select Case statement to encompass
all.

My question is:-

Do I use Offset to make the changes in the adjoining cells.

Many thanks for any advise you can give me.

DaveS
 
Dave said:
For a couple of reasons I want to convert this formula
into VBA.

I think I need to use a Select Case statement to encompass
all.

My question is:-

Do I use Offset to make the changes in the adjoining cells.
Click to expand...

If you mean you want to use a VBA Function in the formula in the cells
then the function can only change the cell from which it was called, by
returning a result to that cell.

If you want a macro to run down the 51 cells changing their value, then
that's fine. Something like this

Sub UpdateColumnI()
Dim I As Integer
Dim V
For I=3 To 53
If Cells(I, "A")="a" Then
V="Done"
ElseIf Cells(I, "H")=Date Then
V="Today"
ElseIf....
End If
Cells(I, "I")=V
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Back
Top