What is wrong with my code here? (Missing IF or End Sub statement?)

**Brookshire** · 07-23-2013, 10:48 AM

Sub Efficiencies_Button1_Click()
Dim wsh As Worksheet
Dim work As Worksheet

'count
Dim r As Long

'work center list variables
Dim m As Long
Dim n As Long
Dim p As Long
Dim q As Long
Dim s As Long

Set wsh = Worksheets("Wk 1")

Application.ScreenUpdating = False

Dim count As Long

count = 0

For Each work In ThisWorkbook.Worksheets

count = count + 1

If count > 1 Then

m = wsh.Range("B" & wsh.Rows.count).End(xlUp).Row
n = wsh.Range("H" & wsh.Rows.count).End(xlUp).Row
p = wsh.Range("N" & wsh.Rows.count).End(xlUp).Row
q = wsh.Range("T" & wsh.Rows.count).End(xlUp).Row
s = wsh.Range("Z" & wsh.Rows.count).End(xlUp).Row

'for the first five workcenters
For r = 4 To m
If wsh.Range("B" & r).Value = work.Range("B").Value Then
work.Range("C").Value = wsh.Range("C" & r).Value
If wsh.Range("H" & r).Value = work.Range("B").Value Then
work.Range("F").Value = wsh.Range("I" & r).Value
If wsh.Range("I" & r).Value = work.Range("B").Value Then
work.Range("I").Value = wsh.Range("O" & r).Value
If wsh.Range("N" & r).Value = work.Range("B").Value Then
If wsh.Range("Z" & r).Value = work.Range("B").Value Then
work.Range("O").Value = wsh.Range("AA" & r).Value
work.Range("Q3").Value = wsh.Range("AB" & r).Value
work.Range("Q6").Value = wsh.Range("AB" & r).Value
work.Range("Q9").Value = wsh.Range("AB" & r).Value
work.Range("Q12").Value = wsh.Range("AB" & r).Value
work.Range("Q15").Value = wsh.Range("AB" & r).Value
work.Range("Q18").Value = wsh.Range("AB" & r).Value
work.Range("Q21").Value = wsh.Range("AB" & r).Value
work.Range("Q24").Value = wsh.Range("AB" & r).Value
work.Range("Q27").Value = wsh.Range("AB" & r).Value
work.Range("Q30").Value = wsh.Range("AB" & r).Value
work.Range("Q33").Value = wsh.Range("AB" & r).Value
work.Range("Q36").Value = wsh.Range("AB" & r).Value
work.Range("Q39").Value = wsh.Range("AB" & r).Value
work.Range("Q42").Value = wsh.Range("AB" & r).Value
work.Range("Q45").Value = wsh.Range("AB" & r).Value
work.Range("Q48").Value = wsh.Range("AB" & r).Value
work.Range("Q51").Value = wsh.Range("AB" & r).Value
work.Range("Q54").Value = wsh.Range("AB" & r).Value
work.Range("Q57").Value = wsh.Range("AB" & r).Value
work.Range("Q60").Value = wsh.Range("AB" & r).Value
work.Range("Q63").Value = wsh.Range("AB" & r).Value
work.Range("Q66").Value = wsh.Range("AB" & r).Value
work.Range("Q69").Value = wsh.Range("AB" & r).Value
work.Range("Q72").Value = wsh.Range("AB" & r).Value
work.Range("Q75").Value = wsh.Range("AB" & r).Value
work.Range("Q78").Value = wsh.Range("AB" & r).Value
work.Range("Q81").Value = wsh.Range("AB" & r).Value
work.Range("Q84").Value = wsh.Range("AB" & r).Value
work.Range("Q87").Value = wsh.Range("AB" & r).Value
work.Range("Q90").Value = wsh.Range("AB" & r).Value
work.Range("Q93").Value = wsh.Range("AB" & r).Value
work.Range("Q96").Value = wsh.Range("AB" & r).Value
work.Range("Q99").Value = wsh.Range("AB" & r).Value
work.Range("Q102").Value = wsh.Range("AB" & r).Value
work.Range("Q105").Value = wsh.Range("AB" & r).Value
work.Range("Q108").Value = wsh.Range("AB" & r).Value
work.Range("Q111").Value = wsh.Range("AB" & r).Value
work.Range("Q114").Value = wsh.Range("AB" & r).Value
work.Range("Q117").Value = wsh.Range("AB" & r).Value
work.Range("Q120").Value = wsh.Range("AB" & r).Value
work.Range("Q123").Value = wsh.Range("AB" & r).Value
work.Range("Q126").Value = wsh.Range("AB" & r).Value
work.Range("Q129").Value = wsh.Range("AB" & r).Value
work.Range("Q132").Value = wsh.Range("AB" & r).Value
work.Range("Q135").Value = wsh.Range("AB" & r).Value
work.Range("Q138").Value = wsh.Range("AB" & r).Value
work.Range("Q141").Value = wsh.Range("AB" & r).Value
work.Range("Q144").Value = wsh.Range("AB" & r).Value
work.Range("Q147").Value = wsh.Range("AB" & r).Value
work.Range("Q150").Value = wsh.Range("AB" & r).Value
work.Range("Q153").Value = wsh.Range("AB" & r).Value
work.Range("Q156").Value = wsh.Range("AB" & r).Value
work.Range("Q159").Value = wsh.Range("AB" & r).Value
work.Range("Q162").Value = wsh.Range("AB" & r).Value
work.Range("Q165").Value = wsh.Range("AB" & r).Value
work.Range("Q168").Value = wsh.Range("AB" & r).Value
work.Range("Q171").Value = wsh.Range("AB" & r).Value
work.Range("Q174").Value = wsh.Range("AB" & r).Value
work.Range("Q177").Value = wsh.Range("AB" & r).Value
work.Range("Q180").Value = wsh.Range("AB" & r).Value
work.Range("Q183").Value = wsh.Range("AB" & r).Value
work.Range("Q4").Value = wsh.Range("AC" & r).Value
work.Range("Q7").Value = wsh.Range("AC" & r).Value
work.Range("Q10").Value = wsh.Range("AC" & r).Value
work.Range("Q13").Value = wsh.Range("AC" & r).Value
work.Range("Q16").Value = wsh.Range("AC" & r).Value
work.Range("Q19").Value = wsh.Range("AC" & r).Value
work.Range("Q22").Value = wsh.Range("AC" & r).Value
work.Range("Q25").Value = wsh.Range("AC" & r).Value
work.Range("Q28").Value = wsh.Range("AC" & r).Value
work.Range("Q31").Value = wsh.Range("AC" & r).Value
work.Range("Q34").Value = wsh.Range("AC" & r).Value
work.Range("Q37").Value = wsh.Range("AC" & r).Value
work.Range("Q40").Value = wsh.Range("AC" & r).Value
work.Range("Q43").Value = wsh.Range("AC" & r).Value
work.Range("Q46").Value = wsh.Range("AC" & r).Value
work.Range("Q49").Value = wsh.Range("AC" & r).Value
work.Range("Q52").Value = wsh.Range("AC" & r).Value
work.Range("Q55").Value = wsh.Range("AC" & r).Value
work.Range("Q58").Value = wsh.Range("AC" & r).Value
work.Range("Q61").Value = wsh.Range("AC" & r).Value
work.Range("Q64").Value = wsh.Range("AC" & r).Value
work.Range("Q67").Value = wsh.Range("AC" & r).Value
work.Range("Q70").Value = wsh.Range("AC" & r).Value
work.Range("Q73").Value = wsh.Range("AC" & r).Value
work.Range("Q76").Value = wsh.Range("AC" & r).Value
work.Range("Q79").Value = wsh.Range("AC" & r).Value
work.Range("Q82").Value = wsh.Range("AC" & r).Value
work.Range("Q85").Value = wsh.Range("AC" & r).Value
work.Range("Q88").Value = wsh.Range("AC" & r).Value
work.Range("Q91").Value = wsh.Range("AC" & r).Value
work.Range("Q94").Value = wsh.Range("AC" & r).Value
work.Range("Q97").Value = wsh.Range("AC" & r).Value
work.Range("Q100").Value = wsh.Range("AC" & r).Value
work.Range("Q103").Value = wsh.Range("AC" & r).Value
work.Range("Q106").Value = wsh.Range("AC" & r).Value
work.Range("Q109").Value = wsh.Range("AC" & r).Value
work.Range("Q112").Value = wsh.Range("AC" & r).Value
work.Range("Q115").Value = wsh.Range("AC" & r).Value
work.Range("Q118").Value = wsh.Range("AC" & r).Value
work.Range("Q121").Value = wsh.Range("AC" & r).Value
work.Range("Q124").Value = wsh.Range("AC" & r).Value
work.Range("Q127").Value = wsh.Range("AC" & r).Value
work.Range("Q130").Value = wsh.Range("AC" & r).Value
work.Range("Q133").Value = wsh.Range("AC" & r).Value
work.Range("Q136").Value = wsh.Range("AC" & r).Value
work.Range("Q139").Value = wsh.Range("AC" & r).Value
work.Range("Q142").Value = wsh.Range("AC" & r).Value
work.Range("Q145").Value = wsh.Range("AC" & r).Value
work.Range("Q148").Value = wsh.Range("AC" & r).Value
work.Range("Q151").Value = wsh.Range("AC" & r).Value
work.Range("Q154").Value = wsh.Range("AC" & r).Value
work.Range("Q157").Value = wsh.Range("AC" & r).Value
work.Range("Q160").Value = wsh.Range("AC" & r).Value
work.Range("Q163").Value = wsh.Range("AC" & r).Value
work.Range("Q166").Value = wsh.Range("AC" & r).Value
work.Range("Q169").Value = wsh.Range("AC" & r).Value
work.Range("Q172").Value = wsh.Range("AC" & r).Value
work.Range("Q175").Value = wsh.Range("AC" & r).Value
work.Range("Q178").Value = wsh.Range("AC" & r).Value
work.Range("Q181").Value = wsh.Range("AC" & r).Value
work.Range("Q184").Value = wsh.Range("AC" & r).Value
End If

