Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
SUM() and OFFSET() CAUSES #VALUE | Excel Worksheet Functions | |||
Max Offset | Excel Discussion (Misc queries) | |||
offset | Excel Worksheet Functions |