1. 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. 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. 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

Posting Permissions

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