Wrote a macro in visual basic editor, can't get it to repeat the action

Hi Frank,

I am still working on making the macros work on additional entries. I
think I am missing something really simple here. I am trying
variations on inserting a continuous instruction in the code but I
result in errors.

I have attached the macro (inside VBE) if you wanted to check. Any
recomendations would be helpful. Thanks in advance Frank,

Matt

Attachment filename: test book excel project matt.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=579085
---
Message posted from http://www.ExcelForum.com/

Hi
to be honest I won't open your file if it contains macros. Simply post
your code (or at least the relevant part) as plain text

--
Regards
Frank Kabel
Frankfurt, Germany


Hi Frank,

I am still working on making the macros work on additional entries.
I
think I am missing something really simple here. I am trying
variations on inserting a continuous instruction in the code but I
result in errors.

I have attached the macro (inside VBE) if you wanted to check. Any
recomendations would be helpful. Thanks in advance Frank,

Matt

Attachment filename: test book excel project matt.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=579085 ---
Message posted from http://www.ExcelForum.com/

Hi Frank-

Now that I think about it, I would not open a file with macros either.


I posted the below from my VB editor. I have been playing around with
it in various ways. I read in a reference book that I can make any
line function continuous but have not been successful with any of the
commands variations I have tried.

Any suggestions would be helpful. I do not mind experimenting with
different ideas but so if you can point me in a direction that you
think may work I can start there.

AS you can probably tell by my basic VB work that I am relatively new
at this. However I am learning so much right now and hope to be more
functional in VBA in the near future.

Once again Frank I appreciate any comments or suggestions.

cheers,
matt, sf



Sub Duplicate1()

Range("A2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("A5").Select

Range("B2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("B5").Select

Range("C2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("C5").Select

Range("D2").Select
ActiveCell = 100
Range("D3").Select
ActiveCell = 110
Range("D4").Select
ActiveCell = 120
Range("D5").Select
ActiveCell = 130
Range("D5").Select

Range("E2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("E5").Select

Range("F2").Select
ActiveCell.FormulaR1C1 = "No Sales Order"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("F4").Select
ActiveCell.FormulaR1C1 = "No Sales Order"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("F5").Select


Range("G2").Select
ActiveCell.FormulaR1C1 = "ARJE"
Range("G3").Select
ActiveCell.FormulaR1C1 = "REX_1"
Range("G4").Select
ActiveCell.FormulaR1C1 = "ARJE"
Range("G5").Select
ActiveCell.FormulaR1C1 = "REX_1"
Range("G5").Select

Range("H2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("H4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("H5").Select

Range("I2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C*-1"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C*1"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C*-1"
Range("I5").Select

Range("J2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("J5").Select

Range("K2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C*-1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C*-1"
Range("K5").Select

Range("L2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("L4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("L5").Select

Range("M2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("M4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("M5").Select

Range("N2").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!N2"
Range("N3").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!N2"
Range("N4").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!O2"
Range("N5").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!O2"
Range("N5").Select

Range("O2").Select
ActiveCell = "=Entry!P2"
Range("O3").Select
ActiveCell = "=Entry!P2"
Range("O4").Select
ActiveCell = "=Entry!P2"
Range("O5").Select
ActiveCell = "=Entry!P2"
Range("O5").Select

Range("P2").Select
ActiveCell = "=Entry!Q2"
Range("P3").Select
ActiveCell = "=Entry!Q2"
Range("P4").Select
ActiveCell = "=Entry!Q2"
Range("P5").Select
ActiveCell = "=Entry!Q2"
Range("P5").Select




End Sub


---
Message posted from http://www.ExcelForum.com/

Hi
this can definetly be shortened. But some things first:
- why are you using VBA at all for this. This could also be achieved
with simple formulas
- could you explain how you want to copy your data. e.g. which cells
should go to which cells in your target sheet.

--
Regards
Frank Kabel
Frankfurt, Germany


Hi Frank-

Now that I think about it, I would not open a file with macros
either.


I posted the below from my VB editor. I have been playing around
with
it in various ways. I read in a reference book that I can make any
line function continuous but have not been successful with any of the
commands variations I have tried.

Any suggestions would be helpful. I do not mind experimenting with
different ideas but so if you can point me in a direction that you
think may work I can start there.

AS you can probably tell by my basic VB work that I am relatively new
at this. However I am learning so much right now and hope to be more
functional in VBA in the near future.

Once again Frank I appreciate any comments or suggestions.

cheers,
matt, sf

Hi Frank,

I decided to do this in VBA becasue, 1) I am trying to learn more about
it, and 2) I thought it would be easier.

The cells I am copying from my master sheet do not correspond to the
same columns in my second sheet.

My idea when I started this was to enter one line of data on my master
sheet. And for this one line of data to copy into my second sheet in a
Journal Entry format to avoid having to manually retype all of the
data.

As you may be able to get an idea from the extract in my VB editor,
the one line of data I entered in my first sheet reflects into four
lines in my second sheet.

What I have written in the VB Editor translates exactly how want it.
The problem lies in getting this to work the same as I enter more lines
of data on my master sheet.

Did I go about this in the wrong way?


---
Message posted from http://www.ExcelForum.com/

Hi Nick, Sorry I don't have you email. But I wanted to thank you for
your tips. It definately shortened my commands by 1/3, and I learned a
little more about VB programming. Unfortunately I still have not been
able to to get my macro to work on my coresponding data entries from
"sheet1".

I just read about a sheet)array function that I am going to tinker
with. Thanks again,
cheers


---
Message posted from http://www.ExcelForum.com/