counting from left to right and stopping when blank

[harpscardiff]

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 |
+-------------------------------------------------------------------+

 

[hideki]

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

You can try the code below if suit you. Please accept that I'
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

 

[harpscardiff]

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

 

[harpscardiff]

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.

 

[hideki]

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

 

[harpscardiff]

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

 

[hideki]

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

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

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

Feel free to tell me if this is still not working or I misunderstoo
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
+-------------------------------------------------------------------