Solved

Select adjacent cell and merge in Excel using vba from Access

Posted on 2014-09-24
7
490 Views
Last Modified: 2014-10-02
I have some code written in Access that I am using to transfer information from the Access database into an Excel spreadsheet and format the spreadsheet.

I have the information moving just fine and I now know how to do some of the formatting. Now I need to be able to take the cell I have selected, find the cell next to it, and then merge the two.

I can't figure out how to do this. I have tried
ActiveCell.Offset(0, 1).Select

Open in new window

but this doesn't seem to do anything. Once I have managed to select the second cell, I am not entirely sure how to merge the two. I think the code below is what I would use:
ActiveSheet.Range(Cells).Merge  

Open in new window


But I am not sure of that either, since I am just trying to get the cell selected at this point.

Here is the code I am working with:
With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
For Each C In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
    If C.Value = "Words in the cell" Then
    With C
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12

Open in new window



I know I am using the heck out of this membership and I am really grateful for all of the help everyone here has given me!
0
Comment
Question by:Megin
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40343043
I think you're far better off creating a query in Access that merges those values for you:
[Data1] & " " & [Data2] AS [Data3]

Then you output the query into Excel rather than the raw table.
0
 

Author Comment

by:Megin
ID: 40344190
I am not trying to concatenate the information in two difference cells. This is wholly a matter of formatting. My boss wants this spreadsheet to look a certain way, and that way includes certain cells being merged to make them bigger.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40344307
Gotcha, I wasn't thinking about Excel's "merge". First you'll need to concatenate the data from the 2nd cell into the 1st cell and then use the merge function. If you don't concat first, the data in the 2nd cell will be wiped out. Something like these:

ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)

Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 1)).Merge

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Megin
ID: 40344375
I don't have any data in the second cell, so I am not worried about that.

When I tried this code, it merged with the cell to the right and the cells below.

I need it take the specific cell I am formatting in that code (the one with the "Words in the cell" in it) and then merge that cell with the one next to it on the right.

I was thinking that, in order to do that, I would just have to change the numbers in the parenthesis, but when I changed them, nothing happened.
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40345055
Hm, that should work. The first number is the row offset and the second is the column offset, which is what you want:
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 1)).Merge

Open in new window

Or if you have the cell identifiers:
Range("D7:E7").Select
Selection.Merge

Open in new window

0
 

Author Closing Comment

by:Megin
ID: 40357768
Sorry it took me so long to get back to you about this.

I figured out how to identify the active cell, which was in the way of using this solution. Now I have it and it works!

Here is what it looks like, with C defined as the cell I am working from:

Range(C, C.Offset(0, 1)).Merge


THANK YOU SO MUCH!!!!!
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40358251
You're so welcome! But yeah, I still find VBA tricky sometimes.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question