VBA Coding needs advice - To Simplify

[fabianj]

Hi,

Please find the attached excel sheet.


My objective is to get values from Sheet1 to Data for individual people. I have to find the "no. of 1's, no. of 2's for each people....


I did the coding but I feel this is not the right way. So far I am getting values individually in values_1(), values_2() so on... can these be grouped to one (in a for loop), it would be great if you can help and advice me on this.


Problems:


1. Need advice to group all the Values_1(), Value_2() ...
2. If re-run the macro with sheet1 as active window I get the results which supposed to be on "Data" copied on sheet1 itself.




Please note: Please run the macro (mainproject () )once you open the sheet.


Awaiting for your earnest reply.


Thanks
Fabian J
15845

[Paul_Hossler]

Maybe something like this



Option Explicit

Sub InProgress()
Dim rRaw As Range, rSummaryStart As Range
Dim iAgent As Long, iNumber As Long, N As Long

Application.ScreenUpdating = False

Set rRaw = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

With Worksheets("data")
Set rSummaryStart = .Range("B3")
Range(rSummaryStart, .Cells(.Rows.count, .Columns.count)).ClearContents
End With

With Worksheets("Data")
For iAgent = 1 To rRaw.Columns.count
.Cells(2 + iAgent, 2).Value = rRaw.Cells(1, iAgent).Value
Next iAgent

For iNumber = 2 To rRaw.Rows.count
For iAgent = 1 To rRaw.Columns.count
If Len(rRaw.Cells(iNumber, iAgent).Value) = 0 Then
N = 10
Else
N = rRaw.Cells(iNumber, iAgent).Value
End If

.Cells(iAgent + 2, N + 2).Value = .Cells(iAgent + 2, N + 2).Value + 1
Next iAgent
Next iNumber

End With
Application.ScreenUpdating = True

Worksheets("data").Select

MsgBox "Done"

End Sub

[fabianj]

Thanks a Lot Paul.... you are Great !!!