Excel 2007 : Subscript out of range

[ususgoreng]

help me, i can't fix this problem,
i want to arrange some number (small array) into one big array
there's column and row number located above and beside the small array [km], that is location of number from small array at big array.
i tried with smaller one (asdwx.xlsm) 10x10, it works, but when i try (help.xlsm), 204x204 it doesn't work.
it said error '9' subscript out of range.
idk why.
thanks

[ususgoreng]

help me

[tigeravatar]

ususgoreng,

Alright, first of all, your variable names should be more than just a or b. They should accurately describe the information being assigned to them. It would also be a good idea to dim your variables to the appropriate type so that they don't take as much memory and are easier to reference. I updated your code, and it now runs successfully and faster. (Although I didn't optimize it)

Sub hopla()
    Dim sht As Worksheet
    Dim colkm As Integer, colkg As Integer, rowkm As Integer, rowkg As Integer
    Dim a As Range, b As Range, c As Range, km As Range, d As Range, kg As Range
    For Each sht In ActiveWorkbook.Sheets
        If sht.Name = "70" Or sht.Name = "71" Or sht.Name = "72" Then
            sht.[AG4:IB207].Value = 0
            For colkm = 19 To 24
                Set a = sht.Cells(18, colkm)
                For colkg = 33 To 236
                    Set b = sht.Cells(3, colkg)
                    If a.Value = b.Value Then
                        For rowkm = 19 To 24
                            Set c = sht.Cells(rowkm, 26)
                            Set km = sht.Cells(rowkm, colkm)
                            For rowkg = 4 To 207
                                Set d = sht.Cells(rowkg, 238)
                                Set kg = sht.Cells(rowkg, colkg)
                                If c.Value = d.Value Then kg.Value = km.Value
                            Next
                        Next
                    End If
                Next
            Next
        End If
    Next sht
End Sub

Hope that helps,
~tigeravatar

[ususgoreng]

wow, that's work.
thank you very much man.



admin : sorry i double post this thread..