PDA

View Full Version : [SOLVED] Cannot show merged cells



lamdt
11-16-2019, 06:35 AM
Hi Guys,
I tried to modify the excel file has been published by Vijian25408 which use excel as database and can easy to filter information of Data sheet on view sheet using Update drop down then search something in box and click on show data to get the result.
I got issue when the data in columns named PO, Quotation, VAT invoice are merged as below:
25409
When i click Update drop down on View sheet then the error Type mismatch will appear. I know that now the function of Show Data cannot get the Cells that not have data. Could someone help me to change the code to permit show data can work even the cells have been merged ?

SamT
11-16-2019, 07:21 AM
Only rank amateurs and extremely experienced Pros dare to use Merged Cells.
Since an experienced pro would not have this problem, all I can say is "Don't use Merged Cells!"

lamdt
11-16-2019, 07:28 AM
Hi SamT,
Thank you for your information.
My employees send weekly report to me which they have been merged information at PO -Quotation - VAT invoice cause it easy to them to follow their daily work and i do not want to request them to change the way they made report file. Is there any way to take data from data sheet with merge cells information or could you guide me how to un-merged cells when click on Update Drop down ? I think about this logic:
1. When click on update - check data
2. If data > 0 then do as code.
3. If data =0 (merge cell) then un-merge, copy information form merge cell and paste to others.
4. Show data
is it possible?
: pray2:: pray2:: pray2:

SamT
11-16-2019, 08:51 PM
VBA code: Sheets("Sheet1").Cells.Unmerge

Do it manually. It only takes a few seconds.

lamdt
11-16-2019, 09:53 PM
The problem is that the real data file is quite big (about 10000+ lines) and we keep update everyday. If VBA can do as i think then everyone just insert their report to the shared data file without check and unmerge all merged cells.

Paul_Hossler
11-16-2019, 10:16 PM
Only rank amateurs and extremely experienced Pros dare to use Merged Cells.
Since an experienced pro would not have this problem, all I can say is "Don't use Merged Cells!"

Bit harsh

Merged cells do have their place if used correctly and carefully

It's easy enough to unmerge them and to enter the 'merged value' into each of the cells to normalize (standardize?? fix??) the table

Downside is that it might add a little to the run time



Option Explicit


Sub FindMergedCells()
Dim r As Range, r1 As Range


Application.ScreenUpdating = False

For Each r In ActiveSheet.UsedRange
If r.MergeCells Then
Set r1 = r.MergeArea
r.UnMerge
r1.Value = r.Value
End If
Next


Application.ScreenUpdating = True


End Sub

SamT
11-17-2019, 01:28 AM
@ lamdt

then everyone just insert their report to the shared data file without check and unmerge all merged cells.

We will need to see the code you use to insert the new data into the shared data file

Me may have to use my snippet above and unmerge the entire sheet, or we may be able to use Paul's method on just the new data.

lamdt
11-17-2019, 01:30 AM
Hi Paul,
I tried to make the Button and add your code there but it does not work.
I tried this file to add Unmerged cells to the Unmerged Merge button it work with row 3-26 but did not work with other, could you guys check the code and guide me what is problem here ?
25411
Thank you so much.

lamdt
11-17-2019, 01:33 AM
Hi SamT,
I copy new data from report file to data base manually.

SamT
11-17-2019, 01:59 AM
VBA code: Sheets("Sheet1").Cells.Unmerge

Do it manually. It only takes a few seconds.

---------------------------------------------------------------------------
Sir or Ma'am,
We are not watching you over your shoulder. We need much bore specific information than "I do it manually."

lamdt
11-17-2019, 04:19 AM
Hi SamT,
Not only one employee send weekly report to me but a team. I know that i can do it manually without problem but i think VBA can do it for me, am i right ?

SamT
11-17-2019, 04:37 AM
VBA can do it for me, am i right ?Probably yes.

But we still need to know very specific details how you get data and enter data.
Step be step details; For example:


Open Workbooks named ...
Open Sheet named ...
Select Range A1:Z99
Copy Range A1:Z99
Open Workbook Sale-Database
Open sheet Data
Select Cell A1
Paste data copied from sheet... Range A1:Z99


We will also need to know what cells are merged. Are they always the same cells?. On what sheet?

lamdt
11-17-2019, 07:03 AM
Hi SamT,
Thank you for your help.
My employees they manage the Sale database by themselves which have 03 important information are: PO , Quotation, VAT Invoice. Normally, data in these columns always merged for easy view. They send file to me as the report weekly and i want to build a sale database which contain date of all sale members. With View sheet i provide them 03 filter information related to PO, Quotation and VAT invoice, they can search and get information of each other.
Normally, after they send file to me, i will do the manually copy all their information and put on data sheet (i just think about make separate sheet for each members but do not know how to search data from multi-sheet), the procedure of copy data is that:
1. Open workbooks named: Sale report W45, W46 ...
2. Open Sheet named: Hien, Oanh, Anh, Thuy ...
3. Select Range: A3:LX (based on each file X maybe 20 or 50)
4. Open workbook Sale-Database
5. Select cell Ax (x is current ending row of last week)
6. Paste data copied above.

By the way, could you guide me how to filter data of multi-sheet, i mean:
1. Click Update drop down (check data of sheet named: Hien, Oanh, Anh, Thuy not only Data)
2. Insert filter information.
3. Click show data-> the result will have information take from all sheets named: Hien, Oanh, Anh, Thuy.
if i can do that then i do not need to copy report of members to data sheet as currently :doh::doh:

SamT
11-17-2019, 09:12 AM
I am not sure that you and I mean the same thing with "merge"

Please upload an original sheet from an employee, a sheet that still has merged data or merged cells on it. Highlight the merging.

Paul_Hossler
11-17-2019, 10:03 AM
I am not sure that you and I mean the same thing with "merge"

Please upload an original sheet from an employee, a sheet that still has merged data or merged cells on it. Highlight the merging.


From OP's #1 -- It's hard to see but the XLSM reflects the picture in the post



I tried to modify the excel file has been published by VijianSale-Database-V4.1.1-Ask.xlsm (http://www.vbaexpress.com/forum/attachment.php?attachmentid=25408&d=1573910864) which use excel as database ...

Paul_Hossler
11-17-2019, 10:32 AM
This was not my code




Private Sub UnMerge_Merged()
With Sheets("data")
a_max = .Cells(.Rows.Count, "A").End(xlUp).Row
For x = 1 To 12
For a = 3 To a_max
With .Cells(a, x)
If .MergeCells Then
ax = .Value
m = .MergeArea.Count
.UnMerge
For am = a To a + m - 1
With Sheets("data").Cells(am, x)
.Value = ax
.Interior.ColorIndex = 6
End With
DoEvents
Next am
End If
End With
Next a
Next x
MsgBox "Done"
End With
End Sub




Look at the attachment

lamdt
11-17-2019, 07:54 PM
Hi Paul,
Thank you for your attention.
I tried your code, i import it to the Update drop down but it does not work :(

Paul_Hossler
11-18-2019, 01:17 PM
You have Form controls on your View sheet. Form control macros go in a standard module, not in the worksheets code module. ActiveX controls have their code in the worksheet module

I don't know about the rest of what you've got, but if I put the Unmerge macro onto a standard module (1) and on View sheet specify that macro (2), then it works



(1)
25426


(2)
25425


This is to only run the Unmerge sub, NOT the rest since I don't have anything to test with

lamdt
11-18-2019, 09:07 PM
It worked Paul, thank you very much but could you add the function like: click 1st time it with unmerge all merged cells, click 2nd time it will merged cells with same information ?