Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Sub error

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

Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of Seamus2626
Seamus2626
Flag of Ireland image

ASKER

Thanks jacko72, can you provide an example of how to fix the statements?

Seamus
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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 sentner
sentner
Flag of United States of America image
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.
Avatar of Seamus2626
Seamus2626
Flag of Ireland image

ASKER

Thank you all, sorted now

Seamus