Offset
 

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

Offset
 

Dave S wrote:
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.


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

Offset
 

Thanks for your time Bill. I appreciate your response.


-----Original Message-----
Dave S wrote:
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.


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

.