For/next loops and how to enter

Lincoln Delph · #1 08-14-2017, 02:47 AM

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.

Pecoflyer · #2 08-14-2017, 03:45 AM

Hi and welcome
couldn't you just use a filter?

Pecoflyer · #3 08-14-2017, 03:49 AM

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.

Lincoln Delph · #4 08-14-2017, 04:02 AM

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.

Lincoln Delph · #5 08-14-2017, 05:15 AM

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.

Lincoln Delph · #6 08-14-2017, 07:04 AM

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

NoSparks · #7 08-14-2017, 08:56 AM

Perhaps this will assist

Lincoln Delph · #8 08-15-2017, 01:38 AM

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.

NoSparks · #9 08-15-2017, 07:02 AM

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.

Lincoln Delph · #10 08-15-2017, 08:20 AM

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.

NoSparks · #11 08-15-2017, 09:12 AM

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.

Lincoln Delph · #12 09-05-2017, 04:00 AM

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.

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