Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-14-2017, 02:47 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default For/next loops and how to enter

In the days of Sinclair and Amstrad I could do For/Next Loops in my sleep. Unfortunately with the advent of the PC I never progressed beyond Basic and I find that at the age of 80 I don't pick things up so easily these days.

I am hoping that someone out there would kindly provide me with the full syntax I am looking for to do what I want to do.

I have a worksheet with several columns of info Column A is a date and Column be is a figure. Every so often Column B has a 0 value. What I am looking for is the syntax to step through the columns and select the 0 records and produce them as consecutive records either elsewhere on the sheet or on another sheet.



If this syntax is forthcoming could you also explain how/where this is entered in the spread sheet.

Thank you for reading.
Reply With Quote
  #2  
Old 08-14-2017, 03:45 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,941
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Hi and welcome
couldn't you just use a filter?
Reply With Quote
  #3  
Old 08-14-2017, 03:49 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,941
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

If you really need VBA it is not necessary to use loops for this kind of problem.
Have a look at this autofilter technique.
To insert the code hit Alt+F11 and enter it in a module.
Reply With Quote
  #4  
Old 08-14-2017, 04:02 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

Thank you. I've had a look and will give it a try but to be honest it looks a little complicated. Not sure how I can adapt it for what I need but I'll give it a try.
Reply With Quote
  #5  
Old 08-14-2017, 05:15 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

A further thank you for taking the time to reply. I looked into your recommendation and realised that auto filters did exactly what I was looking for. If you hadn't pointed the way I would never have found it.
Reply With Quote
  #6  
Old 08-14-2017, 07:04 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

I now realise that the solution I thought I'd found does not do what I want. The filter system just hides the lines that don't meet the criteria. What I'm looking for is a way to step through the data select a matching line and paste it elsewhere.
The end result being that if the criteria is met in B5 the row 5 becomes row 1 elsewhere on the sheet. The next row that meets the criteria becomes row 2 etc.
I'm sure this must be possible but it's beyond my anility and understanding which is why I was really looking for a complete syntax
Reply With Quote
  #7  
Old 08-14-2017, 08:56 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 411
NoSparks is on a distinguished road
Default

Perhaps this will assist
Attached Files
File Type: xlsm Lincoln Delph.xlsm (16.7 KB, 8 views)
Reply With Quote
  #8  
Old 08-15-2017, 01:38 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

Thank you for taking the time to try to help me. Unfortunately I don't know really know how to use it. How do I incorporate it into a worksheet. I have saved it. I have also tried to open it in order to study it and perhaps learn a bit about how to build a macro. When I try to open it, excel opens with a blank screen and when |I click on View Macro I get a message to say that excel has stopped working and windows has found a problem.Have I done something wrong.
Reply With Quote
  #9  
Old 08-15-2017, 07:02 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 411
NoSparks is on a distinguished road
Default

Have no idea as to why Excel stopped working and Windows finding a problem other than perhaps a corruption within the Excel installation.
I've never used it but believe there is a "Repair" option of some sort.

Anyway, lets start from scratch...
Open your Excel to new blank workbook with at least 2 sheets, leave the sheet names at the default Sheet1 and Sheet2.
On Sheet1 put in some data that reflects what you describe in your original post with some column B cells containing only a single zero.
Bring up the Visual Basic for Applications (VBA) Environment. (Short cut to this is Alt + F11)
From the menu bar, Insert > Module
Type (or paste) this into the Module that was just inserted.
Code:
Sub Testing()
    'declare all variables
    Dim lastrow As Long
    Dim i As Long
    Dim writerow As Long

'the first row to write to
writerow = 1

With Sheets("Sheet1")
    'establish last cell with data in column B
    lastrow = .Range("B" & Rows.Count).End(xlUp).Row
    'the rows to loop through
    For i = 2 To lastrow
        ' do what you want regarding this row
        ' BUT **** DO NOT DELETE IT ****
        ' for that you must work from the bottom up using step -1
        If .Range("B" & i).Value = 0 Then
            .Range("B" & i).EntireRow.Copy Sheets("Sheet2").Range("A" & writerow)
            writerow = writerow + 1
        End If
    Next i
End With
End Sub
Close the VBA environment.
Close and save the workbook as an Excel Macro-Enabled Workbook with the .xlsm file extention.
Re-open the just saved workbook.
Bring up the Macro dialogue box, View Macros (Alt+F8 is shortcut)
Run the macro.
See that rows where column B is 0 were copied to Sheet2.

Hopefully the VBA instructions along with the added comments will help you follow what the macro does.
Putting your cursor anywhere within the macro and using the F8 key will step one line at a time through the macro.

Hope this helps.
Reply With Quote
  #10  
Old 08-15-2017, 08:20 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

Thank you so much for your time and patience. Somebody has suggested that there may be a block in PC that won't pass macro files. Don't know whether that was a possibility but if it was I wouldn't begin to know where to look.
Anyway many thanks.
Reply With Quote
  #11  
Old 08-15-2017, 09:12 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 411
NoSparks is on a distinguished road
Default

Don't know about Excel 2016, but my Excel 2010 default installation was Disable all macros without notification

maybe one of these will help

https://support.office.com/en-us/art...f-e8621e8fe373
https://www.youtube.com/watch?v=CyklPFw69_o
https://powerspreadsheets.com/how-to...-macros-excel/

Windows also has some setting somewhere that doesn't like running anything that came from the internet.
That was the line of thought for my previous post.

Beyond that, afraid I can't help you.
Reply With Quote
  #12  
Old 09-05-2017, 04:00 AM
Lincoln Delph Lincoln Delph is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2017
Posts: 7
Lincoln Delph is on a distinguished road
Default

Hi
Just Like to thank you again for the time you gave my problem. Sorry it has taken so long for final comment but I have adapted your code into my spreadsheets and it does exactly what I wanted.

As regards blocking of macro, I'm still nowhere with that. Got on to MS Office on chat, hadn't a clue why I had the problem. Had him looking through my installation but still no idea. But as you've solved my problem it doesn't matter now.

Thanks again.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching Arrays without Loops? ptmuldoon Word VBA 3 12-13-2014 11:21 AM
HTML in email loops endlessly rbtroj Outlook 0 05-19-2011 12:29 PM
Enter + Alt + Enter is not working inside the text box! why???? Jamal NUMAN Word 3 04-17-2011 05:35 PM
Powerpoint loops, but not to beginning imeister PowerPoint 1 02-02-2011 02:05 PM
loops within presentations supateach PowerPoint 1 11-23-2010 04:42 AM


All times are GMT -7. The time now is 11:43 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft