?
Solved

Sub error

Posted on 2011-10-26
5
Medium Priority
?
161 Views
Last Modified: 2012-05-12
Hi,

With the attached code i am receiving an error message "Next without For"

It points at the last line Next i

But already have the control "For i = 2 To rowNum"

Im not sure why there is this error, any suggestions would be great!

Thanks
Seamus
Function convertCurrency()

'variables
Dim rowNum As Integer

'insert the USD value column
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J1").Value = "USD Value"

'work out the number of rows
rowNum = ActiveSheet.UsedRange.Rows.Count

'for each 'total' cell, calculate the dollar value
For i = 2 To rowNum
        
    If Range("C" & i).Value = "EU Total" Then
             If Sheets("Live Bloomberg Data").Range("B2").Value <> 0 And Sheets("Live Bloomberg Data").Range("B2").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B2").Value
   End If
 
    ElseIf Range("C" & i).Value = "£ Total" Then
        If Sheets("Live Bloomberg Data").Range("B3").Value <> 0 And Sheets("Live Bloomberg Data").Range("B3").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B3").Value
   End If
 

    ElseIf Range("C" & i).Value = "SF Total" Then
      If Sheets("Live Bloomberg Data").Range("B4").Value <> 0 And Sheets("Live Bloomberg Data").Range("B4").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B4").Value
   End If
 

    ElseIf Range("C" & i).Value = "SK Total" Then
        If Sheets("Live Bloomberg Data").Range("B5").Value <> 0 And Sheets("Live Bloomberg Data").Range("B5").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B5").Value
   End If
 

    ElseIf Range("C" & i).Value = "DK Total" Then
            If Sheets("Live Bloomberg Data").Range("B6").Value <> 0 And Sheets("Live Bloomberg Data").Range("B6").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B6").Value
   End If
 
    ElseIf Range("C" & i).Value = "NK Total" Then
             If Sheets("Live Bloomberg Data").Range("B7").Value <> 0 And Sheets("Live Bloomberg Data").Range("B7").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B7").Value
   End If
 
    ElseIf Range("C" & i).Value = "CK Total" Then
             If Sheets("Live Bloomberg Data").Range("B8").Value <> 0 And Sheets("Live Bloomberg Data").Range("B8").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B8").Value
   End If
 
    ElseIf Range("C" & i).Value = "SKK Total" Then
              If Sheets("Live Bloomberg Data").Range("B9").Value <> 0 And Sheets("Live Bloomberg Data").Range("B9").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B9").Value
   End If
 
    ElseIf Range("C" & i).Value = "PZ Total" Then
              If Sheets("Live Bloomberg Data").Range("B10").Value <> 0 And Sheets("Live Bloomberg Data").Range("B10").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B10").Value
   End If
 
    ElseIf Range("C" & i).Value = "HF Total" Then
              If Sheets("Live Bloomberg Data").Range("B11").Value <> 0 And Sheets("Live Bloomberg Data").Range("B11").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B11").Value
   End If
 
    ElseIf Range("C" & i).Value = "RB Total" Then
              If Sheets("Live Bloomberg Data").Range("B12").Value <> 0 And Sheets("Live Bloomberg Data").Range("B12").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B12").Value
   End If
 
    ElseIf Range("C" & i).Value = "TY Total" Then
              If Sheets("Live Bloomberg Data").Range("B13").Value <> 0 And Sheets("Live Bloomberg Data").Range("B13").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B13").Value
   End If
 
    ElseIf Range("C" & i).Value = "I# Total" Then
              If Sheets("Live Bloomberg Data").Range("B14").Value <> 0 And Sheets("Live Bloomberg Data").Range("B14").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B14").Value
   End If
 
    ElseIf Range("C" & i).Value = "KS Total" Then
              If Sheets("Live Bloomberg Data").Range("B15").Value <> 0 And Sheets("Live Bloomberg Data").Range("B15").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B15").Value
   End If
 
    ElseIf Range("C" & i).Value = "RC Total" Then
              If Sheets("Live Bloomberg Data").Range("B16").Value <> 0 And Sheets("Live Bloomberg Data").Range("B16").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B16").Value
   End If
 
    ElseIf Range("C" & i).Value = "MD Total" Then
              If Sheets("Live Bloomberg Data").Range("B17").Value <> 0 And Sheets("Live Bloomberg Data").Range("B17").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B17").Value
   End If
 
    ElseIf Range("C" & i).Value = "$Z Total" Then
              If Sheets("Live Bloomberg Data").Range("B18").Value <> 0 And Sheets("Live Bloomberg Data").Range("B18").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B18").Value
   End If
   

 
    ElseIf Range("C" & i).Value = "$ Total" Then
        Range("J" & i).Value = Range("I" & i).Value
    ElseIf Range("C" & i).Value = "$C Total" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("H2").Value
  If Sheets("Live Bloomberg Data").Range("H2").Value <> 0 And Sheets("Live Bloomberg Data").Range("H2").Value <> "" Then
    ElseIf Range("C" & i).Value = "Y Total" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N2").Value
          If Sheets("Live Bloomberg Data").Range("N2").Value <> 0 And Sheets("Live Bloomberg Data").Range("N2").Value <> "" Then
    ElseIf Range("C" & i).Value = "$H Total" Then
      If Sheets("Live Bloomberg Data").Range("N3").Value <> 0 And Sheets("Live Bloomberg Data").Range("N3").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N3").Value
    ElseIf Range("C" & i).Value = "$A Total" Then
      If Sheets("Live Bloomberg Data").Range("N4").Value <> 0 And Sheets("Live Bloomberg Data").Range("N4").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("N4").Value
    ElseIf Range("C" & i).Value = "$N Total" Then
      If Sheets("Live Bloomberg Data").Range("N5").Value <> 0 And Sheets("Live Bloomberg Data").Range("N5").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("N5").Value
    ElseIf Range("C" & i).Value = "$S Total" Then
      If Sheets("Live Bloomberg Data").Range("N6").Value <> 0 And Sheets("Live Bloomberg Data").Range("N6").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N6").Value
    ElseIf Range("C" & i).Value = "$T Total" Then
      If Sheets("Live Bloomberg Data").Range("N7").Value <> 0 And Sheets("Live Bloomberg Data").Range("N7").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N7").Value
    ElseIf Range("C" & i).Value = "KW Total" Then
      If Sheets("Live Bloomberg Data").Range("N8").Value <> 0 And Sheets("Live Bloomberg Data").Range("N8").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N8").Value
    ElseIf Range("C" & i).Value = "PP Total" Then
      If Sheets("Live Bloomberg Data").Range("B9").Value <> 0 And Sheets("Live Bloomberg Data").Range("B9").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N9").Value
    ElseIf Range("C" & i).Value = "IR Total" Then
      If Sheets("Live Bloomberg Data").Range("B10").Value <> 0 And Sheets("Live Bloomberg Data").Range("B10").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N10").Value
    ElseIf Range("C" & i).Value = "RU Total" Then
      If Sheets("Live Bloomberg Data").Range("B11").Value <> 0 And Sheets("Live Bloomberg Data").Range("B11").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N11").Value
    ElseIf Range("C" & i).Value = "$M Total" Then
      If Sheets("Live Bloomberg Data").Range("B13").Value <> 0 And Sheets("Live Bloomberg Data").Range("B13").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N13").Value
    ElseIf Range("C" & i).Value = "TB Total" Then
      If Sheets("Live Bloomberg Data").Range("B14").Value <> 0 And Sheets("Live Bloomberg Data").Range("B14").Value <> "" Then
        Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N14").Value
    'if no currency is found, highlight the record
    Else
        If Right(Range("C" & i).Value, 5) = "Total" And Left(Range("C" & i).Value, 5) <> "Grand" Then
            Range("J" & i).Interior.ColorIndex = 6
            Range("C" & i).Interior.ColorIndex = 6
        End If
    End If

