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( xlCellType LastCell). 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
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
If Not tmatch Is Nothing Then
cs_last_row = cs.UsedRange.SpecialCells(
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
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
Move this:
Outside of this, which you can probably remove as it's doing nothing.
If t.Value = "" Then Exit For
Outside of this, which you can probably remove as it's doing nothing.
If t.Value = "Ticket Number" Then
End If
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'
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
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".
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".
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.
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 Breakpointthen press F5 to continue.
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.