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
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
rhadash

8/22/2022 - Mon
Martin Liss

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.
Martin Liss

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

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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'
Norie

Did you try my suggestion?
For Each t In fs.Columns(9).Cells
    If t.Value = "" Then Exit For

Open in new window

Martin Liss

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".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Martin Liss

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
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck