Help Looping Through Excel Columns & Rows

**Mark123456789** · 02-25-2013, 03:01 PM

hi Eveyone

I'm Mark. I am new to VB coding, I am having some difficulty in looping through excel rows and columns which i was hoping someone could help me with. I have some data shown below.

What i am trying to do is use the table on the left and populate the table on the right. The table on the left shows days Sat - Fri as the column headings and week numbers down each row. The data itself are shift numbers. What i am trying to do is re-organize the data so that days are shown as column headings and shift number is shown down each row. So i want to know which week number and day does each shift occur in.

For example, the table on the left shows wk45 as the first week number. within that week shifts 8, 1, 2, 16 and 33 occur but in different days.

i want the vb code to go to shift 1 on the left side side oftable and populate the wk number that shift occurs in the correct day. below is an example of what i mean.

(i have attached a copy of my spread with data in it.

SAT SUN MON TUES WED THURS FRI SAT SUN MON TUES WED THURS FRI
45 SHIFT 8 SHIFT 1 SHIFT 1 SHIFT 2 SHIFT 1 SHIFT 16 SHIFT 33 SHIFT 1 45 45 45
46 SHIFT 9 SHIFT 7 SHIFT 69 SHIFT 28 SHIFT 1 SHIFT 2 45
47 SHIFT 10 SHIFT 1 SHIFT 29 SHIFT 69 SHIFT 3
48 SHIFT 1 SHIFT 1 SHIFT 4
49 SHIFT 1 SHIFT 2 SHIFT 3 SHIFT 69 SHIFT 5
50 SHIFT 28 SHIFT 1 SHIFT 8 SHIFT 6
51 SHIFT 29 SHIFT 14 SHIFT 28 SHIFT 7
52 SHIFT 28 SHIFT 28 SHIFT 8 SHIFT 15 SHIFT 29 SHIFT 8 45
53 SHIFT 29 SHIFT 29 SHIFT 1 SHIFT 2 SHIFT 9 SHIFT 16 SHIFT 9 SHIFT 9
54 SHIFT 29 SHIFT 10 SHIFT 10
55 SHIFT 28 SHIFT 28 SHIFT 28 SHIFT 28 SHIFT 11
56 SHIFT 29 SHIFT 29 SHIFT 29 SHIFT 29 SHIFT 12
57 SHIFT 8 SHIFT 8 SHIFT 28 SHIFT 9 SHIFT 28 SHIFT 13
58 SHIFT 9 SHIFT 9 SHIFT 27 SHIFT 8 SHIFT 29 SHIFT 14
59 SHIFT 10 SHIFT 10 SHIFT 28 SHIFT 1 SHIFT 2 SHIFT 9 SHIFT 15
60 SHIFT 8 SHIFT 29 SHIFT 10 SHIFT 16 45
61 SHIFT 9 SHIFT 8 SHIFT 27 SHIFT 8 SHIFT 17
62 SHIFT 10 SHIFT 9 SHIFT 28 SHIFT 1 SHIFT 2 SHIFT 9 SHIFT 18
63 SHIFT 10 SHIFT 29 SHIFT 10 SHIFT 19
64 SHIFT 31 SHIFT 28 SHIFT 20
65 SHIFT 32 SHIFT 29 SHIFT 21
66 SHIFT 28 SHIFT 22
67 SHIFT 29 SHIFT 23
68 SHIFT 24
69 SHIFT 25
SHIFT 26
SHIFT 27
SHIFT 28
SHIFT 29
SHIFT 30
SHIFT 31
SHIFT 32
SHIFT 33 45
SHIFT 34
SHIFT 35
SHIFT 36
SHIFT 37
SHIFT 38
SHIFT 39
SHIFT 40
SHIFT 41
SHIFT 42
SHIFT 43
SHIFT 44
SHIFT 45
SHIFT 46
SHIFT 47
SHIFT 48
SHIFT 49
SHIFT 50
SHIFT 51
SHIFT 52
SHIFT 53
SHIFT 54
SHIFT 55
SHIFT 56
SHIFT 57
SHIFT 58
SHIFT 59
SHIFT 60
SHIFT 61
SHIFT 62
SHIFT 63
SHIFT 64
SHIFT 65
SHIFT 66
SHIFT 67
SHIFT 68
SHIFT 69
SHIFT 70

can anyone help at all, please can you comment the code as i want to understand it. Thank you

**mrice** · 02-25-2013, 05:36 PM

Try this (hopefully self explanatory)

Sub Test()
Dim Cell As Range
Set SourceRange = Range(Cells(2, 2), Cells(26, 8))
For Each Cell In SourceRange
    If Cell <> "" Then
        Number = Cells(Cell.Row, 1)
        DayValue = Cells(1, Cell.Column)
        
        TargetRow = Columns(10).Find(Cell, , xlValues, xlWhole).Row
        TargetColumn = Range("K1:Q1").Find(DayValue, , xlValues, xlWhole).Column
        
        Cells(TargetRow, TargetColumn) = Number

    End If
Next Cell


End Sub

**Mark123456789** · 02-25-2013, 06:17 PM

Martin

Thank you for taking the time to help, if i may ask one last request and that is this; the code you supplied works, however the problem is that there are several shifts that occur on the same day but in different weeks, for example shift 8 occurs on Sat in weeks 45,57 and 60.

Is there any way of concatenating the cells so that if a shift occurs on the same day but on multiple weeks then each week is concatenating with the contents of the existing cell.
Hope that makes sense.

Using shift 8 as an example i have shown what it should look like.

SAT
SHIFT 1
SHIFT 2
SHIFT 3
SHIFT 4
SHIFT 5
SHIFT 6
SHIFT 7
SHIFT 8 45 57 60

Once again thank you for your help.

**Mark123456789** · 02-25-2013, 06:19 PM

Martin

Thank you for taking the time to help, if i may ask one last request and that is this; the code you supplied works, however the problem is that there are several shifts that occur on the same day but in different weeks, for example shift 8 occurs on Sat in weeks 45,57 and 60.

Is there any way of concatenating the cells so that if a shift occurs on the same day but on multiple weeks then each week is concatenating with the contents of the existing cell.
Hope that makes sense.

Using shift 8 as an example i have shown what it should look like.

SAT
SHIFT 1
SHIFT 2
SHIFT 3
SHIFT 4
SHIFT 5
SHIFT 6
SHIFT 7
SHIFT 8 45 57 60

Once again thank you for your help.

**mike7952** · 02-25-2013, 07:44 PM

Heres another way

Option Explicit

Sub Shifts()
 Const shName As String = "Data"
 Const cShifts As String = "J2:J71"
 Const cWeeks As String = "A2:H26"
 Const cOutPutCol As String = "K"
 Dim ws As Worksheet
 Dim aArr, i As Long, ii As Long
 Dim e, w
 

 
 Set ws = Worksheets(shName)
 With ws
    aArr = .Range(cShifts)
 End With
 
 ReDim w(6)
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(aArr)
        .Item(aArr(i, 1)) = w
    Next
    With ws
        aArr = .Range(cWeeks)
    End With
    For i = 1 To UBound(aArr)
        For ii = 2 To UBound(aArr, 2)
            If .exists(aArr(i, ii)) Then
                w = .Item(aArr(i, ii))
                If IsEmpty(w(ii - 2)) Then
                    w(ii - 2) = aArr(i, 1)
                Else
                    w(ii - 2) = Join(Array(w(ii - 2), aArr(i, 1)), ",")
                End If
                .Item(aArr(i, ii)) = w
            End If
        Next
    Next
    ws.Cells(2, cOutPutCol).Resize(.Count, 7).Value = _
        Application.Transpose(Application.Transpose(.items))
 End With
 Set ws = Nothing
End Sub

Help Looping Through Excel Columns & Rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View