Macro writing

  • Thread starter Thread starter m.zelenak
  • Start date Start date
M

m.zelenak

I understand the concept of macros, I just don't know how they execute and
the possible functions.
I would like to import a considerable amount of data from excel into access.
The problem I have is that the information I want to import for each record
is on two adjacent lines. I need a macro that will move the data contained
in just one cell to the end of the row above it and then delete the row
that, that information was contained in. Then I need it to repeat that
function on every other row. Sounds easy right? Way my luck runs you could
do anything you want to do except what I need. Anyone know how to do this?
Or direct me to a site I could research it to do it on my own.

Mike
 
Hi Mike

IMHO the best way if you're starting out with excel macros would be to
record the macro and then have a look at the code ... (to record - choose
tools / macro / record new macro; to view the code - choose tools / macro /
macros, click on your macro and choose edit)

i recorded a macro doing what you asked about and ended up with this code
the bits after the ' are explanations of what the code is doing
****
ActiveCell.Offset(1, 2).Range("A1").Select 'select a cell one row down
and two columns across from where the cursor is - cursor was in A2,
therefore i'm selecting C3
Selection.Copy 'copy it
ActiveCell.Offset(-1, 0).Range("A1").Select 'move the cursor up one row
Selection.End(xlToRight).Select 'move the cursor to the last cell
entered in the row
ActiveCell.Offset(0, 1).Range("A1").Select 'move the cursor right one
column
ActiveSheet.Paste 'paste the copied data
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select 'move the cursor
down one row
Application.CutCopyMode = False 'turns off the marque after you've
copied something
Selection.Delete Shift:=xlUp 'delete the row
'here's where i repeated the action for the next row
ActiveCell.Offset(1, 2).Range("A1").Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
****
i recorded this in relative mode rather than absolute (after naming the
macro & clicking OK click on the Relative addressing icon on the stop
recording toolbar - ie make sure its indented)

.....
now to repeat this you can add a loop to the code

***
Do Until ActiveCell = "" 'keep repeating the actions until the
ActiveCell is blank
ActiveCell.Offset(1, 2).Range("A1").Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Loop

****
you could even add a little message to yourself at the end

- after the loop line (and before the end sub line)
***
msgbox "Finished!"
****

let us know how you go

Cheers
Julie
 
Back
Top