reading line by line

[gatarossi]

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é.

 

[OssieMac]

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]

Thanks a lot!!!

André.

 

[gatarossi]

Dear OssieMac,

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

Thanks!!!

André.