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

Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
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
Seamus2626Author Commented:
Thanks jacko72, can you provide an example of how to fix the statements?

Seamus
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sentnerCommented:
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
Seamus2626Author Commented:
Thank you all, sorted now

Seamus
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.