Modify Recorded Macro

I recorded the following macro, which basically Merges 2 cells and Alignes 
the text to the left.

However, when recording it always records the cell 
address(Range("D***:D***").Select.

How can i adapt the cell address to something like:

ActiveCell - ActiveCell.Offset(0,1).select instead of the particular cell 
address.

I need to run this macro about 1000 times

*************************
Sub MergeCells()
'
' MergeCells Macro
'

'
    Range("D156:E156").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("D156:E156").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("D157:E157").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("D158:E158").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub
********************


Corey.... 


0
Corey
11/22/2009 10:51:40 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
782 Views

Similar Articles

[PageSpeed] 28

Your code looks like it's really going down the D:E column--not across the row.

ActiveCell.Offset(0,1).select  
would/should have been: 
ActiveCell.Offset(1,0).select
Right???

If yes:

Option Explicit
Sub testme()

    Dim wks As Worksheet
    
    Set wks = Worksheets("Sheet1")
    
    With wks
        With .Range("D156").Resize(1000, 2)  '1000 rows by 2 columns
            .Merge across:=True   'each row is merged separately            
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = True
            .ReadingOrder = xlContext
        End With
    End With

End Sub

Notice that I did drop the ".mergecells = false" and ".mergecells = true" lines.


Corey wrote:
> 
> I recorded the following macro, which basically Merges 2 cells and Alignes
> the text to the left.
> 
> However, when recording it always records the cell
> address(Range("D***:D***").Select.
> 
> How can i adapt the cell address to something like:
> 
> ActiveCell - ActiveCell.Offset(0,1).select instead of the particular cell
> address.
> 
> I need to run this macro about 1000 times
> 
> *************************
> Sub MergeCells()
> '
> ' MergeCells Macro
> '
> 
> '
>     Range("D156:E156").Select
>     With Selection
>         .HorizontalAlignment = xlCenter
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = False
>     End With
>     Selection.Merge
>     Range("D156:E156").Select
>     With Selection
>         .HorizontalAlignment = xlLeft
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = True
>     End With
>     Range("D157:E157").Select
>     With Selection
>         .HorizontalAlignment = xlCenter
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = False
>     End With
>     Selection.Merge
>     With Selection
>         .HorizontalAlignment = xlLeft
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = True
>     End With
>     Range("D158:E158").Select
>     With Selection
>         .HorizontalAlignment = xlCenter
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = False
>     End With
>     Selection.Merge
>     With Selection
>         .HorizontalAlignment = xlLeft
>         .VerticalAlignment = xlCenter
>         .WrapText = False
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = True
>         .ReadingOrder = xlContext
>         .MergeCells = True
>     End With
> End Sub
> ********************
> 
> Corey....

-- 

Dave Peterson
0
Dave
11/22/2009 11:17:34 PM
Reply:

Similar Artilces:

Notify user macros are disabled.
I want to try and notify the user that macros are disabled. The problem comes when the user overlooks the Security warning bar at the top of Excel 2007 and doesn't enable the marcros as needed. I can't seem to figure out the last details. Our IT requires that we have security set to disable macros with notification. I'm still investigating getting IT to set the security to trust macros in a defined location for our use. My method of warning the user is to have a cell on the worksheets say macros are disabled and to clear the message when the file is opened and se...

a way to amplify the recordings i make
Is it possible to make the volume of the recordings louder? I know i can output the recordings and amplify it as much as i want using an external 3rd party program, the question is will it be synced with my writings during the recording ? (assuming it was synced before the editing...). If not, is it possible to amplify it at the same time it records somehow? except from defining it in windows because i already did so... The reason I ask is because my lecture recordings are quite quiet and i can barely hear what was said, using an external usb microphone... matan ofer wrote...

EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...

Modifier Error
Whenever we try to go to modifier it gets the following error. The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary. The Server has already been rebooted but we are still coming up with the same error. Thanks, Hi make sure that you are not sharing the Forms Dictionary with another users. Thanks BS "Jess M." <JessM@discussions.microsoft.com> wrote in message news:C56C6878-9410-4CF5-95DE-DCA0AC50EA55@microsoft.com... > Whenever we try to go to modifier it gets the following error. > > The Modifier is current...

Problem to modify the size of PlotArea
Hello, I am trying to modify a chart so that it prints in a 20cm x 17,5cm frame. I do understand that this is governed by the plotarea.insideheight and plotarea.insidewidth properties. I also do understant that these properties are read-only. So far my code is the following: With ActiveChart .SizeWithWindow = False .PageSetup.ChartSize = xlScreenSize .PlotArea.Width = 567 .PlotArea.Height = 496 Do While .PlotArea.InsideWidth < 567 .PlotArea.Width = .Pl...

Last record in detail
-- Taylor ?! Regards Jeff Boyce Microsoft Office/Access MVP "TaylorLeigh" <TaylorLeigh@discussions.microsoft.com> wrote in message news:34186B73-4DF3-4680-9F12-73A84F2D62D7@microsoft.com... > > -- > Taylor ...

modified timeline graph
I have a table of dates and events (and "phase"), and I want to make a timeline graph: 1-d horizontal line, no (visible) y-axis. I want the distance between the events to be spaced based on the date, and the event names to be shown at each point. The dates should be shown too, either for each event or just based on auto-scaling of the x-axis (e.g. major unit = 3 months). Here's the hard part. The events happen in different "phases", and I want to show the phases graphically somehow. I was thinking this component of the graph would be a bar chart, and I could someho...

Macro in Template needs to go to XLS
I use Docmd.OutputTo in Access2003 to send the data from a report to Excel. I tell it to use a Template which has 1 macro assigned to it. The OutputTo function saves it as a XLS file but the macro doesn't make it to the new file. Why, and is there something that will pass the macro from the template to the XLS???? Thanks, Rick Rick I've used the DoCmd.TransferSpreadsheet method to write data from a query to a spreadsheet that has an AutoOpen macro on it. This method seems to work fine and the macro runs. Not certain if that's exactly what you're looking for or not, but may...

Modify name displayed in GAL
Hi all! Is there any way to change the manner in wich user are displayed in GAL? I've been aked to set DislayName to "First Last", but leave "Last First" in GAL - is there any way to do this? Thanx -- R.V. ...

modify online services
My bank recently upgraded their online services to include Direct Statements. Previously I had set up to receive Web statements. Money 2004 Omline services Manager does not give me the option to "modify services" only to "change financial institutions." Any ideas on how I can modify online banking services so I can use background banking to get Direct statements? This is how it works in M03: From account list -> Click manage online services -> click Setup online services, then select the financial institution from the list (assuming the list is updated wi...

Outlook Macro
I need to set a large number of Outlook calendar entires to: Show Time as: Free, and Sensitivity: Private I am having difficulty working out how to write a macro to do this. can anyone help please? Thanks V ...

Potential duplicate records: None
When we run the duplicate detection wizard and view the results, there are several records that do not have duplicates. They come up with the message: "Potential duplicate records: None". Does anybody know why this happens? I have looked all over and can't find a reason behind it. I would like to know the anwser to this too. We have identical problems. "Taylor" wrote: > When we run the duplicate detection wizard and view the results, there are > several records that do not have duplicates. They come up with the message: > "Potential duplica...

One Record at a time Report
I have a table with columns that represent different metrics on separate investments. Each investment is a separate row of the table. Is it possible to have a Report based on the table that displays/prints each row as a separate report? Thanks! (B^>)-]=[ WSR wrote: >I have a table with columns that represent different metrics on separate >investments. > >Each investment is a separate row of the table. > >Is it possible to have a Report based on the table that displays/prints each >row as a separate report? Well, lots ot things are ...

GP7.5SP6
We're in the process of upgrading from GP7.5 SP4 to SP6. We are on MSSQL Server 7.0. My supervisor has already implemented the hot fix as describe in MBS (that's a script to update some indexes on the server if someone is using GP7.5 manufacturing on MSSQL7). When I try to import modified forms and reports I've received some errors. For instances some references on the VBA to the GUI forms (i.e. text boxes, buttons) become missing. And on the reports I've been told it couldn't find the table MOP_MO_Variance_DC_Detail_Report_Temp and MOP-Sched_Interface_Report_Before....

Search / Modify / Delete . . .
In �sheet1� I have a database with theses headers �Name, Phone, Address and below are the records rows 2-150. In �sheet2� I have the data entr� form (not VBA) its on the sheet, that has a macro that writes t �Sheet1� (for storage). How can implement a search for a record and return the result t �sheet2�, with the ability to modify or delete from the databas (�sheet1�). Thanking you in advance -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218 View this thread: http://www.ex...

Macros to Browse for Files
I am trying to summarise information held in various excel files. I want to browse for a file by clicking an object 'button' - th various pieces of information held in the file I've selected are to b summarised in the sheet. Ultimately, I am trying to collate information from various excel file and summarise them in a new file. Can this be done? E.G I have twenty different excel files. Each of the files is setu the same way so that the "Bill total" is in the same cell. So, th twenty files all have $10 as a total - the summary file (which I a trying to create) would...

SPF record question
Hello! I think I understand SPF records now (thanks to Jim Martin in another group), but I would like to know if anyone can confirm my questions. I found this page http://www.kitterman.com/spf/validate.html that lets one test an SPF record. It does not test version 2 yet. Let's call my domain "mydomain.net" and assume that my mail server is named "mail.mydomain.net." I have my DNS controlled by www.zoneedit.com and I have a backup mail server there to grab any mail if my server is not answering. When I use www.dnsreport.com to check my domain, it shows two MX re...

Protect Macros
Hi How can I protect macros for a workbook without protecting the workbook/worksheet? Thanks Hi Open the VB editor. Undert Tools menu there's a "project properties" item and there you'll find a "Protection" window. HTH. Best wishes Harald "nc" <anonymous@discussions.microsoft.com> skrev i melding news:046201c52f92$6d31ad30$a401280a@phx.gbl... > Hi > > How can I protect macros for a workbook without > protecting the workbook/worksheet? > > Thanks Thanks Harald >-----Original Message----- >Hi > >Open the VB edi...

Modified By and Modified On fields
I am trying to customize a phone form (will need to do the same to most others) by adding Modified By and Modified On fields. After publishing the form, the fields appear on the form just fine but the lookup to a user list is greyed out. Any ideas? Am I doing something wrong? I found the answer myself. Those are system-generated values and. therefore, cannot be modified by a user "mkatsev" wrote: > I am trying to customize a phone form (will need to do the same to most > others) by adding Modified By and Modified On fields. After publishing the > form, the fields ap...

clear records on subform when go to knew record on main form
I am using the unbound combo fields on the main form (date, joblist, personnel) to populate and schedule fields in a subform when I navigate to a new record (new day) on the main form. I would like the subform records to be cleared out for the new record but remain for the day that has already been scheduled. What code and where do I put it. thanks, barb It should be automatic (no code) if your main form and subform are set up properly. Definitions that will make my explanation easier... just sub in your own names: FormMain - Your main form TableMain - The table or recordset und...

Macro #23
Hi - I am using Excel 2003. I have a column that I enter #'s into everyday. Mold 559 2 3 0 etc. Mold 2 0 5 etc. Sometimes there are no #'s to enter next to the specific catefogy (Mold) and then I have to enter just 0's. I would like to set up a macro so I can activate a button on my toolbar and have just 0's fill in the cells. I have tried to set up a column of the correct # of 0's and then do a macro just to paste, but the syntax is wrong. Can anyone help. Thank You Hi, I'm not sure I understand...

User to decide how often a macro runs
Can anyone help...Please.... I need a macro that asks for the user to input the number of times it needs to run. Thanks for any help in advance Try something like the following: Dim NumRuns As Long Dim Ndx As Long NumRuns = Application.InputBox(prompt:="Enter number of times.", _ Type:=1) For Ndx = 1 To NumRuns ' run your macro code here Next Ndx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Deedee" <Deedee@discussions.microsoft.com> wrote in message news:1BF76D95-9EEB-4A8B-A23C-A38A19AA3CAB@...

Modifying the Sales Receipt
We are using RMS, and our receipts print out on a simple Star impact printer. Two things would be nice. It would be nice to know, generally speaking, how to modify what prints on the receipt. More specifically, when we sell items at a discount, it shows the discount and it shows the discounted price, but it does NOT, for some reason, show the original price. I would like to be able to show the original price at least, and if possible, calculate the amount saved. Our prior system, FoxPro based Biztracker, did that as standard. All suggestions greatly appreciated. -- Bud Izen In the...

How to get date entered recording first time condition is true?
Would like a date entered in a column the first time a condition is met calculated on several other columns in the same row. How could this be implemented? You could modify the circular reference formula at http://www.mcgimpsey.com/excel/timestamp.html to substitute your conditional statement for A1="". Or you could use something like the event macro shown there, but using the Calculation event instead, for instance: Private Sub Worksheet_Calculate() Dim rCheck As Range Dim rCell As Range On Error Resume Next Set rCheck = Range("A...

CNAME resource records for Site is incorrect
I'm running the SBS 2008 BPA and I keep getting this error. >>> The CNAME resource record for the Sites alias should point to the fully qualified domain name of the computer that is running Windows SBS 2008. <<< This is what I have in DNS Manager: server > domain.local Sites - Alias(CNAME) - server.domain.local. - static Any suggestions on how to fix this is greatly appreciated. Thanks! "Shibin" <shibin@txctech.com> wrote in message news:OD6GlZ3nKHA.1928@TK2MSFTNGP05.phx.gbl... > I'm running the SBS 2008 BPA and I keep gett...