Help Writing Code

[Dazzawm]

This is more or less the first code I have written, but could someone point out where it is failing. I am trying to find "Front Link Rod" in column S then copy the entire row onto sheet 2 but all it does is keep copying the first row. I dont think I am far off. Thanks

Sub test()
Dim a As Integer
Dim b As Integer
Dim c As Integer
Range("S1").Select
a = ActiveCell.CurrentRegion.Rows.Count
c = 1
For b = 1 To a
If Selection.Value = "Front Link Rod" Then
Selection.EntireRow.Copy
Sheets("sheet2").Select
Cells(c, 1).Select
ActiveSheet.Paste
c = c + 1
Sheets("sheet1").Select
Else
Selection.Offset(1, 0).Select
End If
Next b
End Sub

 

[Dazzawm]

Dazwm,

I respect that you want to learn and we all start somewhere. I would go ahead and take a gander at hiker's code when you have a change because he does do a couple of things better - namely using Option Explicit and not selecting at all as well as toggling ScreenUpdating to keep the screen from flickering (and speed things up).

Having said that - you fell into a common pitfall. Your particular loop doesn't need an ELSE clause at all. Simply move the part where you select the new row below the END IF statement and delete the ELSE keyword all together.

And in general, loops are slow. The real solution would be to use Autofilter and simply copy it all over in one fell swoop.

I know the easy way would be to sort by that column and copy the whole lot, but I am wanting to learn code. This is a pretty basic code compared to some I see and once I get my head around this one I will be more understanding of more complicated codes.

 

[Dazzawm]

Dazwm,

Having said that - you fell into a common pitfall. Your particular loop doesn't need an ELSE clause at all. Simply move the part where you select the new row below the END IF statement and delete the ELSE keyword all together.

How can I remove the ELSE statement if I have a FOR?

 

[Greg Truby]

I know the easy way would be to sort by that column and copy the whole lot,

NOT Sort! AutoFilter!

How can I remove the ELSE statement if I have a FOR?

ELSE statements are part of IF/END IF statements, not FOR/NEXT loops.

 

[ukmikeb]

What does that mean lol, where has it gone wrong?

Generally it is good practice to indent code where there are sections of code which include-

For .....Next
If ........Else .....End If
With........End With
Do...... Loop Until

and within each section you use Tab to move the line of code usually 4 spaces to the right.

Annotating code is putting Comments in the code (the first character on the line is a single quote) so that you have a record of what the section of code is supposed to be doing.

Debug is a useful tool which enables you to watch the progress of your code if you want to find out why your code is not working in the way you intended. The simplest way to activate it before you run the code is to click the vertical bar to the left of the code on one or more of the lines of code.

When you do this the line will be highlighted with a brown background and a brown dot in the vertical bar - this is known as a break point.

When you run the code then if the normal progress of the code goes through that line then the line will highlight in yellow and the code will stop.

While the code is stopped you have the option of hovering the cursor over each of the variables in your code to view the current value of that variable.

When I wrote -

Originally Posted by ukmikeb
You need to progress your selection irrespective of whether you have found a cell meeting the criteria or not.

At the moment you are only moving to the next row if there isn't a match.

I was trying to point you in the direction of what Greg has told you here -

Your particular loop doesn't need an ELSE clause at all. Simply move the part where you select the new row below the END IF statement and delete the ELSE keyword all together.

in a roundabout way so that you would learn from your elementary error.


hth