Solved

VBA loop.

Posted on 2011-09-06
14
404 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 52

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 52

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 52

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

23 Experts available now in Live!

Get 1:1 Help Now