Hi,

I’ve got a situation, which I thought would be pretty simple, but it
wasn’t.

Brief:
I do a report monthly, which defines how much files are pending.
Recently I’ve been asked to age the items. Normally that would be
easy, if I have a timestamp or date to work off. But there is no date.


So what I’ve done is inserted previous reports as different tabs into
one worksheet, going back to July 04. Then I’ve inserted a new tab,
which looks up July 05 in July 04 thought to June 05, it will bring
back the policy number or blank if the statement is false.

I’ve also got a col, which counts the row of all policy numbers – Which
gives you the age? Unfortunately that’s not the case. Cases appear in
July 04, Aug 04 and Sept 04 and then Oct 04, Nov 04, Dec 04, Jan 05,
Feb 05, Mar 05 and April 05 May is blank then the case may appear in
June 05, making this case 1 Month old.

Question:

Is there anyway to count from right to left and stopping when it blank?


+-------------------------------------------------------------------+
|Filename: ageprofile.PNG |
|Download: http://www.excelforum.com/attachment.php?postid=3676 |
+-------------------------------------------------------------------+

--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=393287

Hi, considering that I'm also a beginner, I always use loop to count
something until certain criteria met. In this case, until the cells
contains blank.

You can try the code below if suit you. Please accept that I'm
learning too.

Sub CountFromRightToFirstBlank()

Dim lngCol As Long
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngCount As Long

'Considering that Column A always have value
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
'the last col that will be counted
lngCol = 5

'start counting from row 2
For lngRow = 2 To lngLastRow
'loop from col E,D,C, B
'you can change the condition that suit you
Do Until Cells(lngRow, lngCol).Value = "" Or lngCol = 1
lngCount = lngCount + Cells(lngRow, lngCol).Value
lngCol = lngCol - 1
Loop

'At last, if there is value in the count,
'write the result in column F
If lngCount < 0 Then
Cells(lngRow, "F").Value = lngCount
End If
Next

End Sub

Best regards,


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=393287

i'll give it a go, i'll let you know.......appreciate the reply.


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=393287

HI hideki,

I've amended the code:

Changed the 'the last col that will be counted, from 5 to 33
Changed Cells(lngRow, "AG").Value = lngCount (from f to ag)

But I get a type mismatch - on line 13

lngCount = lngCount + Cells(lngRow, lngCol).Value


Any Ideas?

Thanks again.


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=393287

Does in line 13 contains other than a number? This will stop the program
since therie was no error checking.

BTW, I found a mistake in my previous code (but nothing related to your
error).
The variable need to be set back to it default like below.

For lngRow = 3 To lngLastRow
Do Until Cells(lngRow, lngCol).Value = "" Or lngCol = 1
lngCount = lngCount + Cells(lngRow, lngCol).Value
lngCol = lngCol - 1
Loop
If lngCount < 0 Then
Cells(lngRow, "F").Value = lngCount
End If
lngCount = 0
lngCol = 5
Next


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=393287

Hi,

I've got the code to run, without any errors. But nothing seems to
happen. I should get a counts in column AG. But nothing



Sub CountFromRightToFirstBlank()

Dim lngCol As Long
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngCount As Long

'Considering that Column A always have value
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
'the last col that will be counted
lngCol = 19

'start counting from row 2
For lngRow = 2 To lngLastRow
'loop from col E,D,C, B
'you can change the condition that suit you
Do Until Cells(lngRow, lngCol).Value = "" Or lngCol = 1
lngCount = lngCount + Cells(lngRow, lngCol).Value
lngCol = lngCol - 1
Loop

'At last, if there is value in the count,
'write the result in column F
If lngCount < 0 Then
Cells(lngRow, "AG").Value = lngCount
End If
lngCount = 0
lngCol = 19
Next

End Sub


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=393287

Sorry for my late reply. The lngcount=0 is okey since its initialise the
number back to 0 before start other count.

I'm not sure how your data constructed but this procedure will run
until it found the first blank cell. Since you started from colum 19
(S), your data should exist in column S, otherwise it will not counted
because its already found the blank cell. For example, you have data in
column S,R,Q,P,O this five cells will be counted for that row. On the
next row, say you have data in column S,R - only two column will be
counted.

I'm sorry if I misunderstood you. Here I attach how it works. The count
result is at column L. I made it short. As you could see in row 5, only
the fives at right only counted (from column K to G).

Feel free to tell me if this is still not working or I misunderstood
you. I'm also a beginner and like to try to solve it.


+-------------------------------------------------------------------+
|Filename: book1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=3713 |
+-------------------------------------------------------------------+

--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=393287