1. ## Formula Help (2000)

Hi all,

Could someone take a look at this and tell me if they see anything wrong with the structure of this If Statement?
If Sheets("logs").Cells(5, 6).Value = " A" And _
Sheets("logs").Cells(5, 9).Value = "UNIT 1" And _
Sheets("logs").Cells(5, 10).Value = "Couple 1" Then
Sheets("charts").Cells(4, 25).Value = Sheets("charts").Cells(4, 25).Value + 1
End If
If Sheets("logs").Cells(5, 6).Value = " A" And _
Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
Sheets("logs").Cells(5, 12).Value = "FAR" Then
Sheets("charts").Cells(5, 25).Value = Sheets("charts").Cells(5, 25).Value + 1
End If
If Sheets("logs").Cells(5, 6).Value = " A" And _
Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
Sheets("logs").Cells(5, 12).Value = "FAR Center" Then
Sheets("charts").Cells(6, 25).Value = Sheets("charts").Cells(6, 25).Value + 1
End If
If Sheets("logs").Cells(5, 6).Value = " A" And _
Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
Sheets("logs").Cells(5, 12).Value = "NEAR Center" Then
Sheets("charts").Cells(7, 25).Value = Sheets("charts").Cells(7, 25).Value + 1
End If
If Sheets("logs").Cells(5, 6).Value = " A" And _
Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
Sheets("logs").Cells(5, 12).Value = "NEAR" Then
Sheets("charts").Cells(8, 25).Value = Sheets("charts").Cells(8, 25).Value + 1
End If

My problem with this is that I it doesn't see the value in the cells, and thus doesn't give add 1

Thanks,
Darryl.

2. ## Re: Formula Help (2000)

One possible thing that I see is that the first If statement has this:

<pre> Sheets("logs").Cells(5, 9).Value = "UNIT 1"
</pre>

and all the rest have this:

<pre> Sheets("logs").Cells(5, 9).Value = "Unit 1"
</pre>

The difference in Upper/Lower case letters in the word "Unit" could be causing you problems.

If that is not your problem, could you upload a workbook that shows the problem and explain what you expect to happen when you run the macro.

One other question. Is there a reason that you are using:

<pre> Sheets("logs").Cells(5, 9).Value
</pre>

rather than the much easier to read:

<pre>Sheets("logs").Range("I5").Value
</pre>

I also think that those If statements would be easier to read and more efficient if they were written like this, which I think does the same thing:

<pre> If Sheets("logs").Range("F5").Value = " A" And _
Sheets("logs").Range("I5").Value = "UNIT 1" And _
Sheets("logs").Range("J5").Value = "Couple 1" Then
Sheets("charts").Range("Y4").Value = Sheets("charts").Range("Y4").Value + 1
Select Case Sheets("logs").Range("L5").Value
Case "FAR"
Sheets("charts").Range("Y5").Value = Sheets("charts").Range("Y5").Value + 1
Case "FAR Center"
Sheets("charts").Range("Y6").Value = Sheets("charts").Range("Y6").Value + 1
Case "NEAR Center"
Sheets("charts").Range("Y7").Value = Sheets("charts").Range("Y7").Value + 1
Case "NEAR"
Sheets("charts").Range("Y8").Value = Sheets("charts").Range("Y8").Value + 1
End Select
End If
</pre>

3. ## Re: Formula Help (2000)

Thanks Legare,

I tried everything to figure out why it wasn't working, I thought it might be the structure of the statement, I didn't think about upper/lower case, it now works. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

I am going to try the select case,

Thanks

Darryl.

4. ## Re: Formula Help (2000)

If case is an issue, you can convert to Upper case before you compare:
<pre> If UCase(Sheets("logs").Range("F5").Value) = " A" And _
UCase(Sheets("logs").Range("I5").Value) = "UNIT 1" And _
UCase(Sheets("logs").Range("J5").Value = "COUPLE 1" Then</pre>

Steve

5. ## Re: Formula Help (2000)

Question,

forgive me if I'm wrong, IS Ucase considered a variant? And with the use of that function does it hold that value in memory? so if it were an "a" it would read it as "A" am I correct, and the same for Lcase?

Thanks,
Darryl.

6. ## Re: Formula Help (2000)

UCase and LCase are functions that returns a variant(string) with all lower case letters converted to upper case and all other characters left unchanged.

#### Posting Permissions

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