The Chosen Values Cutting And Pasting To Other Sheet

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


           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.



Read more ...

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))



Read more ...

Advanced Filter With Drop Down Lists

Excel VBA Advanced Filter With Drop Down Lists


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

excel advanced filter with drop down lists

          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.

excel vba advanced filter with dropdown list

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!


Read more ...

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.    
        excel data validation list

            For this reason ,we created a unique values of columns  with the VBA function to another sheet 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 The 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 sheet and to desired cells .


Read more ...

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 .


Read more ...

Add Formula To Cell With VBA

Excel Insert Formula Into Cell With Macro 


           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 the worksheet : "= IF(F2<=0; H2;F2*H2)"   


Excel insert formulas to cell with macro


All codes of our macro :
Sub carp()
Dim lastrow As Long
lastrow = Sheets("DATA").Cells(Rows.Count, "H").End(xlUp).Row
Sheets("DATA").Range("I:I").Locked = False
Sheets("DATA").Range("I2").Select

ActiveCell.Formula = "= IF(F2<=0, H2,F2*H2)"
Sheets("DATA").Range("I2").AutoFill Destination:=Range("I2:I" & lastrow), Type:=xlFillDefault

Sheets("DATA").Range("I:I").Locked = True
End Sub



Read more ...

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



Read more ...

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



Read more ...