Sunday, January 31, 2016

The Chosen Values Cutting And Pasting To Other Page

Useful Macros -10
Copying The Values In The Column That Match The Criteria To The Other Page

           The chosen value or values in any column are cut and transferred to other pages. While transfer  ,complete row are transmitted. Blank rows in the transfer page are deleted by the macro :

"Sheets("Data").Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).EntireRow.Delete”

In this example,data were searched in the "Column C" .For this reason the loop is used as follows :

“For i = 1 To a
If Cells(i, 3) = "Gardena" Or Cells(i, 3) = "Malibu" Then
Number 3 in the code is "Column C".


The number of cutted and transferred rows are shown on MsgBox.

Wednesday, January 27, 2016

Get Data From Another Sheet Using Vlookup / Data Validation List

Bring Data From Another Sheet Using Vlookup / Data Validation List

   As example ,an invoice template is used. Two names are defined.
      =Customers!$A$2:$A65536 ‘Firmalar
      =Products!$A$2:$A$65536   ‘Urunler

Later by selecting the cell F9 in the sample page ,data validation list is created :



That formula has been entered for cell F10 :

=VLOOKUP(F9;Customers!$A$2:$B$65536;2;FALSE)
With Formula ,the value of cell F10  as automatically changes according to drop-down list (F9)

Other data validation list is created for between C20,C30 :

That formula has been entered for between B20,B30 :
=IF(ISERROR(VLOOKUP(C21;Products!$A$2:$E$65536;2;FALSE));"";VLOOKUP(C21;Products!$A$2:$E$65536;2;FALSE))

For between G20,G30 :
=IF(ISERROR(VLOOKUP(C20;Products!$A$2:$E$65536;5;FALSE));"";VLOOKUP(C20;Products!$A$2:$E$65536;5;FALSE))

For between H20,H30 :
=IF(ISERROR(VLOOKUP(C20;Products!$A$2:$E$65536;3;FALSE));"";VLOOKUP(C20;Products!$A$2:$E$65536;3;FALSE))


E- Ticaret Fatura Şablonu (E-commerce Invoice Template)

E- Ticaret Fatura Şablonu

          Bu faturada birinci bölümde oluşturulan fatura diğer iki bölüme kopyalanıyor.Bölümler arasına karışmasın diye sarı birer sutun ekledim.Yerleri müşteri adı ,ürün,fiyat vs. tahmini olarak ayarladım.Fatura kesilirken sizin basılı faturalarınıza göre ayarlama yapmak gerekebilir.
          Önemli nokta ; ayarlama yaparken satır,sutun ekleme - silme yapmamanız ,bu durumda formülasyon bozulur.Satır veya sutunların genişlik ,yükseklik ayarları ile değişiklik yaparak şablon ayarını yapabilirsiniz.
          Ürün ve müşteri adlarını ,açılır kutu ile seçebilirsiniz.Ürün seçtiğinizde stok kodu,birimi,fiyatı ilgili hücreler otomatik gelir.Fiyat şablon dışında bir hücreye yazılır ve adeti yazdığınızda adet x fiyat = tutar şeklinde tutar hesaplanır I20 hücresinden başlayarak yazılır.

          Yazıcıdan çıktı almadan önce, ilgili hücreleri belli etmek için kullandığım kırmızı renkli başlıkları ve aralardaki sarı sutun ayraçlarını silmeyi unutmayın.
          

Monday, January 25, 2016

Advanced Filter With Drop Down Lists

         In this example ,we have used to filter  another page . We have used drop-down lists (Data_Validation_List) to facilitate filtering.



          The drop-down lists are filled with unique values. Also named ranges are used as sources of data validation (Col1,Col2,Col3 etc.)

When there are changes in the data sheet, may be necessary updating the unique values. so when the book is opened ,"unique values" macro runs automatically.

Private Sub Workbook_Open()
Call unique_values
End Sub

Or unique values can be updated by pressing the button on the "Unique Values" page.


Any updating doesn't need for the named ranges  .Because the codes of named ranges were defined accordingly.For example :

ActiveWorkbook.Names.Add Name:="Col1", RefersTo:="=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-0)"

 The drop-down lists will have updated values with no additional effort!

Friday, January 22, 2016

Excel Data Validation List With Unique Values

Create Data Validation List Based On Unique Entries 

              It is necessary to fill it with unique values to use the data validation list easily.    
        

For this reason ,we created a unique values of columns  with the Vba function to another page and we sorted values in ascending order.Later we defined names for each column that  of the unique values :

- Defined Names : Col1 ,Col2, Col3, Col4, Col5, Col6, Col7, Col8

Codes Of Defining Names :

Sub define_names()
ActiveWorkbook.Names.Add Name:="Col1", RefersTo:="=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-1)"
ActiveWorkbook.Names.Add Name:="Col2", RefersTo:="=OFFSET(Unique_Values!$B$2,0,0,COUNTA(Unique_Values!$B:$B)-1)"
ActiveWorkbook.Names.Add Name:="Col3", RefersTo:="=OFFSET(Unique_Values!$C$2,0,0,COUNTA(Unique_Values!$C:$C)-1)"
ActiveWorkbook.Names.Add Name:="Col4", RefersTo:="=OFFSET(Unique_Values!$D$2,0,0,COUNTA(Unique_Values!$D:$D)-1)"
ActiveWorkbook.Names.Add Name:="Col5", RefersTo:="=OFFSET(Unique_Values!$E$2,0,0,COUNTA(Unique_Values!$E:$E)-1)"
ActiveWorkbook.Names.Add Name:="Col6", RefersTo:="=OFFSET(Unique_Values!$F$2,0,0,COUNTA(Unique_Values!$F:$F)-1)"
ActiveWorkbook.Names.Add Name:="Col7", RefersTo:="=OFFSET(Unique_Values!$G$2,0,0,COUNTA(Unique_Values!$G:$G)-1)"
ActiveWorkbook.Names.Add Name:="Col8", RefersTo:="=OFFSET(Unique_Values!$H$2,0,0,COUNTA(Unique_Values!$H:$H)-1)"
End Sub

