Next without For error?

Question

I have lots of data (numbers) with heading in several worksheets that I am trying to zero. This is done on each column as follows: Taking the value of the first row in the column and subtracting this value from all rows in the column.

I have put together this code (may not be the best way, but Im new to VBA so :))

Dim ws As Worksheet
Dim Header As Range, ColData As Range
Dim firstrow As Long
Dim cell As Range, cell2 As Range

Set ws = ActiveSheet
Set Header = ws.Range("B5").End(xlToRight)

For Each cell In Header
    If IsEmpty(cell) Then
    Else
        firstrow = cell.Offset(2).Value
        If Not IsEmpty(cell.Offset(2)) Then
            Set ColData = ws.Range(cell.Offset(2), cell.Offset(2).End(xlDown))
            For Each cell2 In ColData
                cell2.Value = cell2.Value - firstrow
            Next cell2
 Next cell

I am getting the error of Next without For? Am I missing something? forgive me if its something stupid I have checked several times and cant seem to spot it!

Thanks!

EDIT: Fixed IsEmpty function and it ran.. not giving the correct output yet but I'll work on it!

Fixed it and still getting error as I have said in the comment below! — Moustafa El-Sawy
– Moustafa El-Sawy, Commented May 2, 2016 at 4:22

teylyn · Accepted Answer · 2016-05-02 03:16:51Z

1

As mentioned in a comment to your question, you are missing two End If statements. Here is a simple way how you can avoid this kind of problem when typing out your code:

Every time you start an IF _condition_ Then statement, immediately hit Enter twice and type the End If line. Then go up one line and type what goes inside the IF statement.

The same technique can be applied to all languages and statements that have an opening and a closing marker (For/Next, etc.), be it a keyword, a pair of brackets {} [] (), or comment marks /* [...] */.

answered May 2, 2016 at 3:16

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Moustafa El-Sawy Over a year ago

Take a look at my edited code, still get an error.. Error says Run-time error 438, object doesnt support this property or method :(

D_Bester Over a year ago

@MofasaE Please start a new question instead of asking another question here.

Collectives™ on Stack Overflow

Next without For error?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related