reading line by line

Dear all,

I have a big problem, but I don't known how excel can solve it.

I have a sheet with like it:

process       process_line    status    values_USD
0255155           10                 44               10
0255155           20                 44               20

0255156            10                 33              11
0255156            20                 44              21

0256157            10                  33             12
0256157            20                  33             22
0256157            30                  33             23

0256158            10                 22              13
0256158            20                 33              14
0256158            30                 44              15

And I need a code to do the following:

1)	Create unique process numbers;
2)	Read line by line of each process:
If the status of all lines are 44 then the excel put "ok - invoiced!"

Process        Status                 values_USD
 0255155      ok - invoiced          30

If the status of one line is 33 then the excel put  "verify the line
10 - status 33"

Process       Status                         values_USD
0255156 verify the line 10 - status 33           32

If the status of all lines are 33 the excel put  " ready to invoice"

Process    Status                values_USD
0255157    ready to invoice              57

If in any contains status 22 the excel put "missing components"
Process Status                         values_USD
0255158     missing components        42

It's clear to me the instruction "if", but I don't known how a code of
VBA can read line by line if each process in the sheet to check the
status and after return the "new status" of the process.

Thanks a lot!!!

Andr=E9.

0
gatarossi (81)
7/24/2007 2:33:17 PM
excel 39879 articles. 2 followers. Follow

3 Replies
564 Views

Similar Articles

[PageSpeed] 27

Hi Andre,

The following code should process your data as per your requirements.

Make a copy of your data into a new workbook so that if anything goes wrong, 
you will not loose valuable data.

For the macro to work in it's present form,  you will need to have the data 
on Sheet1, columns A to D. (That is the column header 'process' needs to be 
in cell A1, 'process_line' in cell B1, 'status' in cell C1 and 'values_USD' 
in cell D1.)

The output will go to Sheet2.

I have assumed that you have one row spacing between each process group. If 
there are no blank rows that will not matter but if there is more than 1 
blank row between each process group then it will not work.

Test it and see if it works and I'll appreciate it if you let me know the 
result.



Sub Process_Data()

Dim processCol As Range
Dim processStart As String
Dim processEnd As String
Dim processGrp As Range
Dim i As Integer
Dim j As Integer
Dim processId As String
Dim count44Status As Integer
Dim count33Status As Integer
Dim count22Status As Integer
Dim processLine
Dim sumValuesUSD
Dim statusMsge
Dim dataSht As Object
Dim outputSht As Object


'Edit Sheet1 in following line to match your sheet name
Set dataSht = ThisWorkbook.Sheets("Sheet1")
dataSht.Select
dataSht.Cells(1, 1).Select

Set outputSht = Sheets("Sheet2")
'Insert column headers on sheet 2
'Edit sheet name to match sheet where
'you want the output.
With outputSht
    .Cells(1, 1) = "Process"
    .Cells(1, 2) = "Status"
    .Cells(1, 3) = "Sum of Values_USD"
    .Columns(1).NumberFormat = "@"
End With

'Find last cell with Id in process column and
'name the range containing the process Id's.
'Note: One row added with offset
Set processCol = dataSht.Range("A2", Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0))
processId = processCol.Cells(1, 1)
processStart = processCol.Cells(1, 1).Address

With processCol
    For i = 1 To .Rows.Count
        If processId <> .Cells(i, 1) Then
            processEnd = .Cells(i - 1, 1).Address
            Set processGrp = dataSht.Range(processStart, processEnd)
            
            With processGrp
                'Insert Process Id in first column on sheet 2
                outputSht.Cells(Rows.Count, 1).End(xlUp). _
                    Offset(1, 0) = .Cells(1, 1)
                
                'Test/Count occurrences of Status 44, 33 and 22
                count44Status = 0   'Initialize counter
                count33Status = 0   'Initialize counter
                count22Status = 0   'Initialize counter
                sumValuesUSD = 0    'Initialize sum
                
                For j = 1 To .Rows.Count
                    sumValuesUSD = sumValuesUSD + .Cells(j, 4)
                    
                    'Note: Unsure if status is numeric or text
                    'Following line handles either text or numeric
                    Select Case Format(.Cells(j, 3), "00")
                        Case "44"
                            count44Status = count44Status + 1
                        Case "33"
                            count33Status = count33Status + 1
                            processLine = .Cells(j, 2)
                        Case "22"
                            count22Status = count33Status + 1
                    End Select
                    
                Next j
                
                'Populate statusMsge based on Tests/Counts
                If count44Status = .Rows.Count Then 'All status = 44
                    statusMsge = "OK Invoiced"
                End If
                
                If count33Status = .Rows.Count Then 'All status = 33
                    statusMsge = "Ready to invoice"
                End If
                
                'One or more status = 33 but not all status 33
                If count33Status > 0 And _
                    count33Status < .Rows.Count Then
                    
                    statusMsge = "Verify the line " & _
                        processLine & " - status 33"
                End If
                
                If count22Status > 0 Then 'At least one status = 22
                    statusMsge = "Missing components"
                End If
                
                outputSht.Cells(Rows.Count, 1).End(xlUp). _
                    Offset(0, 1) = statusMsge
                
                outputSht.Cells(Rows.Count, 1).End(xlUp). _
                    Offset(0, 2) = sumValuesUSD
                
                'Skip 1 blank row if necessary.
                If Len(Trim(processCol.Cells(i, 1))) = 0 Then
                    i = i + 1
                End If
                
                processStart = processCol.Cells(i, 1).Address
                processId = processCol.Cells(i, 1)
            End With
            
        End If
    Next i
    
End With
outputSht.Select
Columns("A:C").Columns.AutoFit
Cells(1, 1).Select
End Sub



Regards,

OssieMac


"gatarossi@ig.com.br" wrote:

> Dear all,
> 
> I have a big problem, but I don't known how excel can solve it.
> 
> I have a sheet with like it:
> 
> process       process_line    status    values_USD
> 0255155           10                 44               10
> 0255155           20                 44               20
> 
> 0255156            10                 33              11
> 0255156            20                 44              21
> 
> 0256157            10                  33             12
> 0256157            20                  33             22
> 0256157            30                  33             23
> 
> 0256158            10                 22              13
> 0256158            20                 33              14
> 0256158            30                 44              15
> 
> And I need a code to do the following:
> 
> 1)	Create unique process numbers;
> 2)	Read line by line of each process:
> If the status of all lines are 44 then the excel put "ok - invoiced!"
> 
> Process        Status                 values_USD
>  0255155      ok - invoiced          30
> 
> If the status of one line is 33 then the excel put  "verify the line
> 10 - status 33"
> 
> Process       Status                         values_USD
> 0255156 verify the line 10 - status 33           32
> 
> If the status of all lines are 33 the excel put  " ready to invoice"
> 
> Process    Status                values_USD
> 0255157    ready to invoice              57
> 
> If in any contains status 22 the excel put "missing components"
> Process Status                         values_USD
> 0255158     missing components        42
> 
> It's clear to me the instruction "if", but I don't known how a code of
> VBA can read line by line if each process in the sheet to check the
> status and after return the "new status" of the process.
> 
> Thanks a lot!!!
> 
> André.
> 
> 
0
OssieMac (238)
7/25/2007 9:48:01 AM
Thanks a lot!!!

Andr=E9.

0
gatarossi (81)
7/25/2007 12:36:20 PM
Dear OssieMac,

This code really works!!!! Thanks for your help. I hope one day known
vba like you!

Thanks!!!

Andr=E9.


0
gatarossi (81)
7/25/2007 4:24:04 PM
Reply:

Similar Artilces:

Reading .wks file
Greetings...according to the Excel "help" file, as well as the file extension listing, I *should* be able to read an *.wks file, but Excel insists that it cannot. I am pretty sure the file was created in Microsoft Works. Is there a converter somewhere that I can download/install? Cheers - S2 Excel can read Works 2.0, not later. You have to save them in Works as excel files or Works 2.0 or get a commercial converter. -- Regards, Peo Sjoblom "Skip Stocks" <anonymous@discussions.microsoft.com> wrote in message news:AFC110E0-641D-4D87-9464-B930CC41CF02@microsoft....

File won't open as read only
I have a file that is in use, but another person opens it and it doeasn't display the "file is in use" message. Is there a setting or fix? Hi have you shared this file? -- Regards Frank Kabel Frankfurt, Germany John wrote: > I have a file that is in use, but another person opens it > and it doeasn't display the "file is in use" message. Is > there a setting or fix? The file is on a network share. The share has all the appropriate permissions. >-----Original Message----- >Hi >have you shared this file? > >-- >Regards >Frank Ka...

How to give other users read-only access to Calendar
I want to allow the group Everyone to have read-only access to a calendar in a certain mailbox. I can do this by granting the permission 'Full mailbox access' (under 'Mailbox rights', under 'Exchange Advanced', for the particular user). However this also allows people to to do everything (ie: they become read-write users). I notice that every mailbox in the system has 'Read permissions' granted to group Everyone. This does not allow other people to open items in the mailbox, but as I understand it, permits Exchange Server and Outlook to do shared meetin...

