Solved

Select adjacent cell and merge in Excel using vba from Access

Posted on 2014-09-24
7
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

626 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