Next i

Open in new window

0
Comment
Question by:Seamus2626
5 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37031254
The problem is with your If then elseif statements wwithin the loop they are not correct,

for example the ELSEIF on line 22 as shown above is not within an IF statement as you have an END IF on line 20, there are further instances of the same problem later on, sort out your IF ...Then statements and the problem will go away.
0
 

Author Comment

by:Seamus2626
ID: 37031274
Thanks jacko72, can you provide an example of how to fix the statements?

Seamus
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 37031283
you are missing a endif for the elseif ...

anyhow, you might be interested to modify the code to make it more readable ...
dim _Live_Bloomberg_Data_b2 as string
_Live_Bloomberg_Data_b2 = Sheets("Live Bloomberg Data").Range("B2").Value
dim _Live_Bloomberg_Data_b3 as string
_Live_Bloomberg_Data_b3 = Sheets("Live Bloomberg Data").Range("B3").Value

For i = 2 To rowNum

    Dim _c_value as string
    dim _i_value as string
 
    _c_value = Range("C" & i).Value
    _i_value = Range("I" & i).Value

    select case _c_value
      case  "EU Total"  
         If _Live_Bloomberg_Data_b2 <> 0 And _Live_Bloomberg_Data_b2 <> "" Then
           Range("J" & i).Value = _i_value / _Live_Bloomberg_Data_b2
         End If

      case  "£ Total" 
         If _Live_Bloomberg_Data_b3 <> 0 And _Live_Bloomberg_Data_b3 <> "" Then
           Range("J" & i).Value = _i_value / _Live_Bloomberg_Data_b3
         End If
   ... etc

Open in new window

0
 
LVL 14

Expert Comment

by:sentner
ID: 37031294
This is why proper indentation is helpful.  You should indent each time you begin a loop or conditional block, and only remove the indentation upon exiting that block.  This helps you identify where you're missing an exiting statement like "End If", or where you may have an unintentional one.
0
 

Author Closing Comment

by:Seamus2626
ID: 37031434
Thank you all, sorted now

Seamus
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question