Read mail arn't marked as read anymore
After an SP upgrade of my Office 2000 the priviewed mail doesnt get marked as read anymore. I have tried to change the time (Tools->Options->Priview pane) from 2 -> 3 -> 4 seconds but nothing works. The only way to mark a mail as read is either to open it or right click it and chose Mark as read. Since I only use the priview pane this is very anoying for me. Is this a bug or has some setting changed with the SP? ...

reading confirmation
Good day, I have a problem with outlook. When they send a message to me that demands the shipment of a reading confirmation, even if I accept, the reading confirmation does not come received from the sender. Someone knows from what depends and in which way I can resolve the problem? Thanks for the eventual answers. Niki In news:eht7fo$251$1@fata.cs.interbusiness.it, Niki <nicola.pantaleo@yahoo.it> typed: > Good day, > > I have a problem with outlook. When they send a message to me that > demands the shipment of a reading confirmation, even if I accept, the > read...

Word 2007 line count
Where is the line count recorded in Word 2007? Thanks! What do you mean by "recorded"? If you want to add line numbers to your pages, go to Page Layout tab, Page Setup group, Line Numbers. If you want to see what line you're in as you type, right-click on the Status Bar (bottom of the document window) and check it (and other useful things) on the list that appears. (This appears to show only the line on the present page, not a cumulative total.) On Jan 15, 2:50=A0pm, Siouxzqmc <Siouxz...@discussions.microsoft.com> wrote: > Where is the line count record...

"Unblock" feature should be optional when reading e-mail in CRM
When viewing e-mail messages in CRM, a line appears saying "Unblock" to allow the full message content to be read. Can this be made a configurable server or security setting? We are trying to reduce "clicks" as much as possible. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

The memory could not be "read".
I'm at work yesterday afternoon responding to e-mail on my desktop, and Adobe pops up from the toolbar with a notice that there is an update available for Acrobat. I accept the download, keep plugging away at my e-mail, and install it when it's done. It runs through the install, tells me that I should restart, but I ignore it and keep working. It's near the end of the day and I'm going to be going home soon anyways. I forgot to turn it off when I left. Came in this morning, restarted it through the Start menu, and rebooted. Upon reaching the "Ctrl+Alt+Del" ...

publisher document to pdf format without horizontal lines
I created a map with text boxes, and pictures and use Print to pdf to create an unalterable map for general users. However, when I do this there are multiple orizontal grey lines on the pdf output. How do I save a publisher document as a pdf document without these lines? Are you printing to Acrobat or some other PDF printer? If you print your publication to a regular printer, does the output have the lines? Can you send the file to me? I will test your file to see if I get the same result. mary-sauer at columbus.rr.com -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://...

how can i copy a document to a CD without making it read only?
HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? You can't. It is not the file, but the media, that is read only. Even CD-RW media does not allow editing a file on the CD. Copy te file from CD to HD, mak edits and if a CD-RW you should be able to burn the edited file back to the CD. hth "DON" wrote: > HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? ...

Unable to Read Japanese Email
I correspond with several Japanese users and can read emails from some of them without a problem, but emails from others are nothing but a series of ?????. In one case, I can read one email but not another from the same sender. Changing the Encoding doesn't help. Sending emails in Japanese to them without a problem...they can read it fine. Would appreciate any suggestions/solutions as I'm stumped. I'm using Outlook 2003 on a Windows XP Home operating system. Japanese language support is installed. ...

Transferring read e-mail to another folder
Hello I was wondering whether there was a way in which I could automatically transfer my e-mail that has been read into another folder, such as "Old e-mail" or something like that? Thanks ...

Pie chart data labels and lines disappear
Help! I have a couple of pie charts in a file that I've set data labels and values to show. Everytime I close the file, when I reopen it, they're gone, although when I go to chart option, both are checked as being displayed. If I uncheck the boxes, then recheck them, the labels and values show again. Everytime I close the file, the same things happen. What's up with this? ...

Outlook not marking read emails as read
Hi - We have 1 computer that is doing the oddest thing, anytime the customer reads the last email in his box, exit's out of email and then comes back in, that email is now marked as unread - it's the weirdest thing I have seen in a long time. I uninstalled office XP, rebooted, then reinstalled and applied the 2 service packs, hoping that would fix it, but it didn't. So now I'm stuck and was wondering if anybody out there has any thoughts on what to do.... thanks! Gerri Urban gurban@ci.broomfield.co.us ...

Pivot Table missing lines
Hi Folks! I have a pivot table derived for a worksheet of about 250 lines. When I add additional rows to the work sheet, sort it, and sav it followed by refreshing my pivot table, I'm missing the last two item in this pivot table. I can repeat this every time. When I creat another identical pivot table for the same updated worksheet, my las to items are in this new pivot table. Always the last two items! Any suggestions. I thank you in advance for your feedback. Best Regards, Slewrat -- Slewrat ----------------------------------------------------------------------- Slewrate's ...