'start next workbook
End If

Next work

Application.ScreenUpdating = True

UserForm1.Hide

End Sub

There is something wrong with the bolded red section, where it is constantly giving me an error message of "Next Without For". If I delete the Next work, then the error message is "Block If without End If"

Thanks for the help

**AndyLitch** · 07-23-2013, 10:51 AM

You've not closed your for/next loop

For r = 4 to m ............................. needs a next r before the first red end if

**The_Prist** · 07-23-2013, 10:52 AM

You don't close If statement:

If wsh.Range("B" & r).Value = work.Range("B").Value Then
 work.Range("C").Value = wsh.Range("C" & r).Value
 If wsh.Range("H" & r).Value = work.Range("B").Value Then
 work.Range("F").Value = wsh.Range("I" & r).Value
 If wsh.Range("I" & r).Value = work.Range("B").Value Then
 work.Range("I").Value = wsh.Range("O" & r).Value

and also For r

use the "code" tag for code listings

**Solus Rankin** · 07-23-2013, 10:53 AM

You're missing End if after all your if statements here:

If wsh.Range("B" & r).Value = work.Range("B").Value Then
work.Range("C").Value = wsh.Range("C" & r).Value
If wsh.Range("H" & r).Value = work.Range("B").Value Then
work.Range("F").Value = wsh.Range("I" & r).Value
If wsh.Range("I" & r).Value = work.Range("B").Value Then
work.Range("I").Value = wsh.Range("O" & r).Value
If wsh.Range("N" & r).Value = work.Range("B").Value Then
If wsh.Range("Z" & r).Value = work.Range("B").Value Then
work.Range("O").Value = wsh.Range("AA" & r).Value

