Recording Macros

Can I get help in this newsgroup with recording macros?

I have recorded the following macro using the macro recorder. The cursor was
'in' cell E7

    Selection.Cut
    Range("E5").Select
    ActiveSheet.Paste
    Range("E10").Select

I would now like to convert this to a general purpose macro. I need help
using [R][C] notation.

The steps are simple:


For i = 1 to 100
(1) Using the active cell, cut its value (a constant)
(2) Move up 2 rows
(3) Paste value
(4) Move down 5 rows.
Next i

How do I change the recorded macro?

Thanks for help.

EdC




0
econgleton (13)
6/18/2004 12:08:26 AM
excel 39879 articles. 2 followers. Follow

5 Replies
509 Views

Similar Articles

[PageSpeed] 56

something like this. UNTESTED

for i=1 to 100 step 5
cells(i,"e").cut cells(i-2,"e")
next i

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Ed C" <econgleton@snet.net> wrote in message
news:_nqAc.173$sb6.50@newssvr32.news.prodigy.com...
> Can I get help in this newsgroup with recording macros?
>
> I have recorded the following macro using the macro recorder. The cursor
was
> 'in' cell E7
>
>     Selection.Cut
>     Range("E5").Select
>     ActiveSheet.Paste
>     Range("E10").Select
>
> I would now like to convert this to a general purpose macro. I need help
> using [R][C] notation.
>
> The steps are simple:
>
>
> For i = 1 to 100
> (1) Using the active cell, cut its value (a constant)
> (2) Move up 2 rows
> (3) Paste value
> (4) Move down 5 rows.
> Next i
>
> How do I change the recorded macro?
>
> Thanks for help.
>
> EdC
>
>
>
>


0
Don
6/18/2004 12:32:42 AM
Why does this work? Am I missing something? Where's the paste statement?

EdC
"Don Guillett" <donaldb@281.com> wrote in message
news:%235Q4DvMVEHA.2564@TK2MSFTNGP11.phx.gbl...
> something like this. UNTESTED
>
> for i=1 to 100 step 5
> cells(i,"e").cut cells(i-2,"e")
> next i
>
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Ed C" <econgleton@snet.net> wrote in message
> news:_nqAc.173$sb6.50@newssvr32.news.prodigy.com...
> > Can I get help in this newsgroup with recording macros?
> >
> > I have recorded the following macro using the macro recorder. The cursor
> was
> > 'in' cell E7
> >
> >     Selection.Cut
> >     Range("E5").Select
> >     ActiveSheet.Paste
> >     Range("E10").Select
> >
> > I would now like to convert this to a general purpose macro. I need help
> > using [R][C] notation.
> >
> > The steps are simple:
> >
> >
> > For i = 1 to 100
> > (1) Using the active cell, cut its value (a constant)
> > (2) Move up 2 rows
> > (3) Paste value
> > (4) Move down 5 rows.
> > Next i
> >
> > How do I change the recorded macro?
> >
> > Thanks for help.
> >
> > EdC
> >
> >
> >
> >
>
>


0
econgleton (13)
6/18/2004 2:05:33 AM
Something that may help you, as you record macros, is the Relative 
Reference button on the Stop Recording toolbar. When activated, it 
records movement, rather than specific cells.

If you have VBA help installed, you can select the word Cut in the code, 
and press the F1 key. This will open help, at the page with information 
about the Cut method. It explains that you can specify a destination for 
the cut cells, as Don did.

His code is designed to work in column E, so if you want this to work in 
the active column, you could add code to calculate that. For example:

'=================================
Sub CutValues()
Dim i As Integer
Dim r As Long
Dim col As Integer
r = ActiveCell.Row
col = ActiveCell.Column

If r < 3 Then
   MsgBox "Please select a cell in row 3 or higher"
   Exit Sub
End If

For i = 1 To 100
   Cells(r, col).Cut (Cells(r - 2, col))
   r = r + 5
Next i
End Sub
'===============================

Ed C wrote:
> Why does this work? Am I missing something? Where's the paste statement?
> 
> EdC
> "Don Guillett" <donaldb@281.com> wrote in message
> news:%235Q4DvMVEHA.2564@TK2MSFTNGP11.phx.gbl...
> 
>>something like this. UNTESTED
>>
>>for i=1 to 100 step 5
>>cells(i,"e").cut cells(i-2,"e")
>>next i
>>
>>-- 
>>Don Guillett
>>SalesAid Software
>>donaldb@281.com
>>"Ed C" <econgleton@snet.net> wrote in message
>>news:_nqAc.173$sb6.50@newssvr32.news.prodigy.com...
>>
>>>Can I get help in this newsgroup with recording macros?
>>>
>>>I have recorded the following macro using the macro recorder. The cursor
>>
>>was
>>
>>>'in' cell E7
>>>
>>>    Selection.Cut
>>>    Range("E5").Select
>>>    ActiveSheet.Paste
>>>    Range("E10").Select
>>>
>>>I would now like to convert this to a general purpose macro. I need help
>>>using [R][C] notation.
>>>
>>>The steps are simple:
>>>
>>>
>>>For i = 1 to 100
>>>(1) Using the active cell, cut its value (a constant)
>>>(2) Move up 2 rows
>>>(3) Paste value
>>>(4) Move down 5 rows.
>>>Next i
>>>
>>>How do I change the recorded macro?
>>>
>>>Thanks for help.
>>>
>>>EdC
>>>
>>>
>>>
>>>
>>
>>
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/18/2004 11:16:08 AM
Thank you very much, Debra!!!!

I looked all over for the way to record in relative reference. Used it in
Excel97 but couldn't find it in current XP version. Will look again.

I was totally disappointed with VBA help. Did just what you said; highlight
and F1. Worthless!!!! Didn't use cut but did try delete, selection, etc.
Didn't think cells would apply. Maybe I should be more patient but was in
hurry.

Thanks again,

