Excel macro set-up?

Question

Does anyone know how I can set up a macro that I can run on different cells? I am repeating the same steps hundreds of times, but the macro I currently have has the specific cells recorded that I used originally. (For example - lets say I recorded the macro using cells B15, B18 and J25 to make changes. I now have to make the same exact changes on a variety of rows in the same cells like j15,J18 and J25 then AA15, AA18 and AA25 and so on.) Every time run the macro it automatically defaults back to row b where it was originally recorded. i can I change it to use the formatting but change the rows?

Answer 1

The first answerer is right in a way, but a person could help you out a little more than that.

There are two ways you can do it. One way is to use an InputBox to grab the range. That could be a little more difficult to explain so I'm going to go with the easiest way to explain.

I'm assuming you already have your cells selected that you want changed when you run your macro. So I'll explain it using the Selection method.

Lets take your example for instance. Just got in to your coding and change a few things.

Your first cell on your example should say something like:

Range("B15").Select
Selection. then something or other here

Then the rest of your code here.

The macro recorder specifies the exact cell you chose. You want to more "generalize" yours.

So start yours off with:

"Selection" then a dot then the rest of the line code

This will change what you have selected and not the exact range.

Then if the number of rows and columns that the rest of the cells offset stay the same, you can use the Offset property to change those based on the cell you have selected.

The offset property works like this:

Selection.Offset(# of Rows, # of Columns) ...the rest goes here

If I wanted to change your first group (B15, B18, and J28), I to where the selected cell (B15) states "red", B18 states "white", and J28 states "blue". I could do it like this:

ActiveCell.Value = "red"
ActiveCell.Offset(3, 0).Value = "white"
ActiveCell.Offset(13, 8).Value = "blue"

or

Selection.Value = "red"
Selection.Offset(3, 0).Value = "white"
Selection.Offset(13, 8).Value = "blue"

I prefer the first method unless changing a group of cells to the same value.

If I use selection that would change EVERYTHING that is selected and it is more for use of more than one cell being selected.

Say for instance if I had 20 cells selected and I wanted all of them to state "I love Excel!". I could write:

Selection.Value = "I love Excel!"

Answer 2

you could edit the source code to either pop up an entry box to ask for the cell range to perform the macro on, or to perform the macro on the current selection - both ways require some knowledge of vba to make the alterations though.

Answer 3

Maybe, you should make sure that you use "Relative Reference" (in the Stop toolbar, when you record a macro. Next to Stop button).

So, wherever you apply this macro, it will apply to cell that you are currently select. Not at original cell that you record the macro before.

Hope, helpfully

------------------------------