CODEWITHSUNDEEP
excelexcel-2007vba
Sathish Kothandam
Sathish Kothandam

Reputation: 1520

vba loop throws error

I have a number of sheets in one excel ..

What I am going to do is consolidate as per the column title into master sheet.

If the column not present in any of the sheets means , filled as “Not available “ in master sheet for those data’s

Below code written by me.. as per my understanding …the code which I pasted here is correct .. but there was a prob in loop .. I don’t where it is ..

Can anyone help me out from this prob …

Sub Sathish()
    'Declaration

    Dim ii
    Dim j As Integer
    Dim a As Integer
    Dim i As Integer
    Dim rrange As Range
    Dim trange As Range

    'Assigning

    a = Worksheets.Count
    ii = Array("saaa", "Description", "saaa", "Model", "sathish")

   Dim aa As Integer

    'Practical
    'Call Create_new_sheet
    For j = 1 To a
        Sheets(j).Select
        If Not ActiveSheet.Name = "Master" Then
            For i = 1 To 3
                Rows("1:1").Select
                For Each trange In Selection
                    If trange.Value = ii(i) Then
                        Selection.Find(What:=ii(i), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
                        ActiveCell.Offset(1, 0).Select
                        Range(Selection, Selection.End(xlDown)).Copy Destination:=Sheets("Master").Cells(6550, i).End(xlUp).Offset(1, 0)
                    Else
                        Sheets("Master").Cells(6550, i).End(xlUp).Offset(1, -1).Select
                        Selection.End(xlDown).Offset(0, 1).Select
                        ActiveCell.Value = "[not applicable]"
                        ActiveCell.Offset(-1, 0).Select
                        Range(ActiveCell, Selection.End(xlUp)).Offset(1, 0).Select
                        For Each rrange In Selection
                            rrange.Value = "[not applicable]"
                        Next rrange
                    End If
                Next trange
            Next i
        End If
    Next j
End Sub

Upvotes: 1

Views: 231

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

I was planning to post this as a comment but since there is lot of text, it wouldn't allow me.

Sathish, your understanding of the above problem is almost correct but you missed on few parts.

If trange.Value = ii(i) Then This part will only check first three values of the array. If you notice your loop runs only 3 times (For i = 1 To 3)

Also that is not how .Find is used :) I would recommend going through this link and re-apply the logic.

TOPIC: .Find and .FindNext In Excel VBA (See Section 1)

LINK: http://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/

When I write code, I write the logic down and then code accordingly. I would shamelessly recommend this link as well

TOPIC: To ‘Err’ is Human (See Section 1 for what I meant by above. See other sections as well to improve your code)

LINK: http://siddharthrout.wordpress.com/2011/08/01/to-err-is-human/

Here is an example on how the logic would look like.

LOGIC

  • Loop Through All the worksheets
  • Check if the Sheet is not Master
  • Set the search range as 1st row
  • Use .Find to check if the values in the array is present in row 1. Use a separate loop here.
  • If found (then do this)
  • If not found (then do this)

If you are still stuck then post the code that you tried and we will take it from there.

HTH

Sid

Upvotes: 1

Related Questions