On a side note you really should indent your code. This becomes a lot easier to catch.

**LaffyAffy13** · 07-23-2013, 10:53 AM

For each "For" statement that you have, you must place a "Next" wherever you want the program to loop. For each "If" statement, the program requires an "End If" statement.

**Brookshire** · 07-23-2013, 11:06 AM

work.Range("Q121").Value = wsh.Range("AC" & r).Value
work.Range("Q124").Value = wsh.Range("AC" & r).Value
work.Range("Q127").Value = wsh.Range("AC" & r).Value
work.Range("Q130").Value = wsh.Range("AC" & r).Value
work.Range("Q133").Value = wsh.Range("AC" & r).Value
work.Range("Q136").Value = wsh.Range("AC" & r).Value
work.Range("Q139").Value = wsh.Range("AC" & r).Value
work.Range("Q142").Value = wsh.Range("AC" & r).Value
work.Range("Q145").Value = wsh.Range("AC" & r).Value
work.Range("Q148").Value = wsh.Range("AC" & r).Value
work.Range("Q151").Value = wsh.Range("AC" & r).Value
work.Range("Q154").Value = wsh.Range("AC" & r).Value
work.Range("Q157").Value = wsh.Range("AC" & r).Value
work.Range("Q160").Value = wsh.Range("AC" & r).Value
work.Range("Q163").Value = wsh.Range("AC" & r).Value
work.Range("Q166").Value = wsh.Range("AC" & r).Value
work.Range("Q169").Value = wsh.Range("AC" & r).Value
work.Range("Q172").Value = wsh.Range("AC" & r).Value
work.Range("Q175").Value = wsh.Range("AC" & r).Value
work.Range("Q178").Value = wsh.Range("AC" & r).Value
work.Range("Q181").Value = wsh.Range("AC" & r).Value
work.Range("Q184").Value = wsh.Range("AC" & r).Value
'Next r
End If
End If
End If
End If
End If

