Skip to main content

Posts

create an Index sheet for all the worksheets within the workbook,Creating a Table of Contents

By
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&quo
Read more

Paste in Sheet selected from a Userform ComboBox

By
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
Read more

Function To Returns Max ID As Identity With Table Name and Field Name As Parameter in SQL

By
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
Read more

Match and Sort Text in Two Columns in MS EXCEL

By
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
Read more

Code to concatenate multiple cells in a range using excel macro

By
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)
Read more