Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula (Office 2000)

    I need a formula to perform the following task. If A1 is blank and if D1 is blank, put the number 8 in A1. If A1="R", don't put anything in A1 and don't erase the "R". If D1 is not blank, put the results of (8-D1) in A1. It's a timesheet.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (Office 2000)

    If you want to do this without code, you can't, because if the cell a1 has the value R, it can't hold a formula as well.

    But

    =IF(A1="R","R",IF(AND(ISBLANK(A1),ISBLANK(D1)),8,8-D1))

    placed in B1 will do what you ask.

    and if you want code to do the above, then this should do the trick:

    Sub timesheet()

    If ActiveSheet.Cells(1, 1).Value = "R" Then
    ElseIf IsEmpty(ActiveSheet.Cells(1, 1).Value) _
    And IsEmpty(ActiveSheet.Cells(1, 4).Value) Then
    ActiveSheet.Cells(1, 1).Value = 8
    Else
    ActiveSheet.Cells(1, 1).Value = 8 - ActiveSheet.Cells(1, 4).Value
    End If

    End Sub


    However, if you need to loop down column a, then


    Sub timesheet2()

    Dim intRowNumber As Integer
    Dim intWhatEver As Integer

    intWhatEver = 30 ' you'll need to set this for your timesheet

    For intRowNumber = 1 To intWhatEver

    If ActiveSheet.Cells(intRowNumber, 1).Value = "R" Then
    ElseIf IsEmpty(ActiveSheet.Cells(intRowNumber, 1).Value) _
    And IsEmpty(ActiveSheet.Cells(intRowNumber, 4).Value) Then
    ActiveSheet.Cells(intRowNumber, 1).Value = 8
    Else
    ActiveSheet.Cells(intRowNumber, 1).Value = _
    8 - ActiveSheet.Cells(intRowNumber, 4).Value
    End If

    Next 'intWhatEver


    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formula (Office 2000)

    AHuffman

    Try this: =IF(AND(A1="",D1=""),8,IF(UPPER(A1)="R","R",IF(D1< >"",8-D1,"Need this part")))

    I did not really understand what you are trying to do, and I am not sure how you can write a formula to change another cell's value, even conditoinal formating might not be able to do it. Plus you have what we call a curcilar reference, where one cell's value depends on that cell's value. Example when you say <<< <font color=red> If A1 is blank and if D1 is blank, put the number 8 in A1. </font color=red>. Cell A1's value of 8 depends on its value of Blanck.

    Maybe my English is not working right today, I feel <img src=/S/doze.gif border=0 alt=doze width=15 height=15> but this forumla may help you get started.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •