Solved

VBA loop.

Posted on 2011-09-06
14
394 Views
Last Modified: 2012-05-12
Hello experts

I have this code
Sub loopA()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet2")
For Each cel In Sheets("sheet1").Range("B" & Sheets("sheet1").Range("B1").End(xlDown).Row & ":B" & Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub

Open in new window

which is taking each URL from sheet1 "B1" and placing it into sheet2 "A1" in a loop.  I would like to change the following,

Rather than taking the first one and then next and so on.  Where the used URLs always stay in sheet1, I would like it to remove each URL from sheet1 once it is placed in sheet2, and then the loop should simply always take the first one…

Thank you
0
Comment
Question by:Olympia275
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36488941
Delete the row or only the cell?
0
 

Author Comment

by:Olympia275
ID: 36488947
only the cell
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36488965
This seems a little odd to me, but maybe I'm missing something.  Doesn't the loop just keep writing over sheet2:A1 over and over, so it will only have the last value after the loop exists?  Why not just get the last value in the first place?

~bp
0
 

Author Comment

by:Olympia275
ID: 36489017
That's correct; I will always have in sheet2, the latest value.  However, I need to remove from sheet1.  This code is running for hours and when it is interrupted it always starts Again from the first URL, so I manually have to search and remove the used URLs from my URL list in sheet1

Also, I'm having a memory leak problem and I'm thinking that when VBA needs to memorize all used URLs.  This may use up some memory?  So rather than memorize and go to next, just remove used URLs and always use the first.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36489088
In that case you can simplify

Put the first cell in B into Sheet 2 and then delete existing data in column B of sheet1

Chris
Sub loopA()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet2")

mySheet.Range("A1") = Sheets("sheet1").Range("B1").End(xlUp).Value
Sheets("sheet1").Range("B:B").Clear

End Sub

Open in new window

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 36489169
Whilst I do not understand the loop parameters ... assuming you have them correct then one way to use your code to the same purpose but as a loop is changing to a row increment and then clearing the content of a given cell.

Note I am assuming in this case that when you post the value to sheet 2 an event is triggered and that therefore you want to use that structure.

Chris
Sub loopA()
Dim cel As Long
Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet2")
    For cel = Sheets("sheet1").Range("B1").End(xlDown).Row To Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
        mySheet.Range("A1") = Sheets("sheet1").Range("B" & cel).Value
        Sheets("sheet1").Range("B" & cel).Clear
    Next cel
End Sub

Open in new window

0
 

Author Comment

by:Olympia275
ID: 36489229
you are correct, an event is triggered, which starts on entire program, with many filters...

I have tested your code, it is taking just one URL removing and placing it in sheet2 but is not coming back for the next URL.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 51

Expert Comment

by:Bill Prew
ID: 36489434
Chris is the expert here, but it sounds like you want something like this:

Sub loopA()
    Dim cel As Range
    Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet2")

    Do While Range("B1").Value <> ""
        mySheet.Range("A1") = Range("B1").Value
        Range("B1").Delete Shift:=xlUp
    Loop

End Sub

Open in new window

~bp
0
 

Author Closing Comment

by:Olympia275
ID: 36489509
Thanks.

It is working now.  Maybe it was a problem with my sheet.  I placed all the codes in a new sheet and it is now working as expected.

Thanks so much.
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36489575
I try to stay away from storing large amounts of data in memory.
I glad you have a solution - I would use something like this:


Sub loopA()

    ' turn screen updates off for speed
    Application.ScreenUpdating = False
   
   
    ' Position where you wish to start in sheet 2
    Sheets(2).Select
    Range("Sheet2!A1").Select
   
    ' Position where you wish to start in sheet 1
    Sheets(1).Select
    Range("B1").Select
   
    ' Loop while there is a value to work with
    Do While ActiveCell.Value <> ""
       
        ' Store value temporarily
        myValue = ActiveCell.Value
       
        ' Remove from Sheet 1
        ActiveCell.ClearContents
       
        ' Jump to next cell down in Sheet 1
        ActiveCell.Offset(1, 0).Select

        ' Jump to sheet 2
        Sheets(2).Select
       
        ' Paste stored value
        ActiveCell.Value = myValue
       
        ' Set hyperlink if desired
        MakeLink ActiveCell, myValue, "", ""
       
        ' Move to next cell in Sheet 2
        ActiveCell.Offset(1, 0).Select
       
        ' jump back to sheet 1
        Sheets(1).Select
               
    Loop

    ' turn screen updates on
    Application.ScreenUpdating = True


End Sub

Sub MakeLink(ByVal cell As Range, ByVal url As String, _
    ByVal txt As String, ByVal tooltip_text As String)
    ' function from: http://vb-helper.com/howto_excel_make_hyperlink.html

    ActiveSheet.Hyperlinks.Add _
        Anchor:=cell, _
        Address:=url, _
        ScreenTip:=tooltip_text, _
        TextToDisplay:=txt
End Sub

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36489600
Bill, too kind but just worth a mention ... checking for an empty cell is fine in most cases ... but if there are blanks in a data series then this would stop execution.  The range as used by the OP has the benefit of addressing non contiguous data ranges.

Chris
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36489623
Good point, thanks Chris, so we would likely have to determine the full "height" of the range up front and then loop that many times in my approach?

I interpreted the OP desire slightly differently, and thought they wanted to actually remove the cells once processed rather than just clear them, which doesn't seem to have been the desire.

~bp
0
 

Author Comment

by:Olympia275
ID: 36489667
thanks for all of you. Chris and Bill, I think that was the problem.  Why bills code wasn't executing. and for slycoder your code Is not overwriting in sheet2 it is simply copying and adding rows...
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36489709
Allowing for no formatting on a cell the clear is pretty much the same as delete.  The beauty of the range using the built in methods is it is precise so for example:

Sheets("sheet1").Range("B1").End(xlDown) is the last populated cell in the first block of data
Sheets("sheet1").Range("B" & rows.count).End(xlUp) is the last populated cell in the sheet

Hence no end to numbers of blank cells within the range of first to last.  The range can of course be processed to delete and move the cells up as long as the deleted cell is the first one in the range.

For info another ranging approach that allows for rows > 1 as the first populated cell and random other row as the last is to use the 'usedrange':

activesheet.usedrange.cells(1).row ... row number of the first row with data entered
?activesheet.usedrange.rows(activesheet.usedrange.rows.count).row ... last row with data

In this case the row data returned would include any cells populated and subsequently deleted so is sometimes better and sometimes worse than a simple range.

Chris

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now