how to loop in macro?

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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 :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
2,018
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
2,018
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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,136,341
Messages
5,675,203
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top