Excel VBA - cell value problem

[Scott Ballinger]

I am having a problem where VBA code "cells(r,c).value" is coming bac
with blank when the cell clearly has a value.

The macro is sequencing through all rows and reading the value of
column that has been used as the key for a subtotal operation. I rea
the right(cells(r,c).value,5) and check if it is equal to "Total". Thi
works fine nearly all of the time, but occasionally it comes back wit
a blank value even when I can see the value of the cell in question i
"Test Total".

This is on excel 2000.

Any ideas?


-Scott Ballinge

 

[Trevor Shuttleworth]

Scott

might help if you post the code. Some of these guys seem to have psychic
powers and will very likely give you a solution without further information
but some of us (well, me at least) need a few more clues.

That said, one guess is that maybe you have one or more spaces to the right
of the word total.

Regards

Trevor

 

[Scott Ballinger]

Here's the code:

Headrow = 2
lrow = 150

For i = (HeadRow) To (lrow)

If Right((WS.Cells(i, 3).Value), 5) = "Total" Then
' insert copy code here
WS.Cells(i, 3).Copy
WS.Cells(i, 8).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End If


Column 3 was used as the key to a subtotal, so the values the cells i
that column on all subtotal rows ends with "Total". Of the 27 subtotal
I am picking up all but one.

troubleshooting with the addition of:
msgbox(i & WS.Cells(i, 3).Value) into this loop shows that all of th
other subtotal rows have the value I expect, but this one has a valu
of "".

Manually looking at the cell shows that it does have a value of "Tes
Total".

I am stumped on this one. Excel's subtotal function added these row
and the value.

Any help would be greatly appreciated.

Thanks,
Scott Balinge

 

[Trevor Shuttleworth]

Scott

two thoughts:

one, probably unlikely but the comparison is case sensitive Total <> total
<> TOTAL, etc

two, when the subtotals are inserted, the rows go beyond the 150 checked in
the code. Could be this as you don't say which of the totals lines is
missing. If it's in the middle, I'm all out of ideas. If it's at the end,
it could be this.

So, to cater for both of these potential problems, try this:

Sub Test()
Dim HeadRow As Long
Dim lRow As Long
HeadRow = 2
lRow = Range("C" & Rows.Count).End(xlUp).Row
For i = (HeadRow) To (lRow)
If UCase(Right((Cells(i, 3).Value), 5)) = "TOTAL" Then
' insert copy code here
Cells(i, 3).Copy
Cells(i, 8).PasteSpecial Paste:=xlFormulas
End If
Next
Application.CutCopyMode = False
End Sub

Regards

Trevor

 

[David McRitchie]

Besides VBA being case sensitive, another possibility that you have
not said that it is not a problem and I think this was mentioned,...
If you have spaces or other characters like possibly CHR(160) which
is the non-breaking space character you would not notice this since
text is by default left aligned.

You can check on your workbook with this formula:
="'*" & D6 & "*" & CODE(RIGHT(D6,1))

If it is not a matter of just fixing data, you can use
substitute and trim in your VBA..

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Scott

two thoughts:

one, probably unlikely but the comparison is case sensitive Total <> total
<> TOTAL, etc

two, when the subtotals are inserted, the rows go beyond the 150 checked in
the code. Could be this as you don't say which of the totals lines is
missing. If it's in the middle, I'm all out of ideas. If it's at the end,
it could be this.

So, to cater for both of these potential problems, try this:

Sub Test()
Dim HeadRow As Long
Dim lRow As Long
HeadRow = 2
lRow = Range("C" & Rows.Count).End(xlUp).Row
For i = (HeadRow) To (lRow)
If UCase(Right((Cells(i, 3).Value), 5)) = "TOTAL" Then
' insert copy code here
Cells(i, 3).Copy
Cells(i, 8).PasteSpecial Paste:=xlFormulas
End If
Next
Application.CutCopyMode = False
End Sub

Regards

Trevor