Help Writing Code

Dazzawm · Jul 1, 2011

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

Code:

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

ukmikeb · Jul 1, 2011

Hi

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.

hth

Dazzawm · Jul 1, 2011

ukmikeb said:
Hi

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.

hth

Sorry what part of the code do I need to amend then.

hiker95 · Jul 1, 2011

dazwm,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub testV2()
' hiker95, 07/01/2011
' http://www.mrexcel.com/forum/showthread.php?t=561557
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w1.Cells(Rows.Count, "S").End(xlUp).Row
For a = 1 To LR Step 1
  If w1.Cells(a, "S") = "Front Link Rod" Then
    NR = w2.Range("S" & Rows.Count).End(xlUp).Offset(1).Row
    w1.Rows(a).Copy w2.Rows(NR)
  End If
Next a
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the testV2 macro.

Dazzawm · Jul 1, 2011

Thanks Hiker95 I appreciate all your effort but I really wanted you to point out where my code went wrong rather than writing another else I will never learn!!

ukmikeb · Jul 1, 2011

Hi

I have indented and annotated the code below -

Code:

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
'       Found matching row and copy to Sheet2
 
        Selection.EntireRow.Copy
        Sheets("sheet2").Select
        Cells(c, 1).Select
        ActiveSheet.Paste
        c = c + 1
'       Re-select Sheet1 to continue searching for criteria
        Sheets("sheet1").Select
    Else
'      Match not found - look at next row in Sheet 1
 
        Selection.Offset(1, 0).Select
    End If
Next b
End Sub

hth

Dazzawm · Jul 1, 2011

ukmikeb said:
Hi
I have indented and annotated the code below -

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

hiker95 · Jul 1, 2011

dazwm,

Your code contains many Select statements that will actually slow down the macro.

See the bold text below to help you understand what is going on.

Rich (BB code):

Option Explicit
Sub testV2()
' hiker95, 07/01/2011
' http://www.mrexcel.com/forum/showthread.php?t=561557
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")

''Find the LR, lastrow, in Sheet1, in column S
LR = w1.Cells(Rows.Count, "S").End(xlUp).Row

''Loop in Sheet1, column S, from cell S1 To S_lastrow
For a = 1 To LR Step 1

  ''If a cell in Sheet1, column S = "Front Link Rod"
  If w1.Cells(a, "S") = "Front Link Rod" Then
    
    ''Find the NR nextrow, next available blank row in Sheet2 in column S
    NR = w2.Range("S" & Rows.Count).End(xlUp).Offset(1).Row
    
    ''Copy the row in Sheet1 that contains in column S = "Front Link Rod"
    ''  to the next available blank row in Sheet2 in column S
    w1.Rows(a).Copy w2.Rows(NR)
  End If
Next a
w2.Activate
Application.ScreenUpdating = True
End Sub

hiker95 · Jul 1, 2011

dazwm,

This is more or less the first code I have written

Training / Books / Sites

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Cascading queries

http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

Excel VLOOKUP Function and VLOOKUP Example
http://www.contextures.com/xlFunctions02.html

INDEX MATCH - Excel Index Function and Excel Match Function
http://www.contextures.com/xlFunctions03.html

http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)
by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

Professional Excel Development
by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)

Excel 2002 VBA: Programmers Reference
by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

VB & VBA in a Nutshell: The Language
(http://www.amazon.co.uk/VB-VBA-Nutsh...4671189&sr=1-2)

Writing Excel Macros with VBA
(http://www.amazon.co.uk/Writing-Exce...4671189&sr=1-3)

User Form Creation
http://www.contextures.com/xlUserForm01.html

DonkeyOte: My Recommended Reading
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Greg Truby · Jul 1, 2011

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.

Help Writing Code

Dazzawm

Well-known Member

Excel Facts

ukmikeb

Well-known Member

Dazzawm

Well-known Member

hiker95

Well-known Member

Dazzawm

Well-known Member

ukmikeb

Well-known Member

Dazzawm

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

Help Writing Code

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock