Creating a loop in a Macro to do through data
 

Hi,

I am trying to create a Macro in Excel to go through a list of names on one
sheet, and starting from the top pick one up at a time and perform the action
prescribed by the macro. I need some help with some coding in Excel. I have
got the action setup in the macro, however I am unable to get the macro to
change what its looking at.

See code below, in this line of Code (ActiveCell.FormulaR1C1 =
"=Entities!R[-3]C[-1]"), I would like it to change the cell each time its
performed the action and keep changing and continuing the action until it
arrives at a blank cell.

Sub EMEA_Reporting()
'
' EMEA_Reporting Macro
' Macro recorded 01/04/2009 by KRazaq
'

'
ActiveCell.FormulaR1C1 = "=Entities!R[-3]C[-1]"
Range("B6").Select
Sheets("Base").Select
Sheets("Base").Copy Befo=Sheets(1)
Selection.ShapeRange.Delete
Cells.Select
ActiveWorkbook.BreakLink Name:="C:\Hyperion\SmartView\bin\HsTbar.xla",
Type _
:=xlExcelLinks
End Sub


Thanks for you help.

Khurram

Creating a loop in a Macro to do through data
 

On Apr 1, 11:12*am, khurram_razaq
wrote:
Hi,

I am trying to create a Macro in Excel to go through a list of names on one
sheet, and starting from the top pick one up at a time and perform the action
prescribed by the macro. I need some help with some coding in Excel. I have
got the action setup in the macro, however I am unable to get the macro to
change what its looking at.

See code below, in this line of Code (ActiveCell.FormulaR1C1 =
"=Entities!R[-3]C[-1]"), I would like it to change the cell each time its
performed the action and keep changing and continuing the action until it
arrives at a blank cell.

Sub EMEA_Reporting()
'
' EMEA_Reporting Macro
' Macro recorded 01/04/2009 by KRazaq
'

'
* * ActiveCell.FormulaR1C1 = "=Entities!R[-3]C[-1]"
* * Range("B6").Select
* * Sheets("Base").Select
* * Sheets("Base").Copy Befo=Sheets(1)
* * Selection.ShapeRange.Delete
* * Cells.Select
* * ActiveWorkbook.BreakLink Name:="C:\Hyperion\SmartView\bin\HsTbar.xla",
Type _
* * * * :=xlExcelLinks
End Sub

Thanks for you help.

Khurram

Khurram,

Here is some code to pick up the contiguous cells in a range (i.e.
starting at A1 and looking down the column until there is a blank
cell). I'm not quite sure what you trying to accomplish, so the code
below has syntax necessary to pick up a range to loop through and then
loops through each item in the range.

Best,

Matt Herbert

Sub LoopThroughCells()

Dim rngData As Range
Dim rngItem As Range

With Worksheets(1) '<< index name or number here
Set rngData = .Range("A1", .Range("A1").End(xlDown))
End With

For Each rngItem In rngData
MsgBox rngItem.Value
Next

End Sub