VBA Excel : Membandingkan jika ganjil atau genap, If even or odd statement

Hai sanak saudara,

Kali ini sharing tentang jika angka ganjil/genap di suatu cell maka :

Cekidot :

ThisWeek = Sheets("MASTER FUNCTION").Cells(5, 11).Value ' cell yang jadi patokan

If ThisWeek Mod 2 = 1 Then

GoTo 1 'perintah selanjutnya

End If

Terimakasih,

Mbahe

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT

 

VBA Excel : Membuat tanggal menjadi format teks dalam sebuah formula, put date as format text in a formula

Hai bloger, sekedar sharing agar tanggal dapat diatur formatnya dalam formula (tidak berbentuk angka).. 

coba dengan rumus ini : =TEXT($A$1,"mmm-yy"

Terimakasih,

Mbahe

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT

 

VBA Excel : Menghitung jumlah row yang ter filter, count row filtered

Sub hitunguntukproses

Dim X As Long

X = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

For Y = 0 To X Step 1

Call proses

Next Y

End sub

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT

 

VBA Excel : Unfilter / buka semua filter dalam sebuah sheet tanpa menghapus filter, Unfilter all data without erase filter

Sub UNFILTER_DATA()

If ActiveSheet.FilterMode = True Then

ActiveSheet.ShowAllData

End If

On Error Resume Next

End Sub

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT

 

VBA Excel : Membuka / mengaktifkan worksheet excel dengan partial nama, Open or activate excel worksheet with partial name

Sub BUKA_FIRST_TIME()

    Dim winAPP As Window

    For Each winAPP In Application.Windows

        If winAPP.Caption Like "*nama dokumen kamu*" Then 'ganti nama dokumen kamu / change your document name

            winAPP.Activate

            Exit For

        End If

    Next

End Sub

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT

 

VBA Excel : Mengatur semua ukuran Chart dan ukuran huruf dalam Chart, dalam suatu sheet, how to adjust chart size and font size

Berbagi info dalam mengatur ukuran chart dan tulisan di dalamnya, jadi tidak harus mengubah satu - satu bila terjadi perubahan.

Contoh sbb :

Syntaxnya :

1. Untuk mengatur ukuran chart

Sub ADJUST_CHART()

Dim a, b, c, d, e, f As Integer

a = 269.3   '9.5cm

b = 368.5   '13cm

c = 226.8   '8cm

d = 354.3   '12.5cm

e = 538.5826771654 '19cm

f = 340.157480315 '12cm

ActiveSheet.ChartObjects.Select

ActiveSheet.ChartObjects.Height = f

ActiveSheet.ChartObjects.Width = e

End Sub

2. Untuk mengatur ukuran tulisan

Sub ADJUST_CHART_TITLE_SIZE()

    Dim myChart As ChartObject

    For Each myChart In ActiveSheet.ChartObjects

        With myChart.Chart

         .ChartTitle.Font.Size = 14  '.chart title bisa diganti dengan yang lain

         .Axes(xlValue).TickLabels.Font.Size = 10 'value kiri

         .Axes(xlValue).AxisTitle.Font.Size = 12 'value kiri (keterangan)

         .Axes(xlValue, xlSecondary).TickLabels.Font.Size = 10 'value kanan

         .Axes(xlValue, xlSecondary).AxisTitle.Font.Size = 12 'value kanan (keterangan)

         .Axes(xlCategory).TickLabels.Font.Size = 10 ' Bawah

         .Legend.Font.Size = 9  'legend

        End With

    Next myChart

End Sub

Semoga membantu, bila ada tambahan bisa ditambahkan ya.

_____________________________________________________________________

IMPORTANT - This email and any attachments may be confidential and privileged. 

If received in error, please contact Thiess and delete all copies.  You may not

rely on advice and documents received by email unless confirmed by a signed Thiess

letter.  This restriction on reliance will not apply to the extent that the above email

communication is between parties to a contract and is authorised under that contract.

Before opening or using attachments, check them for viruses and defects.  Thiess'

liability is limited to resupplying any affected attachments. THIESS PRIVACY STATEMENT