'start next workbook

work

End Sub

Okay, I've added an End If for every If statement but now I'm getting an error saying "For without Next" highlighting End Sub

**LaffyAffy13** · 07-23-2013, 11:11 AM

You need to remove the apostrophe before your "Next r"

**Solus Rankin** · 07-23-2013, 11:11 AM

brookshire

You have nested your If statements by placing the end ifs there. The way you have it the 2nd if only happens if the first if is true and so on...
If this then
do this
end if

is probably a better format for what you are trying to achieve. Also you have for r = to start a loop. When your code is ready to start another loop you need a next r statement.

**Brookshire** · 07-23-2013, 12:58 PM

Okay, I changed things up and now there are no issues as far as running the macro is involved. My next question is, I'm pulling numbers into these cells from another worksheet based on an ID number but when the ID number isn't found on the other worksheet, the cell will appear as "#N/A". Is there a code I can enter into the macro that will convert all cells that are "#N/A" into a blank cell?

This is what I'm referring to:

\1

The last column is the sum of all numbers from the first two columns, but if any cell in the first two columns are "#N/A", then it cannot spit out a number in the last column. So I would like to enter a code in VBA to basically converts any cell that is "#N/A" to a blank cell. How do I do that?

**Solus Rankin** · 07-23-2013, 01:06 PM

If the original question is no longer a problem you should mark the thread as [SOLVED] and start a new thread with an appropriate title.

Is the N/A a product of a vlookup?

**Brookshire** · 07-23-2013, 01:32 PM

Yes it is .

What is wrong with my code here? (Missing IF or End Sub statement?)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Re: What is wrong with my code here? (Missing IF or End Sub statement?)

Thread Information

Users Browsing this Thread

Similar Threads

Code for email alerts from excel isn't working, wrong code possibly?

=SUM(C18:C20) Calculating wrong missing out cell

If Statement in Function code, what is missing?

cant figure out what I'm doing wrong or missing...

What is wrong with this IF statement? need help.

Bookmarks

Bookmarks

Posting Permissions