Solved

Select adjacent cell and merge in Excel using vba from Access

Posted on 2014-09-24
7
428 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

743 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

11 Experts available now in Live!

Get 1:1 Help Now