Do without loop (but it has...)

F

finaljustice

Board Regular
Joined
Oct 6, 2010
Messages
175
Hi there, I've come up with this code, but it doesn't work after I added more "IFs".
This code works perfectly, this was me initially developing the idea:
Code: 
Sub Estrutura()
Application.ScreenUpdating = False
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2011
        If ActiveCell.Offset(0, 166).Value = 2011 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2011").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2011").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

Now when I try and enhance to do the same thing but now with more IFs the code says "DO without LOOP" Error.

This code does'nt work:
Code: 
Sub Estrutura()
Application.ScreenUpdating = False
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2011
        If ActiveCell.Offset(0, 166).Value = 2011 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2011").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2011").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        Else
    '2012
        If ActiveCell.Offset(0, 166).Value = 2012 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2012").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2012").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        Else
    '2013
        If ActiveCell.Offset(0, 166).Value = 2013 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2013").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2013").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        Else
    '2014
        If ActiveCell.Offset(0, 166).Value = 2014 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2014").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2014").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub

Could anyone shine a light for me why does it work with a single IF and not the others? How should this be structured so that I go down the list once only.

Thank you for your attention.
Final
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
For example this was my way around it but using several DO/ LOOPs but I would figure there should be a way to go through it only once.
Code: 
Sub Estrutura()
Application.ScreenUpdating = False
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2011
        If ActiveCell.Offset(0, 166).Value = 2011 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2011").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2011").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2012
        If ActiveCell.Offset(0, 166).Value = 2012 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2012").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2012").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2013
        If ActiveCell.Offset(0, 166).Value = 2013 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2013").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2013").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2014
        If ActiveCell.Offset(0, 166).Value = 2014 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2014").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2014").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
 
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this, made a minor adjustment to you Else Ifs

Code: 
Sub Estrutura()
Application.ScreenUpdating = False
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2011
        If ActiveCell.Offset(0, 166).Value = 2011 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2011").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2011").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        ElseIf ActiveCell.Offset(0, 166).Value = 2012 Then    '2012
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2012").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2012").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        ElseIf ActiveCell.Offset(0, 166).Value = 2013 Then '2013
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2013").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2013").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        ElseIf ActiveCell.Offset(0, 166).Value = 2014 Then '2014
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2014").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2014").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        Else
        'Do Nothing
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Comfy said:
Try this, made a minor adjustment to you Else Ifs

Code: 
Sub Estrutura()
Application.ScreenUpdating = False
Sheets("Base_1").Select
Range("B4").Select
Do While ActiveCell <> Empty
    '2011
        If ActiveCell.Offset(0, 166).Value = 2011 Then
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2011").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2011").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        ElseIf ActiveCell.Offset(0, 166).Value = 2012 Then    '2012
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2012").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2012").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        ElseIf ActiveCell.Offset(0, 166).Value = 2013 Then '2013
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2013").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2013").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
        ElseIf ActiveCell.Offset(0, 166).Value = 2014 Then '2014
            If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1,FP1,FQ1").Copy
            Sheets("2014").Select
            Range("B35").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B35").End(xlUp).Offset(1).EntireRow.Insert
 
            Else ' ñ vantajoso
            Rows(ActiveCell.Row).Range("B1,V1,Z1,AC1,FN1,FK1").Copy
            Sheets("2014").Select
            Range("B1048576").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Range("B1048576").End(xlUp).Offset(1).EntireRow.Insert
 
            End If
 
        Else
        'Do Nothing
        End If
 
    Sheets("Base_1").Select
    ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
Click to expand...


Great that worked perfectly, so I guess when ever you have and ELSE within a if I must use the ELSEIF then correct?

Thank you very much for your help mate!
final
 
Upvote 0
finaljustice said:
Great that worked perfectly, so I guess when ever you have and ELSE within a if I must use the ELSEIF then correct?

Thank you very much for your help mate!
final
Click to expand...

Only use elseif when validating against multiple criteria.

Here are 3 examples of ifs:

(No Need for Else)
If myString = "Help" Then myNumber = 2
End if

(Use of Else)
If mySting = "Help" Then
myNumber = 2
Else
myNumber = 1
End if

(Multiple criteria)
If mySting = "Help" Then
myNumber = 2
Elseif myString = "No Help" Then
myNumber = 0
Else
myNumber = 1
End if
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Add total row with a loop
Replies
4
Views
138
gberg
G
JohnZ1156
  • Solved
Print only visible Filtered Rows from Excel Table
Replies
2
Views
313
JohnZ1156
JohnZ1156
T
  • Question
Years together I was using a macro for automate a w/book is not as on today why?
Replies
1
Views
347
GlennUK
G
L
  • Question
Formula & Sub to format phone numbers working by itself but not in conjunction with other code.
Replies
4
Views
190
Scott Huish
Scott Huish
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
120
Scifo
S

Forum statistics

Threads
1,214,812
Messages
6,121,693
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top