Link to home
Start Free TrialLog in
Avatar of rhadash
rhadash

asked on

If Statement not working Excel VBA 2010

Hi,
I have code tha will copy all excel workbook sheets in a folder into a single workbook.  Then it looks at the Affs tab for the ticket number.  It will find match the ticket number from the Affs tab to the ticket number in any tab with name "aviswkly" .  Every ticket in the Affs tab will have a match.  It then copies the "aviswkly" data followed by Affs tab data into the combinedsheet tab.   It does the copy and paste fine but then gets hung up.  The following code has issue that I can't figure out.    It just hangs like it might be looping through all rows. I've attached the code and workbooks. Appreciate any advice on this.


Dim t As Range, cs As Worksheet, zs As Worksheet, ss As Worksheet, tmatch As Range, cs_last_row As Long


Set fs = Worksheets("affs")
Set cs = Worksheets("Combinedsheet")

For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then
   If t.Value = "" Then Exit For
End If

    For Each zs In Wb1.Sheets ' was: ThisWorkbook.Sheets
        If Left(zs.Name, 9) = "aviswkly_" Then
'            If Left(zs.Name, 9) = "aviswkly_" Then this is original line changing tab name
            Set tmatch = zs.Columns(9).Find(What:=t.Value)
            If Not tmatch Is Nothing Then
                cs_last_row = cs.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
                cs.Range("A" & cs_last_row & ":Q" & cs_last_row).Value = zs.Range("A" & tmatch.Row & ":Q" & tmatch.Row).Value
                cs.Range("R" & cs_last_row & ":AQ" & cs_last_row).Value = fs.Range("A" & t.Row & ":Z" & t.Row).Value
       
           End If
        End If
    Next zs
Next t
AffReport.xls
Afftest-code.txt
ABG-103455-MiamiDade-012913.xls
ABG-103455-MiamiDade-021213.xls
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

This part doesn't do anything useful.


    If t.Value = "Ticket Number" Then
        If t.Value = "" Then Exit For
    End If

Open in new window


If t is "Ticket Number" then it can't be blank so there's no need to test for t.Value = "" and in any case no matter if it's "Ticket Number" or "blah" it falls through to For Each zs loop.
So I'm guessing that what you want is


Set fs = Worksheets("affs")
Set cs = Worksheets("Combinedsheet")

For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then

        For Each zs In Wb1.Sheets ' was: ThisWorkbook.Sheets
            If Left(zs.Name, 9) = "aviswkly_" Then
    '            If Left(zs.Name, 9) = "aviswkly_" Then this is original line changing tab name
                Set tmatch = zs.Columns(9).Find(What:=t.Value)
                If Not tmatch Is Nothing Then
                    cs_last_row = cs.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
                    cs.Range("A" & cs_last_row & ":Q" & cs_last_row).Value = zs.Range("A" & tmatch.Row & ":Q" & tmatch.Row).Value
                    cs.Range("R" & cs_last_row & ":AQ" & cs_last_row).Value = fs.Range("A" & t.Row & ":Z" & t.Row).Value
            
               End If
            End If
        Next zs
    End If
Next t

Open in new window

Avatar of Norie
Norie

Move this:
If t.Value = "" Then Exit For

Open in new window


Outside of this, which you can probably remove as it's doing nothing.
If t.Value = "Ticket Number" Then

End If

Open in new window

Avatar of rhadash

ASKER

When I change to:
For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then

The code runs complete but nothing is copied/pasted so the end result is empty file.


When I change to:
For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then
  End If
It just hangs the debug hightlight on  'If Not tmatch Is Nothing Then'
Did you try my suggestion?
For Each t In fs.Columns(9).Cells
    If t.Value = "" Then Exit For

Open in new window

Do you know how to use Debug? If not then see my article. In any case put a breakpoint on bolded line (by clicking it the left-hand margin)

For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then

and then highlighting t.Value to see if it is "Ticket Number".
Avatar of rhadash

ASKER

Changed as noted to:
For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then Exit For

This time the 1st End if after the copy & paste was hightlighted. Clicked in left margin but this didn't show me anything.
Here's an excerpt from my article:

Toggle Breakpoint
Availability: At design time and when in break mode
Shortcut: F9

A breakpoint is a place at which the program will pause until you manually let it resume.  To set a breakpoint you can either place the cursor on the line where you want the code to pause and then press F9, or, more simply, just click in the margin of the that line. When you do either of those things VB places a red bullet in the margin and also highlights the line in red. Here is an example where a breakpoint has been placed on a line.
 
Breakpoint

If you then run the program and the breakpoint is reached, VB will highlight the line's code in yellow and then wait there for you to do whatever you want. One of the things that you might want to do is to check the value of variables in the Sub or Function (these are called Local variables) and there are several ways to do that while the code in the Sub or Function is paused. You can hover the mouse over a variable and VB will pop up a little box that shows the variable name and its value...
then press F5 to continue.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rhadash

ASKER

The error dscription did not lead me to your solution but logical once you presented- would want to ignore header.  Thank you.  I will take some time to read some of your articles as well.