SIGN UP

Editing A Marco

Avatar Image
Old_Geezer | 11:32 Tue 13th Dec 2016 | Computers
5 Answers
Hopefully a quick, and for those with expertise, easy question regarding Excel 2003 macros.

I used Excel to create a macro to correct an error that occurs when I copy data from one place and paste it into the spreadsheet. It worked well enough first time but I find it doesn't subsequently, and I'm sure it is because it has a strange choice of default, and opts to code the less likely to be required, specific cell locations, in the macro instead of the almost certainly required, start from the present cursor position and work from there.

As an example I see the following as part of the macro created.

Range("A216").Select
Selection.Cut
Range("F215").Select
ActiveSheet.Paste

The reference to A216 and F215 are clear nonsense. That will only ever work if the data is always in the same place which, since the data is time/date order is never going to be needed again. Once corrected in that location it doesn't need correcting there again. It needs to be corrected at the position containing the new flawed data.

Ideally I'd like to achieve 2 things. Start at the row's A column cell, drop down to the cell below and remove superfluous (always the same) text at the end of the cell contents, leaving the numeric (currency as it happens) value. Then cut that cell, return up to the first row again, travel across to the F column where the data should be (and used to be until recently) and paste there. Then delete the now empty row below, and move down ready to correct the next lot.

But I could probably work with simply a quick solution as to how to get the macro to not move to a specific cell to start itself but work where the cursor already is.

Anyone have any solution I could use please ?

TIA.

Answers

1 to 5 of 5rss feed

Avatar Image
Use activecell.select to do your selection cut on then activecell.offset(0,5) for the paste, I think
12:05 Tue 13th Dec 2016
Use activecell.select to do your selection cut on then activecell.offset(0,5) for the paste, I think
Question Author
Cheers. I'll play around with that, as I want to to up and down columns too, and see what I can achieve with it.
Question Author
Almost got a simple one working. If I have moved the selection to cell A in a row, what do I put to select the whole of that row for deletion ?

I tried Row.Select and the editor was happy, but run time was not.
Question Author
Ah no panic, needed "ActiveCell.EntireRow.Select"

Cheers. I'll carry on playing around and try to improve it.
To move up or left use negative numbers in the offset brackets

1 to 5 of 5rss feed

Do you know the answer?

Editing A Marco

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.