If to understand  the cause of the Offset formula used in defining names ; for example :

=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-1)

Unique_Values!$A$2: Start at cell $A$2, which is the first value in the list
0: Stay in that same row (so still at $A$2)
0: Stay in that same column (so, again, still at $A$2)
COUNTA(Unique_Values!$A:$A)-1: count the number of cells in column A that have values and then subtract 1 (the heading cell: “Priority”); grab an area that is that tall, starting with the cell currently “selected” ($A$2)

Data validation list can be added to the requested page and to desired cells .

Sunday, January 17, 2016

Excel Veri Ayırma (Split)

Gelişmiş Veri Ayırma - Split Şablonu

         D1 hücresindeki veri ,kullanıcı seçimine göre açılan inputbox a değer girilmek suretiyle (virgül,nokta,boşluk vs) A2 hücresinden aşağıya doğru parçalara ayırılır.Ayrılan parça sayısı msgbox ile gösterilir.

         Inputbox varsayılan olarak virgül yazılı olarak açılır ,del tuşu ile silinebilir yeni değer yazılabilir.Boş bırakılırsa veriyi boşluğa göre ayırır .
Boşluğa göre ayırma yapılırsa bu sayede herhangi bir metindeki kelime sayısı öğrenilebilir.





Saturday, January 16, 2016

Splitting Strings İnto Multiple Rows With VBA

Splitting String Based On User Choice

              When entering data in cell D1, the wrap-text function works and cell D1's height is set .Cell D1's width not change .    
              If the button in page is pressed inputbox opens.You can write in the inputbox, which delimiter will be used for splitting .
The results are listed in  column A. Splitted data total number are displayed on msgbox.



Thursday, January 14, 2016

Advanced Filtering With Vba Codes

Using Advanced Filters With Vba To Automate Filtering

       This method is pretty easy to filter specific values in your data with VBA code.



The design of for an Advanced Filter table is important to properly filter your data :

        Each heading in your designated table  must match verbatim to the column headings in your data set.

-          The column names must have been repeated   to apply multiple criteria to a single column in your data .

Sunday, January 10, 2016

Excel Discount Calculation On Userform With Vba Class

Discounted Price Calculation With Vba Class Module
           
           On the userform,the price of the product and discounted price is calculated as automatically  with text boxes.Vba class was used for calculation.

Following formula is used  to calculate the discount :

"Discounted Price  =  Quantity * Price (Quantity * Price /100) * Discount  "

If you wish calculations and processes can be saved from the userform to sheet.


Thursday, January 7, 2016

Create Index Page For WorkBook

Creating The Workbook's Index With Vba Codes

         In the index page , hyperlinks are created  to all  pages of the workbook. Clicking the hyperlinks,can be went to related address.

When "ESC key" is pressed, can be returned to the index page.Index page is automatically updated when a new sheet is added.
.

If you add the following codes to the  which page ,index is created in this page :

Private Sub Worksheet_Activate()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 0
Sheets("INDEX").Range("A10:B2000").ClearContents
For Each Page In Worksheets
Sheets("INDEX").Cells(k, 2).Select
Sheets("INDEX").Cells(k, 1).Value = m
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
Sheets("INDEX").Cells(1, 1).Select
End Sub


Tuesday, January 5, 2016

Add Formula To Cell With VBA

Insert Formula Into Cell With Macro  Code

           With macro , formulas inserting  are fast especially to multiple cells  .

"Sheets("DATA").Range("I2").AutoFill Destination:=Range("I2:I" & lastrow), Type:=xlFillDefault"

Issues to be considered :
- The extra quotes  must be used .
Example : "= IF(D12<=0,"”valid”,F2*H2)"

- The comma must be used instead of semicolon.
Example : "= IF(F2<=0, H2,F2*H2)" 
When used into page : "= IF(F2<=0; H2;F2*H2)"   


Monday, January 4, 2016

Regular Expression Examples

Excel Regular Expression Examples

         Particularly parsing processes is  easy and fast with regex.I have collected some examples of regular expression on one page.

These examples :
- Seperating Text And Numbers
- Seperating Text And Decimal Numbers
- Seperating Text And Numbers Into 3  Columns
- Email Validation


Saturday, January 2, 2016

Separating Text And Numbers

Separating Non-Numeric Characters And Numbers In Excel

                We used "Regular Expression Language (Regex)" for this tutorial.Cells in column B were used as sample.
Data are parsed with the button into Column C (as numeric)  and Column D (as alphabetic)..

This pattern code was used "to separate numbers" :

With RegEx
        .Global = True
        .Pattern = "\d+"        
  End With

To seperate non-numeric characters (letter etc.) :

With RegEx2
        .Global = True
        .Pattern = "[^0-9,]"     
 End With