EdC
(I'm too old to learn too many new tricks at the same time; 75 next week)


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:40D2CEF8.9070802@contexturesXSPAM.com...
> Something that may help you, as you record macros, is the Relative
> Reference button on the Stop Recording toolbar. When activated, it
> records movement, rather than specific cells.
>
> If you have VBA help installed, you can select the word Cut in the code,
> and press the F1 key. This will open help, at the page with information
> about the Cut method. It explains that you can specify a destination for
> the cut cells, as Don did.
>
> His code is designed to work in column E, so if you want this to work in
> the active column, you could add code to calculate that. For example:
>
> '=================================
> Sub CutValues()
> Dim i As Integer
> Dim r As Long
> Dim col As Integer
> r = ActiveCell.Row
> col = ActiveCell.Column
>
> If r < 3 Then
>    MsgBox "Please select a cell in row 3 or higher"
>    Exit Sub
> End If
>
> For i = 1 To 100
>    Cells(r, col).Cut (Cells(r - 2, col))
>    r = r + 5
> Next i
> End Sub
> '===============================
>
> Ed C wrote:
> > Why does this work? Am I missing something? Where's the paste statement?
> >
> > EdC
> > "Don Guillett" <donaldb@281.com> wrote in message
> > news:%235Q4DvMVEHA.2564@TK2MSFTNGP11.phx.gbl...
> >
> >>something like this. UNTESTED
> >>
> >>for i=1 to 100 step 5
> >>cells(i,"e").cut cells(i-2,"e")
> >>next i
> >>
> >>-- 
> >>Don Guillett
> >>SalesAid Software
> >>donaldb@281.com
> >>"Ed C" <econgleton@snet.net> wrote in message
> >>news:_nqAc.173$sb6.50@newssvr32.news.prodigy.com...
> >>
> >>>Can I get help in this newsgroup with recording macros?
> >>>
> >>>I have recorded the following macro using the macro recorder. The
cursor
> >>
> >>was
> >>
> >>>'in' cell E7
> >>>
> >>>    Selection.Cut
> >>>    Range("E5").Select
> >>>    ActiveSheet.Paste
> >>>    Range("E10").Select
> >>>
> >>>I would now like to convert this to a general purpose macro. I need
help
> >>>using [R][C] notation.
> >>>
> >>>The steps are simple:
> >>>
> >>>
> >>>For i = 1 to 100
> >>>(1) Using the active cell, cut its value (a constant)
> >>>(2) Move up 2 rows
> >>>(3) Paste value
> >>>(4) Move down 5 rows.
> >>>Next i
> >>>
> >>>How do I change the recorded macro?
> >>>
> >>>Thanks for help.
> >>>
> >>>EdC
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
econgleton (13)
6/18/2004 7:38:55 PM
Hi Ed,

Sometimes VBA Help isn't too helpful. It's a great resource if you know 
what you're looking for, and need to find the details on how something 
works. But, if you're not sure where to start, or what key words to 
enter, it can be a daunting (or hopeless!) task.

And happy (almost) birthday! Keep learning those new tricks, a few at a 
time.

Debra

Ed C wrote:
> Thank you very much, Debra!!!!
> 
> I looked all over for the way to record in relative reference. Used it in
> Excel97 but couldn't find it in current XP version. Will look again.
> 
> I was totally disappointed with VBA help. Did just what you said; highlight
> and F1. Worthless!!!! Didn't use cut but did try delete, selection, etc.
> Didn't think cells would apply. Maybe I should be more patient but was in
> hurry.
> 
> Thanks again,
> 
> EdC
> (I'm too old to learn too many new tricks at the same time; 75 next week)
> 
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:40D2CEF8.9070802@contexturesXSPAM.com...
> 
>>Something that may help you, as you record macros, is the Relative
>>Reference button on the Stop Recording toolbar. When activated, it
>>records movement, rather than specific cells.
>>
>>If you have VBA help installed, you can select the word Cut in the code,
>>and press the F1 key. This will open help, at the page with information
>>about the Cut method. It explains that you can specify a destination for
>>the cut cells, as Don did.
>>
>>His code is designed to work in column E, so if you want this to work in
>>the active column, you could add code to calculate that. For example:
>>
>>'=================================
>>Sub CutValues()
>>Dim i As Integer
>>Dim r As Long
>>Dim col As Integer
>>r = ActiveCell.Row
>>col = ActiveCell.Column
>>
>>If r < 3 Then
>>   MsgBox "Please select a cell in row 3 or higher"
>>   Exit Sub
>>End If
>>
>>For i = 1 To 100
>>   Cells(r, col).Cut (Cells(r - 2, col))
>>   r = r + 5
>>Next i
>>End Sub
>>'===============================
>>
>>Ed C wrote:
>>
>>>Why does this work? Am I missing something? Where's the paste statement?
>>>
>>>EdC
>>>"Don Guillett" <donaldb@281.com> wrote in message
>>>news:%235Q4DvMVEHA.2564@TK2MSFTNGP11.phx.gbl...
>>>
>>>
>>>>something like this. UNTESTED
>>>>
>>>>for i=1 to 100 step 5
>>>>cells(i,"e").cut cells(i-2,"e")
>>>>next i
>>>>
>>>>-- 
>>>>Don Guillett
>>>>SalesAid Software
>>>>donaldb@281.com
>>>>"Ed C" <econgleton@snet.net> wrote in message
>>>>news:_nqAc.173$sb6.50@newssvr32.news.prodigy.com...
>>>>
>>>>
>>>>>Can I get help in this newsgroup with recording macros?
>>>>>
>>>>>I have recorded the following macro using the macro recorder. The
>>>>
> cursor
> 
>>>>was
>>>>
>>>>
>>>>>'in' cell E7
>>>>>
>>>>>   Selection.Cut
>>>>>   Range("E5").Select
>>>>>   ActiveSheet.Paste
>>>>>   Range("E10").Select
>>>>>
>>>>>I would now like to convert this to a general purpose macro. I need
>>>>
> help
> 
>>>>>using [R][C] notation.
>>>>>
>>>>>The steps are simple:
>>>>>
>>>>>
>>>>>For i = 1 to 100
>>>>>(1) Using the active cell, cut its value (a constant)
>>>>>(2) Move up 2 rows
>>>>>(3) Paste value
>>>>>(4) Move down 5 rows.
>>>>>Next i
>>>>>
>>>>>How do I change the recorded macro?
>>>>>
>>>>>Thanks for help.
>>>>>
>>>>>EdC
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/18/2004 8:12:37 PM
Reply:

Similar Artilces:

Need Unprotect/Protect for Excel Macro
Hello there, I need the code to put into a macro to go in and unprotect a sheet (just long enough for me to insert some data)...then re-protect the sheet before saving. This way....the user cannot destroy the data or formula in teh protected sheet. How do I do this? Is there a global command or do I have to imput my password when I ask it to follow me recording the macro. Please help! Anyone!! Thanks!! Wade Previous post of J E McGimpseys will give you the syntax Sub Toggleprotect2() Const PWORD As String = "ken" Dim wkSht As Worksheet For Each sh ...

How do I add a record counter to my form
I would like to add my own record counter, like "Record # of #", to my form. How can I do this? Private Sub Form_Load() DoCmd.GoToRecord , , acNext DoCmd.GoToRecord , , acFirst End Sub Private Sub Form_Current() Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records" End Sub This code places the info in the form's title area. To place it in a Label, substitute Me.LableName.Caption for Me.Caption. To place it in a TextBox, substitute Me.TextBoxName.Va...

Record recording
I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php?action=ge...

Start Up Macro
How do I create a macro that runs automatically when the spreadsheet i opened -- colinfraser@prosealuk.co ----------------------------------------------------------------------- colinfraser@prosealuk.com's Profile: http://www.officehelp.in/member.php?userid=523 View this thread: http://www.officehelp.in/showthread.php?t=126905 Posted from - http://www.officehelp.i right click the excel icon in the upper left next to file>select workbook open> put it in there -- Don Guillett SalesAid Software dguillett1@austin.rr.com "colinfraser@prosealuk.com" <colinfraserprosea...

VBA MACRO Emergency!!!!
My spreadsheet has hundreds of rows with blank rows in between. The only way I know that I am at the end of the data is to find 20 consecutive blank rows. Example below. I need to evaluate row, if this is not the last row then proceed with my other logic so forth and so on. If it is the last row, I need to return to A7 and proceed with my other logic. A 1.XXXXXXXX 2. 3.XXXXXXXX 4.XXXXXXXX 5.XXXXXXXX 6. 7. 8. 9.XXXXXXXX 10.XXXXXXXX 11-20 are blank Thanks in advance OS The easiest way to find your end of data is : LastRow = [A65000].End(xlUp).Row Regards. Daniel > My spread...

excel macro mail send
Hello, I'm using this vb macro code for sending mail. --------------------------------------------------------------- Sub mailto_Selection() Dim Email As String, Subj As String, cell As Range Dim response As Variant Dim msg As String, url As String Email = "" 'create list below Subj = "Family Newsletter" msg = "Here needs to be named range from excel(some text)" '-- Create the URL For Each cell In Selection Email = Email & cell.Text & "; " Next cell url = "mailto:" &...

Help with sql which counts records
Could someone help me to extend this sql to include: 1. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 2. a count of txtmulti where the field is a YES/NO field and I want a count of where the answer is YES 3. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 4. a count of txtnbrparts where the field is a number field and I want a count of where the answer greater than 1 I think I need to extend the WHERE statement? SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt...

Selecting specific records
Is it possible to create a form with a "select" field (yes/no box), to select several items (say employees) and then output this information to a report with only the fields selected? -- Lori A. Pong How about multi-select list box? HTH -- -Larry- -- "Lori" <lpong@sternconstruction.com> wrote in message news:CAE5922D-92E5-4A01-83B2-6484024725C5@microsoft.com... > Is it possible to create a form with a "select" field (yes/no box), to select > several items (say employees) and then output this information to a report > with only the fields sele...

Multiple records in table to display single records
My table has 3 columns, A is account numbers, B is first name, C is last name. If an account has 2 owners, the account number is listed twice in C1, like this: A B C 123 Hank Jones 123 Ann Jones 231 Mike Smith 321 Jim John I want to query this table to display the names side by side if there is more than one owner, so that my data appears like this: A B C D E 123 Hal Jones Ann Jones 231 Tim Smith 321 Jim John Help please! Sorry, got the names mixed up! "Hurrikane4" wrote: > My table has 3 columns, A is account numb...

How do I trigger an event on opening a record
I have an Access 2003 form where I would like to take an action (e.g. via an event procedure or macro) whwnever the user opens a record for editing (e.g. via the arrowed record selector buttons). Effectively what I want is something like an On Enter or On Open control but relating to a whole record. It seems that such a control is only available for (e.g.) a text box, which relates to a single field in a record. I thought there would be something in the Detail section of the form as I thought that section related to whole records (?) but there isn't such a control there. Any idea...

Macro Keys no functioning via Keystroke
I have set up a number of marcos assigned to Function keys, but though they function fine when triggering them with the mouse, you can't use them via a Keystroke. ie Alt F11. Does anyone have any suggestions? were they created with a mouse? try creating them with the keyboard only. "Tom" <ttaylor@cherrybrook.com> wrote in message news:563701c5258c$4e5cf050$a501280a@phx.gbl... > I have set up a number of marcos assigned to Function > keys, but though they function fine when triggering them > with the mouse, you can't use them via a Keystroke. ie Alt >...

Object Source / Record Source
Hello I have created a database regarding the School System. I have created three forms (one form and two subforms) one main form is (Students) where i enter student's name, father's name, phone, address etc. now on the main Student form i want to place a subform. But the thing i want to know is: I want to make two buttons on my main form (Fees) and (Progress).. because i have two subforms (sbffees and sbfprogress) now the record source of subformbox should be controled with these two buttons. when i click the ( Fee) button the Source Object should be (sbf-fee) and when i c...

Outlook client 'View in CRM' does not open the record
My 'View in CRM' button is not opening the CRM record. Pop-up blocker is turned off. Any other ideas? Thank you very much! Suzy ...

Inventory Reconcile Macro
Does anyone have a macro already recorded to enter dynamics and run and inventoroy reconcile (including the manufacturing module where it reconiles picklists.)? If so would they like to share it? If I have to I will come in after hours to record but would like to avoid this if possible. thanks -- Doug You'll need a tool called auto-dim to make this work. It's available from support. That will allow you to schedule a job that will launch GP, run a macro and then exit unattended. I'm not aware of any limitations on the macro(s) that can be run. -- Jim@TurboChef &quo...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

MCI recording silence, but Sound Recorder records OK.
Hello everybody, This code using MCI is working fine in many PCs, but one (in which only a long silence is recorded): // // OPEN // sMsg.Format("open new type waveaudio alias %s", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("status %s mode wait", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s format tag PCM", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s channels 1", m_sWavName); mciE...

Control the number of records shown in a report/subreport.
I've posted this question before and when I follow the advice, I keep getting a circular reference error. So I'm hoping that if I post some more info, I may get insight as to what is going wrong. I have a report (BartS1report) it uses a query (BartS1Report). This report as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists the customer name, address, and systems serviced. The subreport lists the systems serviced. The reports are connected through the Master/Child links Customer ID and the ServiceAddress. When there are more than 20 systems per ...

New Record
I have a form/subform. I have an event that opens a popup. I select from a list on the popup that copies the value onto a txtbox on the Main form and the popup then automatically closes. In the same code I place the focus in the subform to begin adding data to a new record. The problem I 'm having is that when the focus is set in the sub, there are two lines. The top line, which is the one I'm entering data in, does not automatically put the autonumber in for the ID that links the tables, but the record below it does, which is one that I'm not using. When I fill in the...

What determines the order in which records are viewed
Hi all What determines the order in which records are viewed in a table If I import data or create a new table from a query regards, Garry ...

Hide path in macro warning box
hey guys! Is there a way to hide the path to my file when the macro warning bo pops up? Thanks! : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com I don't think so, but maybe you could mask it. Provide two workbooks. The first workbook's sole purpose is to open the "real" one. But put the first workbook somewhere that you can publicize. The user will get see that drive/folder. And if they allow macros to be run, then the other will open with no veri...

Database Deleting Record Problem
Just launched a new database. Using an Autonumber field to generate document numbers (sequence is not important - just need a unique identifier). There are a number of fields that are required fields based on an option group selection. Problem; If all required fields (they are required fields programmatically) are not completed and you attempt to exit the form, a message box appears asking " Do you want to save changes". If you say "Yes" the form closes, does not save, and the record is deleted - but users are not aware of this deletion - and they think the recor...

Recording last modification of records
I have found out how to record the date and time of the last modification to a record, which is great. Can I also record the user that made the changes? If so,how? Something like this can give you the network login name. http://www.mvps.org/access/api/api0008.htm -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "splitz" wrote: > I have found out how to record the date and time of the last modification to > a record, which is great. Can I also record the user that made the changes? > If so,how? ...

Report Not Showing All Records
Dear All, I Have a Report that Shows all the records in a Table, at the moment the table hold 200 Records. but the report which is related to the table, is only displaying 32 pages, with the one record on the last page. the report is only showing 160 Records, What would cause the report to exclude the other 40 Records? Theres no filters set or anything. Any one got any suggestions? Regards Dan ...

Button to go to a specific record in a form
Hello, I have two forms. I need to use a command button to go to a second form's specific record. a. In the wizard, when it is time to chose the fields to match, there is nothing in the left side of the screen. b. How do I do this in code. Thnaks for all of your help. Joe In the Click event of the command button, use the primary key field of the record you want to open in the second form in the OpenForm Where argument. This is a good example in VBA Help. -- Dave Hargis, Microsoft Access MVP "Joe C" wrote: > Hello, > > I have two forms. I need to use a c...

me too, problem with import... only 15 records
me too, problem with import... only 15 records.... also in one of my customers crm i need a fast answer.... I dont see a question to answer...? JUAN MARKETING wrote: > me too, problem with import... only 15 records.... also in one of my > customers crm > > i need a fast answer.... Hehe yeah, what is the question!? what are you trying to import? Sorry, I don't know the reason, bu each time i try to import x number of accounts or contacts, MSCRM only import 15 records... Promise, everything is ok... columns, format csv from excel, size, records.... I say that, becau...