Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

Select adjacent cell and merge in Excel using vba from Access

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
Megin
Asked:
Megin
  • 4
  • 3
1 Solution
 
Russell FoxDatabase DeveloperCommented:
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
 
MeginAuthor Commented:
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
 
Russell FoxDatabase DeveloperCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
MeginAuthor Commented:
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
 
Russell FoxDatabase DeveloperCommented:
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
 
MeginAuthor Commented:
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
 
Russell FoxDatabase DeveloperCommented:
You're so welcome! But yeah, I still find VBA tricky sometimes.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now