1

In worksheet wsb,I am trying to copy column B and Column having ParName in header and pasting it to columns B & H respectively of worksheet wso. The problem is It's running only for first Item and also for the first matched value of i for that item and not for all the matched item-i values.

Dim ws, wsa, wsb, wsc, wso As Worksheet
Dim index1b, LastRow, MOLastRow, wsoLastRow As Long
Dim ColLtr1b As Variant
Dim MoNameArr

Set wsb = Workbooks(Y).Sheets("REF")
wsb.Activate
LastRow = GetLastRow(wsb, 2)
Arr = Array("Abc", "Def")
Set wso = Workbooks(W).Sheets("Output")

For Each Item In Arr

    For i = 2 To LastRow

        If Cells(i, 2).Value = Item Then

            wsb.Activate
            ParName = wsb.Cells(i, 3).Value
            Set wsc = Workbooks(M).Sheets(Item)
            wsc.Activate
            index1b = Application.Match(ParName, wsc.Rows(1), 0)

            If Not IsError(index1b) Then

                ColLtr1b = Replace(wsc.Cells(1, index1b).Address(True, False), "$1", "")
                MOLastRow = wsc.Cells(Rows.Count, 2).End(xlUp).Row
                Range("B2:B" & GetLastRow(wsc, 2)).Copy

                wso.Activate
                wsoLastRow = GetLastRow(wso, 2)
                Range("B" & wsoLastRow + 1).Select
                ActiveSheet.Paste

                wsc.Activate
                Range(ColLtr1b & "2:" & ColLtr1b & GetLastRow(wsc, 2)).Copy

                wso.Activate
                Range("H" & wsoLastRow + 1).Select
                ActiveSheet.Paste

            End If

        End If

    Next i

Next Item
Community
  • 1
  • 1
HobbyCoder
  • 45
  • 9

1 Answers1

0

Declare your variables like this:

Dim ws As Worksheet, wsa As worksheet, wsb as Worksheet
Dim wsc as Worksheet, wso As Worksheet
Dim index1b as Long, LastRow as Long, MOLastRow as Long, wsoLastRow As Long

Then start debugging with pressing F8. It goes line by line and you may see where is the problem in the nested loop. It can be in one of these 3:

  • you need to write Trim(Cells(i, 2)) in the If Cells(i, 2).Value = Item Then condition;

  • you are not calculating LastRow correctly;

  • you have On Error Resume Next somewhere in your code and you are entering an error w/o noticing;

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • I have already tried all of these. The Item is getting matched for first element of array.It runs for the first matched i value of first item only. After that, although i gets incremented, The Cells values are not checked. LastRow is correct.On Error resume next is not present anywhere in the rest of the code(not mentioned above). – HobbyCoder Apr 18 '18 at 10:33
  • 1
    @Saboohi - I really do not wish to write it (because it is a bad practice), but write `wsb.Activate` before the `If Cells(i, 2).Value = Item Then` and it would probably work. – Vityata Apr 18 '18 at 10:36
  • @Saboohi - are you sure? However, what happens when you start going line by line with F8? – Vityata Apr 18 '18 at 10:42
  • It runs for the first matched i value of both the items in array and displays output but still doesn't show for all matched i values. – HobbyCoder Apr 18 '18 at 10:51
  • If I also add wsb.Activate before Next i, then it runs for all matched(i) values of first item but doesn't run for second item of array. – HobbyCoder Apr 18 '18 at 10:57
  • @Saboohi - I guess that you are not interested in debugging with F8. Still, write `Set wsc = Workbooks(M).Sheets(Item)` after `For Each Item In Arr`, restart your PC and give it a try again. – Vityata Apr 18 '18 at 10:59
  • I have been debugging since beginning.The code is working after adding wsb.Activate before Next i. Seems like while debugging, my pointer was at "Next Item" and that's why it was not showing for another item of array. Thanks for all your help. – HobbyCoder Apr 18 '18 at 11:07
  • ABove you mentioned that it's a bad practise. Can you please explain how? – HobbyCoder Apr 18 '18 at 11:08
  • 1
    @Saboohi - see this for the bad practise - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/35864330#35864330 – Vityata Apr 18 '18 at 11:19