Bỏ merge và điền full chỗ trống - Unmerge cells and fill with duplicate data with VBA code - Unmerge Quickly

 Unmerge cells and fill with duplicate data with VBA code

With the following VBA code, you can quickly unmerge the cells and fill down the values.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Modulewindow.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub UnMergeSameCell() 'Upadateby20131127 Dim Rng As Range, xCell As Range xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Application.DisplayAlerts = False For Each Rng In WorkRng     If Rng.MergeCells Then         With Rng.MergeArea             .UnMerge             .Formula = Rng.Formula         End With     End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

3. Then press the F5 key to run this code, a dialog is displayed for selecting a range to work with, see screenshot:

4. Click OK, then the merged cells have been unmerged and auto-filled down the original merged values.

https://www.extendoffice.com/documents/excel/1139-excel-unmerge-cells-and-fill.html

Label Expression - Advance (VBScript)

Ví dụ - dòng code  trong Label expression (Advanced VBScript): 

Function FindLabel ( [loaiDatHT], [loaiDatQH] )

  FindLabel = "<UND>" &  [loaiDatHT] & "</UND>" & vbnewline & "<CLR red='255' green='0' blue='0'>" & [loaiDatQH] & "</CLR>"

End Function

=> Kết quả:

Link tham khảo: http://webhelp.esri.com/arcgisdesktop/9.1/index.cfm?TopicName=About%20building%20label%20expressions
Trích link:
ArcGIS text formatting tags

Labels will be drawn using the symbol specified in the Label Manager or on the Labels tab of the Layer Properties dialog box. You can modify or override the appearance of this symbol for particular portions of the expression by inserting ArcGIS text formatting tags into the expression as text strings. This lets you create mixed-format labels where, for example, one field in a label is underlined.
The tags that you can use are listed in the table below. Acceptable values for Color (RGB) are red, green, blue = 0–255, and acceptable values for Color (CMYK) are cyan, magenta, yellow, black = 0–100; missing color attributes are assumed to be 0.

Font	"<FNT name='Arial' size='18'>" & [LABELFIELD] & "</FNT>" "<FNT name='Arial' scale='200'>" & [LABELFIELD] & "</FNT>"
Color	"<CLR red='255' green='255' blue='255'>" & [LABELFIELD] & "</CLR>" "<CLR cyan='100' magenta ='100' yellow='100' black='100'>" & [LABELFIELD] & "</CLR>"
Bold	"<BOL>" & [LABELFIELD] & "</BOL>"
Italic	"<ITA>" & [LABELFIELD] & "</ITA>"
Underline	"<UND>" & [LABELFIELD] & "</UND>"
All capitals	"<ACP>" & [LABELFIELD] & "</ACP>"
Small capitals	"<SCP>" & [LABELFIELD] & "</SCP>"
Superscript	"<SUP>" & [LABELFIELD] & "</SUP>"
Subscript	"<SUB>" & [LABELFIELD] & "</SUB>"
Character spacing (0%=regular)	"<CHR spacing='25'>" & [LABELFIELD] & "</CHR>"
Character width (100%=regular)	"<CHR width='150'>" & [LABELFIELD] & "</CHR>"
Word spacing (100%=regular)	"<WRD spacing='150'>" & [LABELFIELD] & "</WRD>"
Line leading (pts)	"<LIN leading='12'>" & [LABELFIELD] & "</LIN>"
Un-Bold	"<_BOL>" & [LABELFIELD] & "</_BOL>"
Un-Italic	"<_ITA>" & [LABELFIELD] & "</_ITA>"
Un-Underline	"<_UND>" & [LABELFIELD] & "</_UND>"
Un-Superscript	"<_SUP>" & [LABELFIELD] & "</_SUP>"
Un-Subscript	"<_SUB>" & [LABELFIELD] & "</_SUB>"

Excel - Định dạng màu tự động từ các chỉ số (R, G, B)

Microsoft Excel Magic : How to make colors automatically, using the R G B value?

https://www.youtube.com/watch?v=pRsB_fZDvXA

Private Sub CommandButton1_Click()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
R = Range(Cells(i, 1), Cells(i, 1)).Value
G = Range(Cells(i, 2), Cells(i, 2)).Value
B = Range(Cells(i, 3), Cells(i, 3)).Value
Range(Cells(i, 4), Cells(i, 4)).Interior.Color = RGB(R, G, B)
Next i
End Sub

--/--

Với:

• i = 2: i là hàng/dòng bắt đầu tính toán. Giá trị này cần thay thế tùy thuộc vào bảng của bạn.
  Ví dụ: Bắt đầu tính toán và trả ra giá trị từ hàng 5 thì phải thay "i = 5"

• Cells(i, 4): 4 là cột mà màu sẽ trả ra. Con số này thay đổi theo tùy chọn của người dùng. Muốn trả ra màu ở cột thứ mấy thì thay số 4 thành số thứ tự của cột đó.
  Ví dụ: Muốn trả ra giá trị ở cột thứ 10 thì phải thay là "Cells(i, 10)"