Software Applications Programming Errors and solutions.

Please like , share and subscribe to learn more about MS Word, PPT, Excel. 

create an Index sheet for all the worksheets within the workbook In this example there is worksheet named 'HOME' where you create a list of worksheets.  Sub ListSheets() ' Description: This procedure creates worksheet List ' and corresponding GoTo hyperlinks. '-- Const cstrProcedure = "ListSheets" On Error GoTo HandleError Dim wks As Worksheet Dim strSheetName As String Dim i As Integer, j As Integer, n As Integer Application.ScreenUpdating = False With ThisWorkbook n = .Worksheets.Count ' Customize Start Reference Set c = .Worksheets("Home").Range("B6") ' Start List From HOME!B6 c.CurrentRegion.Offset(1, 0).ClearContents i = 0: j = 0 For Each wks In .Worksheets i = i + 1 ''' If Sheet Name <> predefined sheet names thenh update List by sheet name If (wks.Name <> "HOME...

Paste in Sheet selected from a Userform ComboBox Sub CopyTest() '---------- 'Sheets("?Sheet1")Select ' <<< Syntax!! (apostroph) 'Range("B:E").Select 'Selection.Copy 'Sheets(cboPasteInSheet.Text).Select '-- ' What do you want to do here? ' what are E13 and HA13 ?? 'For i = E13 To HA13 Step 4 'If ActiveCell.Row = Empty Then 'ActiveCell.EntireRow.Insert 'End If 'Next i '------------ ' Syntax how to Copy Excel Range to another position: With Worksheets("Sheet1") ' Copy complete Columns .Range("B:E").Copy Destination:=.Range("H:H") ' Copy Range (H2=destination Top-Left Cell) .Range("B2:E20").Copy Destination:=.Range("H2") End With End Sub

All the data are combined in one column. Each cell has multiple values  to separate into the neighboring columns, which are separated by commas. Sub BreakDownTextToColumns() Dim strTextData As String Dim i As Integer, x As Integer Dim strSplitText$() x = 1 Range("A1").Select ' cell above data to separate Do Until ActiveCell.Offset(x, 0).Range("A1").Value = "" strTextData = ActiveCell.Offset(x, 0).Range("A1").Value strSplitText = Split(strTextData, ",") ' a comma is used as the signal to separate For i = 0 To UBound(strSplitText) ActiveCell.Offset(x, i).Range("A1").Value = (strSplitText(i)) Next i x = x + 1 Loop End Sub

 call an oracle SQL script from EXCEL For example, I'll execute SELECT query. Step 1: Learn name OLEDB provider of data for Oracle. If you established Oracle Client, this provider will be present. To learn a name of the provider, for example, create an empty file with extension *.udl (in Windows, certainly ). Then open its properties. On a page "Provider" you can see the list of providers. Step 2: Open VBA in Excel (Tools - Macro - Visual Basic Editors) and write this Script (Insert - New Module): Sub Macro1() ' Macro1 Macro Dim MyConn As ADODB.Connection Dim MyRst As ADODB.Recordset Dim MyPr As String Dim Ct As Long Set MyConn = New ADODB.Connection MyPr = "Provider=your_OLEDB_provider_name;Password=your_password;Persist Security Info=True;User ID=your_user;Data Source=your_Oracle_server_name" MyConn.Open MyPr Set MyRst = New ADODB.Recordset MyRst.Open "Select * from table_name", MyConn, adOpenStatic...

Get Excel Column Name in excel sheet Just write in macro of excel in module this code Public Function ConvertToLetter(iCol As Integer) As String Dim columnName As String Dim modulo As Integer While iCol > 0 modulo = (iCol - 1) Mod 26 columnName = Chr(65 + modulo) + columnName iCol = Int((iCol - modulo) / 26) Wend ConvertToLetter = columnName End Function and call in your sheet

Trim string from sentence in excel Public Function trimtwo(ByVal r As Range) As String Dim str As String str = r(1, 1) Dim Flag As Boolean Flag = False Dim indexof As Integer indexof = InStr(str, "Shri") If indexof Then str = Mid(str, indexof + 4, Len(str)) Flag = True End If indexof = InStr(str, "SHRI") If indexof Then str = Mid(str, indexof + 4, Len(str)) Flag = True End If indexof = InStr(str, "Smt.") If indexof Then 'str = Mid(str, 1, 1) & Mid(str, indexof + 5, Len(str)) str = Mid(str, indexof + 5, Len(str)) Flag = True End If indexof = InStr(str, ",") If indexof Then str = Mid(str, 1, indexof - 1) End If str = Trim(str) If Flag Then indexof = InStr(str, " ") If indexof Then str = Mid(str, 1, indexof - 1) & Mid(str, indexof + 1, Len(str)) End If End If trimtwo = str End Function Public Function trimone(ByVal r As...

Vlookup in excel to get value from other sheet and use of instr function Vlookup example =IF(ISERROR(VLOOKUP(J5,Designation!$B$2:Designation!$D$130,3,FALSE)),"",VLOOKUP(J5,Designation!$B$2:Designation!$D$130,3,FALSE)) =IF(ISERROR(VLOOKUP(G6,EmployeeDetails!$E$2:$F$18,2,FALSE)),"",VLOOKUP(G6,EmployeeDetails!$E$2:$F$18,2,FALSE)) Public Function scut(ctxt As String) As String Dim ind As Integer ind = InStr(ctxt, "0") Dim rstr As String rstr = Mid(ctxt, 1, ind - 1) scut = rstr End Function This is simple example of Vlookup. How to use vlookup in Excel worksheet. =trims(C2) =IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$E$2:$J$470,6,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$F$2:$J$470,5,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$G$2:$J$470,4,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$H$2:$J$470,3,FALSE)),IF(ISERROR(VLOOKUP(TRIM(B2),EInfo!$I$2:$J$470,2,FALSE)),"",VLOOKUP(TRIM(B2),EInfo!$I$2:$J$470,2,FALSE)),VLOOKUP(TRIM(B...

Option Explicit '32-bit API declarations 'Test the code: 'Click Tools --> Macro --> Macros... 'Select CombineFiles and press Run. Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _ pszpath As String) As Long Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _ As Long Public Type BrowseInfo hOwner As Long pIDLRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Function GetDirectory(Optional msg) As String On Error Resume Next Dim bInfo As BrowseInfo Dim path As String Dim r As Long, x As Long, pos As Integer 'Root folder = Desktop bInfo.pIDLRoot = 0& 'Title in the dialog If IsMissing(msg) Then bInfo.lpszTitle = "Please sel...

Option Explicit 'Copy a range/column after the last column with data 'Note: This example use the function LastCol 'This example copy column A from each sheet after the last column with data on the DestSh. 'I use A:A to copy the whole column but you can also use a range like A1:A10 'Use A:C if you want to copy more columns. 'Change it here ''Fill in the column(s) that you want to copy 'Set CopyRng = sh.Range("A:A") 'Remember that Excel 97-2003 have only 256 columns. 'Excel 2007 has 16384 columns. 'When you run one of the examples it will first delete the summary worksheet 'named DBMergeSheet if it exists and then adds a new one to the workbook.'This ensures that the data is always up to date after you run the code. Sub AppendDataAfterLastColumn() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableE...