Solved

Select adjacent cell and merge in Excel using vba from Access

Posted on 2014-09-24
7
444 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2007 Count Unique Values in a Group 5 33
count unique records in access report 2 23
Auto Filter in Combo Box 7 31
Search Form not Querying 2 10
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now