Software Applications Programming Errors and solutions.

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

How to Find the Number of Repeating Values in a Table SELECT EmpNo, RepeatNo=count(*) FROM Employee GROUP BY EmpNo HAVING count(*) > 1 ORDER BY RepeatNo DESC

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

Function To Returns Max ID As Identity With Table Name and Field Name As Parameter in SQL CREATE PROCEDURE [dbo].[aMaxId] @TableName nvarchar(100), @FieldName nvarchar(100) AS DECLARE @sql nvarchar(4000) SELECT @sql = ' SELECT IsNull(Max('+@FieldName+'),0)+1 as Maxi' + ' FROM dbo.'+@TableName EXEC sp_executesql @sql

Match and Sort Text in Two Columns in MS EXCEL Sub LarsSort() Dim lngColA As Long Dim lngColC As Long Dim rngColC As Range Dim varColC As Variant Dim i As Long ' How many rows in column A? lngColA = Range("A" & Rows.Count).End(xlUp).Row ' For each item in column A, see if there is a match in column C For i = 1 To lngColA On Error Resume Next Set rngColC = Columns(3).Find(What:=Range("A" & i).Value, After:=Range("C" & i), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) On Error GoTo 0 If rngColC Is Nothing Then ' No match. Move the contents of column C to the end (if there is a value) If Range("C" & i).Value <> "" Then Range("C" & (WorksheetFunction.Max(Range("C" & Rows.Count).End(xlUp).Ro...

Code to find  to count blank cells in excel dim c as long on error resume next c = range("d42:d51").specialcells(xlcelltypeblanks).count on error goto 0 if c>0 then msgbox "your message here",vbcritical end if

Code to concatenate multiple cells in a range using excel macro Sub Combine_Multiple_Cells() On Error Resume Next Application.DisplayAlerts = False Dim Myrange As Range Dim mydelim As String Dim Combine As String Dim eachcell As Range Dim getvalue As Range Set Myrange = Application.InputBox(Prompt:="Please select a range with your Mouse to be Concatenated.", Title:="dseri", Type:=8) On Error GoTo 0 If Myrange Is Nothing Then Exit Sub Else mydelim = Application.InputBox(Prompt:="Please enter a Delimiter.", Title:="Tejas Gandhi") For Each eachcell In Myrange Combine = Combine & mydelim & eachcell.Text Next eachcell Set getvalue = Application.InputBox(Prompt:="Select a Cell where you want the Combination.", Title:="dseri", Type:=8) getvalue = Right(Combine, Len(Combine) - 1) End If End Sub One more option is there to create a User Defined Function (UDF) ...