Relative Macro

[markDuffy]

Hi I am trying a lesson to learn about VBA. Not sure if I should ask this here. I have been doing it again and again for hours. I also appreciate that left in is all the Microsoft stuff which could be stripped out. I have left it in so as to show that the code has not been messed with.

I have uploaded an excel file “ExcelForum100”

There are two macros
Ctrl-shift-L which is a simple absolute macro and runs perfectly.
Ctrl-Shift-A which is a simple Relative macro which I cannot get running.
Sheet one is from a shorter list and is what I get from the absolute macro.
Sheet is a slightly larger list to demonstrate the flexibility of a relative macro. You will see it is missing the total.
Sheet three is the file used to create the macro which works fine.
Honestly I am totally flummoxed.

This is a course I am doing so as to be able to understand the directions from you so despite being an odd request is actually related to here….I hope.

The line which is stopping it all seems to be 5 from the bottom
“ActiveCell.Offset(-1, -3).Range("A1").Select”

Please Login or Register  to view this content.

Mark

[Solus Rankin]

What range are you trying to select? A1?

You can't offset to the left of column A and the range collection is not part of ActiveCell which if I had to guess was an object.

ActiveCell.offset(-1,-3).select maybe? Or Range("A1").Select?


e/ Turns out Range is a property. But the questions remain the same. I'm guessing you're trying to offset from left of column A (there isn't a column left of A).

[markDuffy]

Hi Solus

thanks for this. I was directed to an Auto complete validation which also allowed new data and i could not work out how to use it so am going back to study. Long way to go. I will study your suggestion which is most likely correct but how did it get written like that as it was generated by recording.

Mark

[Solus Rankin]

The macro recorder can record in relative if you ask it to. What I'm guessing is there was a different "starting cell" when you record then there was when you ran the macro. If you run the macro with a starting position left of where the recorded starting position (activecell) was then you run the risk of running out of columns.

[markDuffy]

Thanks again Solus. I have been studying the lines based on what you said earlier. Below I show what i have learnt. Actually having had to study every line has helped me enormously.

Please Login or Register  to view this content.

hope this is useful to anyone who has had my problem. In all honesty I am still not sure how i created a -1 for the column.
Again thanks. Pleased to make this solved.

Mark