Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro/Method of Importing DATA in a loop | Excel Discussion (Misc queries) | |||
Creating a macro for data input. | Excel Discussion (Misc queries) | |||
Creating a macro to formatt data | Excel Programming | |||
Creating macro to lookup data | Excel Discussion (Misc queries) | |||
Excel macro to loop through worksheets and graph data from each worksheet | Excel Programming |