?
Solved

VBA loop.

Posted on 2011-09-06
14
Medium Priority
?
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 56

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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
 
LVL 56

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 56

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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