Server: Msg 207, Level 16, State 3, Procedure PROC_CLN_DATA, Line
Need help. Received the above error when I execute the following Thanks. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO --drop proc PROC_CLN_DATA ALTER PROCEDURE PROC_CLN_DATA AS BEGIN set nocount on DECLARE @SYS_DATE DATETIME, @sys_dt char(10),@l_count int,@sys_dt1 datetime ...

Suggested reading
Any suggested reading for Access 2003 VBA? I have both "Step by Step Access 2003" And "Microsoft Access 2003 VBA for Dummies" (how Ironic) and niether have been very helpful. Try this book. It's a winner: Access 2003 VBA Programmer's Reference by Patricia Cardoza, Teresa Hennig, Graham Seach, and Armen Stein http://www.amazon.com/Access-2007-Programmers-Reference-Programmer/dp/0470047038/ref=sr_1_1/104-1181757-2327103?ie=UTF8&s=books&qid=1185824619&sr=8-1 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.access...

Message(s) Not Being Marked As Read
I have "Mark message read after displayed for" '0' seconds checked. But in my Junk e-mail folder the messages don't get marked as read when I select them, only when I open them. I'm assuming this is because there isn't a the reading pane for the Junk e-mail folder even though I have "Show reading pane" checked in "Layout" a reading pane doesn't display in the Junk email right pane. Is this normal? I'd rather just click on the message instead of opening the message to mark it as read. James > I'd rather just c...

Controling READ ONLY and READ/WRITE mode when opening a project PS
Hello All, I was looking for a way to force users to select between READ ONLY and READ/WRITE instead of it defaulting to READ/WRITE when opening projects in MS Project 2007. Any suggestions would be a huge help. Thanks, Eric Eric -- Short of using custom software development, there is no way to force this issue with your PMs. If you want to try the custom software development route, then please repost your message in the microsoft.public.project.developer newsgroup. Otherwise, make this a training and performance issue with your PMs. Hope this helps. -- Dale A. Ho...

Read/Not read
Hello We have an exhange-server environment. The Boss' secretary need's to be able to read the Boss' mail, without the messages being marked as read, within outlook 2003. She can access the Boss mail, but all mail she reads is being marked as read, hence the boss can not figure out what he has seen/not seen. What is the solution? Thanx a lot /Jan Hi Towli. There is no way to marked as unread automatically, Just she should be tick the unread option on the pop up menu after she open the her boss e-mail. Once you right click button one of e-mail on the e-mail list, you w...

read an ascii file with fopen
I try to open with fopen and read an ascii file, line by line, but get garbage - among the right data in the CString variable that is filled with this line data. Can someone copy&paste the right code how to so that? Thanks in advance. Mark "Mark" <mark@chasan.ar> wrote in message news:%23sPmEzsgGHA.2208@TK2MSFTNGP05.phx.gbl... > I try to open with fopen and read an ascii file, line by line, but get > garbage - among the right data in the CString variable that is filled with > this line data. > > Can someone copy&paste the right code how to so that?...

Programatically reading a XSD File
Hello, Let us say I have a schema file like this sample below. How would I using ..NET classes be able to read this XSD file and get all the values for each element, such as "name", "type", "minoccurs" etc.,? I would appreciate if somebody can help me with some sample code. Thanks for your help. Ganesh ********************* <?xml version="1.0" standalone="yes"?> <xs:schema id="Account_Did" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata&...

after saving to cd many times. now says read only?
i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of a sudden it now say's the file is read only. although i can still save to my hard drive? nsturre wrote: > i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of > a sudden it now say's the file is read only. although i can still save to my > hard drive? Unless you have formatted your CDRW for packet writing, then yes, it will be Read only. CDROM = CD Read Only Memory! -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk Just ...

Synchronizing and Read Only
I am trying to set up a one note notebook on a desktop that I can access from a notebook computer as a synchronized one note notebook. I am using Windows 7 and have a Homegroup set up on the network. I have set up a Test notebook on the desktop and when I attempt to open it from the notebook computer through the Homegroup it always opens as read only. I have looked at the permissions of the folder on the desktop and ensured that the folder is not read only but everytime I attempt to open it it reverts back to read only (permissions show as a blue square not a check). I have also ...

Read Receipts being stored up and sent months later
We have a person outside the organization that sends email to a person in our organization and they request a read receipt. Today, the person outside the organization recived months of read receipts from the person in our organization. Outlook 2002, Exchange 2000. Any thoughts? Thank you did the person mark them all read today? Switch mailboxes? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net ...