how to loop in macro?

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,676
if i am in an active cell which is empty and want to stop the macro at this point, what code should i type in visual basic to achieve this ... thanks :)
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,676
thanks dear .. also can u tell me how to repeat a code in vba.
it would be like pressing the assign value for the code again and again. but i believe there should be a code in vba for this..
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
It sounds like you may be selecting ranges in your VBA code, which is generally not necessary, you can work with ranges directly in VBA.

What is it you are trying to do exactly?
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,676
Sub Macro2()
'
' Macro2 Macro
'

'
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Application.Goto Reference:="R30C10"
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("A9").Select
ActiveSheet.Paste
Sheets("History").Select
Range("G3").Select
End Sub



..now i want to repeat this macro until Range("G3").Select is "Blank" .
Note: i am using relative references so G3 will continue below to be G4, G5 G6 and so on, untill there is a blank value in it that should stop the macro performing above.

i think i need a loop macro but i can't understand on how to use it with this code.. thanks :)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
You can probably do this without a loop. You want to copy from G2 down until it hits a blank from what sheet and paste it where exactly?
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,676
copy from Sheets("History").Select
and paste to Sheets("Sheet3").Select

lets say paste it in cell A1 of Sheet 3
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
XL2007 won't let you name a sheet History, so I had to call it History2 for this sample:

Code:
Sub test()
Worksheets("History2").Range("G2", Range("G2").End(xlDown)).Copy Worksheets("Sheet3").Range("A1")
End Sub
 

foolycoolycurry

New Member
Joined
Aug 14, 2006
Messages
3
try:
Code:
If Activecell.Value = "" Then Exit Sub
Thanks so much. This also helped me. I am working in MS Project's VBA and couldn't get a "Do Until" loop to work correctly with the ActiveCell variable. This puts a stop to it just when I want it to.
 

Forum statistics

Threads
1,081,728
Messages
5,360,925
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top