My new blog present here.

Featured Post

Insights into Sitecore Search: A Definitive Introduction

A key component of digital experience management is effective information retrieval. A simplified approach is required for websites, applications, and platforms containing a lot of material so that consumers can easily get the data they require. This need is met by Sitecore, a well-known name in the field of digital experience platforms (DXPs), which provides powerful and comprehensive search functionality. We will travel into the realm of Sitecore Search in this article, learning about its capabilities, architecture , and the enormous value it offers both developers and end users. Introduction to Sitecore Search    A headless content discovery platform powered by AI , Sitecore Search enables you to build predictive and custom search experiences across various content sources. To extract and index you

Code related to Excel Macros

Code related to Excel Macros:

In this article I am trying to explore some important code related to VB macros which can help you to write robust VB macro based excel project.

For all code I am using the “Test.xls” excel file which have two work sheets:

1. Inventory

2. Payroll

Add the following reference to project:

1. Microsoft Excel 12.0 Object Library

2. MicrosoftOffice 12.0 Object Library

3. Microsoft Forms 2.0 Object Library

4. Microsoft Scripting Runtime

i. If you want to find total numbers of used rows and columns in the work sheet Payroll:

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

ii. If you want to find first occurrence some text(for example: Deducted) and want to delete that column in which text found in the work sheet Payroll:

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

Dim Driverrange_Payroll As Range

Dim oTemp As Range

Dim ROWSTART_Payroll As Long

Dim COLSTART_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

‘Define the range in which you need to search the text

Set Driverrange_Payroll = wsheet_Payroll.Range("a1:HV" & USEDROW_Payroll)

'---------Find Deducted in Payroll Sheet------------

Set oTemp = Driverrange_Payroll.Find("Deducted")

If Not oTemp Is Nothing Then

‘If Deducted exist in the Payroll sheet

‘----Store row number------

ROWSTART_Payroll = oTemp.Row

‘----Store column number------

COLSTART_Payroll = oTemp.Column

'----Delete column --------

wsheet_Payroll.Columns(COLSTART_Payroll).Delete

'------------------------------------

Set oTemp = Nothing

End If

iii. If you want to find first occurrence some text(for example: EmplidSearch) and want to insert new column with value after that column in which text found in the work sheet Payroll:

Dim wsheet_Payroll As Excel.Worksheet

Dim USEDROW_Payroll As Long

Dim USEDCOL_Payroll As Long

Dim Driverrange_Payroll As Range

Dim oTemp As Range

Dim ROWSTART_Payroll As Long

Dim COLSTART_Payroll As Long

‘Create connection to Payroll worksheet

Set wsheet_Payroll = Worksheets("Payroll")

‘Activate the Payroll shett

wsheet_Payroll.Activate

‘Find total number of Used Row in the Payroll sheet

USEDROW_Payroll = wsheet_Payroll.UsedRange.Rows.Count

‘Find total number of Used Row in the Payroll sheet

USEDCOL_Payroll = wsheet_Payroll.UsedRange.Columns.Count

‘Define the range in which you need to search the text

Set Driverrange_Payroll = wsheet_Payroll.Range("a1:HV" & USEDROW_Payroll)

'---------Find Deducted in Payroll Sheet------------

Set oTemp = Driverrange_Payroll.Find("EmplidSearch")

If Not oTemp Is Nothing Then

‘If Deducted exist in the Payroll sheet

‘----Store row number------

ROWSTART_Payroll = oTemp.Row

‘----Store column number------

COLSTART_Payroll = oTemp.Column

'----Insert column after empid--------

wsheet_Payroll.Columns(COLSTART_Payroll).Offset(, 1).Insert

'------------------------------------

'----------Enter value =Deducted in all columns---------

wsheet_Payroll.UsedRange.Columns(COLSTART_Payroll + 1).Value = "Deducted"

'------------------------------------

Set oTemp = Nothing

End If

Comments

Popular posts from this blog

Sitecore GraphQL Queries

Twenty 20 Masti:

Sitecore Experience Edge GraphQL Queries