#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
Hi and welcome
couldn't you just use a filter?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
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.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Perhaps this will assist
|
#8
|
|||
|
|||
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.
|
#9
|
|||
|
|||
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 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. |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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. |
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 |