QP School - Excel VBA Tutorials

Format cells in a range with bold font and yellow background color

Thu, 27 Jul 2023 13:26:06 +0200

Example:

Sub FormatCellsInRange()
Dim ws As Worksheet
Dim formatRange As Range

'Set the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("Sheet1")

'Set the range of cells to be formatted (adjust the range as per your data)
Set formatRange = ws.Range("A1:B5")

'Apply formatting to the range
With formatRange
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0) 'Yellow color
End With
End Sub

Explanation:

The code defines a subroutine named FormatCellsInRange.
It declares variables for the worksheet (ws) and the range of cells to be formatted (formatRange).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
Set formatRange = ws.Range("A1:B5") sets the range A1:B5 to be formatted.
Adjust the range as per your data.
The With block is used to apply formatting to the formatRange.
.Font.Bold = True makes the font bold for all cells in the range.
.Interior.Color = RGB(255, 255, 0) sets the background color of all cells in the range to yellow using the RGB value (255, 255, 0).

Copy data to another worksheet

Thu, 27 Jul 2023 13:24:59 +0200

Example:

Sub CopyDataToAnotherSheet()
Dim sourceWs As Worksheet
Dim targetWs As Worksheet
Dim lastRowSource As Long
Dim lastRowTarget As Long

'Set the source worksheet where the data is located
Set sourceWs = ThisWorkbook.Sheets("SourceSheet")

'Set the target worksheet where the data will be copied
Set targetWs = ThisWorkbook.Sheets("TargetSheet")

'Find the last row with data in the source worksheet
lastRowSource = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row

'Find the last row with data in the target worksheet
lastRowTarget = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row

'Copy the data from the source worksheet to the target worksheet
sourceWs.Range("A1:A" & lastRowSource).Copy targetWs.Range("A" & lastRowTarget + 1)
End Sub

Explanation:

The code defines a subroutine named CopyDataToAnotherSheet.
It declares variables for the source worksheet (sourceWs), the target worksheet (targetWs), and the last row with data in each worksheet (lastRowSource and lastRowTarget).
Set sourceWs = ThisWorkbook.Sheets("SourceSheet") specifies the source worksheet ("SourceSheet") where the data is located.
Set targetWs = ThisWorkbook.Sheets("TargetSheet") specifies the target worksheet ("TargetSheet") where the data will be copied.
The lastRowSource variable is determined using sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A of the source worksheet.
The lastRowTarget variable is determined using targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A of the target worksheet.
The data from column A of the source worksheet (from A1 to the last row with data) is copied to the target worksheet starting from the next empty row in column A.

Autofill a series of numbers in a column

Thu, 27 Jul 2023 13:23:58 +0200

Example:

Sub AutofillSeries()
Dim ws As Worksheet
Dim startValue As Integer
Dim endValue As Integer

'Set the worksheet where the series will be created
Set ws = ThisWorkbook.Sheets("Sheet1")

'Specify the start and end values for the series
startValue = 1
endValue = 10

'Enter the start value in the first cell of the series (A1)
ws.Range("A1").Value = startValue

'Autofill the series from the second cell (A2) to the end value (A10)
ws.Range("A1").AutoFill Destination:=ws.Range("A1:A10"), Type:=xlFillSeries
End Sub

Explanation:

The code defines a subroutine named AutofillSeries.
It declares variables for the worksheet (ws), the start value of the series (startValue), and the end value of the series (endValue).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the series will be created.
startValue = 1 and endValue = 10 set the start and end values for the series.
The start value (1 in this example) is entered in cell A1 using ws.Range("A1").Value = startValue.
The ws.Range("A1").AutoFill method is used to autofill the series from cell A2 to cell A10.
Destination:=ws.Range("A1:A10") specifies the destination range for autofilling, and Type:=xlFillSeries indicates that it should be filled as a series.

Loop through columns and clear contents

Thu, 27 Jul 2023 13:21:28 +0200

Example:

Sub ClearContentsInColumns()
Dim ws As Worksheet
Dim lastColumn As Long
Dim col As Long

'Set the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("Sheet1")

'Find the last column with data in the worksheet
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

'Loop through each column and clear the contents
For col = 1 To lastColumn
ws.Columns(col).ClearContents
Next col
End Sub

Explanation:

The code defines a subroutine named ClearContentsInColumns.
It declares variables for the worksheet (ws), the last column with data (lastColumn), and a loop counter (col).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The lastColumn variable is determined using ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column, which finds the last column with data in the worksheet.
The For loop iterates through each column from 1 to lastColumn.
ws.Columns(col).ClearContents clears the contents of each cell in the current column (col) of the worksheet.

Sum values based on criteria

Thu, 27 Jul 2023 13:20:19 +0200

Example:

Sub SumValuesBasedOnCriteria()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim sumResult As Double
Dim targetCriteria As String

'Set the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("Sheet1")

'Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Specify the target criterion in column A
targetCriteria = "Category1"

sumResult = 0 'Initialize the sum

'Loop through each row and calculate the sum of values in column B
For i = 1 To lastRow
If ws.Cells(i, "A").Value = targetCriteria Then
sumResult = sumResult + ws.Cells(i, "B").Value
End If
Next i

MsgBox "Sum of values for " & targetCriteria & ": " & sumResult
End Sub

Explanation:

The code defines a subroutine named SumValuesBasedOnCriteria.
It declares variables for the worksheet (ws), the last row with data (lastRow), a loop counter (i), the sum result (sumResult), and the target criterion (targetCriteria) to calculate the sum for.
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The lastRow variable is determined using ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A.
targetCriteria = "Category1" specifies the criterion (Category1) for which the sum will be calculated.
The For loop iterates through each row from 1 to lastRow.
The If statement checks if the value in column A (ws.Cells(i, "A").Value) matches the targetCriteria.
If it does, the value in column B (ws.Cells(i, "B").Value) is added to the sumResult.
Finally, a message box displays the sum of values for the specified criterion.

Create a hyperlink with VBA

Thu, 27 Jul 2023 13:10:22 +0200

Example:

Sub CreateHyperlink()
Dim ws As Worksheet

'Set the worksheet where the hyperlink will be created
Set ws = ThisWorkbook.Sheets("Sheet1")

'Create a hyperlink in cell A1
ws.Hyperlinks.Add _
Anchor:=ws.Range("A1"), _
Address:="https://www.example.com", _
TextToDisplay:="Visit Example Website"
End Sub

Explanation:

The code defines a subroutine named CreateHyperlink.
It declares a variable for the worksheet (ws) where the hyperlink will be created.
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the hyperlink will be added.
The ws.Hyperlinks.Add method is used to create a hyperlink.
Anchor:=ws.Range("A1") sets cell A1 as the anchor for the hyperlink (where the link will be displayed).
Address:="https://www.example.com" specifies the URL or address to which the hyperlink points.
TextToDisplay:="Visit Example Website" sets the text that will be displayed for the hyperlink in cell A1.

Split text in cells with VBA

Thu, 27 Jul 2023 13:09:33 +0200

Example:

Sub SplitTextInCells()
Dim sourceRange As Range
Dim cell As Range
Dim parts As Variant

'Set the range where the text is located (assuming data starts from A1)
Set sourceRange = Range("A1:A5")

'Loop through each cell in the range
For Each cell In sourceRange
'Split the text in the cell based on the delimiter
parts = Split(cell.Value, ",")

'Place the split parts in adjacent columns
cell.Offset(0, 1).Resize(1, UBound(parts) + 1).Value = parts
Next cell
End Sub

Explanation:

The code defines a subroutine named SplitTextInCells.
It declares variables for the source range (sourceRange), a loop variable (cell), and an array variable to hold the split parts (parts).
Set sourceRange = Range("A1:A5") specifies the range A1:A5 where the text is located.
The For Each loop is used to loop through each cell in the sourceRange.
The Split function is used to split the text in the cell based on the delimiter ("," in this example).
The result is stored in the array variable parts.
The split parts are then placed in adjacent columns to the right of the original cell.
cell.Offset(0, 1) refers to the cell next to the current cell, and Resize(1, UBound(parts) + 1) adjusts the size of the destination range to fit the number of split parts.
The split parts are written to the destination range using .Value = parts.

Count colored cells with VBA

Thu, 27 Jul 2023 13:07:58 +0200

Example:

Sub CountColoredCells()
Dim countColor As Long
Dim cell As Range
Dim targetColor As Long

'Specify the target color (RGB value) to count
targetColor = RGB(255, 0, 0) 'Red color in this example

countColor = 0 'Initialize the counter

'Loop through each cell in the range
For Each cell In Range("A1:A10")
'Check if the cell's background color matches the target color
If cell.Interior.Color = targetColor Then
countColor = countColor + 1 'Increment the counter
End If
Next cell

MsgBox "Number of cells with the target color: " & countColor
End Sub

Explanation:

The code defines a subroutine named CountColoredCells.
It declares variables for the counter (countColor), a loop variable (cell), and the target color (targetColor) you want to count.
In this example, we use the RGB value for the color red (255, 0, 0).
The counter countColor is initialized to 0.
The For Each loop is used to loop through each cell in the range "A1:A10".
The If statement checks if the background color of the current cell matches the target color (targetColor = RGB(255, 0, 0)).
If there is a match, the counter countColor is incremented by 1.
Finally, a message box displays the number of cells with the target color.

Calculate factorial of a given number in VBA

Thu, 27 Jul 2023 13:04:03 +0200

Example:

Function Factorial(number As Integer) As Double
Dim result As Double
Dim i As Integer

result = 1

'Calculate factorial
For i = 1 To number
result = result * i
Next i

Factorial = result
End Function

Sub CalculateFactorial()
Dim num As Integer

'Enter the number for which you want to calculate the factorial
num = 5

'Calculate and display the factorial using the Factorial function
MsgBox "Factorial of " & num & " is: " & Factorial(num)
End Sub

Explanation:

The code defines a custom function named Factorial, which calculates the factorial of a given number.
The Factorial function takes an integer number as input and returns a Double as the result.
A variable result is initialized to 1, as the factorial of 0 is 1.
The For loop runs from 1 to number, and in each iteration, the result is multiplied by i.
The Factorial function returns the final result as the factorial of the input number.
The CalculateFactorial subroutine demonstrates how to use the Factorial function.
A number (5 in this example) is assigned to the variable num.
The factorial of num is calculated using the Factorial function and displayed in a message box using MsgBox.

Generate random numbers in VBA

Thu, 27 Jul 2023 13:03:08 +0200

Example:

Sub GenerateRandomNumbers()
Dim i As Long

'Generate and place 10 random numbers in column A
For i = 1 To 10
Cells(i, "A").Value = Int((100 * Rnd) + 1)
Next i
End Sub

Explanation:

The code defines a subroutine named GenerateRandomNumbers.
It declares a loop counter i.
The For loop runs from 1 to 10, generating ten random numbers.
Rnd is used to generate a random number between 0 and 1.
(100 * Rnd) generates a random number between 0 and 100,
and Int((100 * Rnd) + 1) generates a random integer between 1 and 100.
The generated random number is placed in column A for each iteration using Cells(i, "A").Value.

Concatenate data in columns

Thu, 27 Jul 2023 13:01:59 +0200

Example:

Sub ConcatenateColumns()
Dim lastRow As Long
Dim i As Long

'Find the last row with data in column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Loop through each row and concatenate values from columns A and B
For i = 1 To lastRow
Cells(i, "C").Value = Cells(i, "A").Value & " " & Cells(i, "B").Value
Next i
End Sub

Explanation:

The code defines a subroutine named ConcatenateColumns.
It declares variables for the last row with data (lastRow) and a loop counter (i).
The lastRow variable is determined using Cells(Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A.
The For loop iterates through each row from 1 to lastRow.
In each iteration, the values from columns A and B are concatenated with a space (" ") in between, and the result is placed in column C using Cells(i, "C").Value.

Calculate the avg and color the cells when their values are above or below the avg

Thu, 27 Jul 2023 12:41:34 +0200

Example:

Sub CalculateAverageAndColorCode()
Dim dataRange As Range
Dim avgValue As Double
Dim cell As Range

'Set the range where you want to calculate the average and apply color coding
Set dataRange = Range("A1:A10")

'Calculate the average value of the range
avgValue = Application.WorksheetFunction.Average(dataRange)

'Color code cells based on whether their value is above or below the average
For Each cell In dataRange
If cell.Value > avgValue Then
cell.Interior.Color = RGB(0, 255, 0) 'Green color for above average
ElseIf cell.Value < avgValue Then
cell.Interior.Color = RGB(255, 0, 0) 'Red color for below average
End If
Next cell
End Sub

Explanation:

The code defines a subroutine named CalculateAverageAndColorCode.
It declares variables for the data range (dataRange), the average value (avgValue), and a loop variable (cell) to iterate through each cell in the range.
Set dataRange = Range("A1:A10") specifies the range A1:A10 where you want to calculate the average and apply color coding.
The Application.WorksheetFunction.Average function is used to calculate the average value of the dataRange.
The For Each loop is used to iterate through each cell in dataRange.
If a cell's value is above the average, it is colored green using cell.Interior.Color = RGB(0, 255, 0).
If a cell's value is below the average, it is colored red using cell.Interior.Color = RGB(255, 0, 0).

Protect specific cells with VBA

Thu, 27 Jul 2023 12:39:12 +0200

Example:

Sub ProtectSpecificCells()
Dim ws As Worksheet
Dim protectedRange As Range

'Set the worksheet where the cells are located
Set ws = ThisWorkbook.Sheets("Sheet1")

'Set the range of cells to be protected (adjust the range as per your data)
Set protectedRange = ws.Range("B210")

'Protect the specified cells with a password
ws.Protect Password:="mypassword", UserInterfaceOnly:=True
protectedRange.Locked = True
End Sub

Explanation:

The code defines a subroutine named ProtectSpecificCells.
It declares variables for the worksheet (ws) and the range of cells to be protected (protectedRange).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the cells are located.
Set protectedRange = ws.Range("B210") sets the range of cells B2 to D10 to be protected.
The ws.Protect method is used to protect the entire worksheet with a password ("mypassword").
The UserInterfaceOnly:=True argument allows VBA code to modify protected cells without unprotecting the worksheet.
protectedRange.Locked = True locks the specified cells within the protected worksheet.

Find a specific value in a range and replace it with a new value

Thu, 27 Jul 2023 12:38:21 +0200

Example:

Sub FindAndReplace()
Dim searchRange As Range
Dim findValue As Variant
Dim replaceValue As Variant

'Set the range where you want to find and replace values
Set searchRange = Range("A1:A10")

'Specify the value to find and the value to replace it with
findValue = "OldValue"
replaceValue = "NewValue"

'Find and replace the value in the range
searchRange.Replace What:=findValue, Replacement:=replaceValue, LookAt:=xlWhole
End Sub

Explanation:

The code defines a subroutine named FindAndReplace.
It declares variables for the search range (searchRange), the value to find (findValue), and the value to replace it with (replaceValue).
Set searchRange = Range("A1:A10") specifies the range A1:A10 where you want to find and replace values.
findValue = "OldValue" and replaceValue = "NewValue" set the values to be found and replaced, respectively.
The searchRange.Replace method is used to find and replace the value.
What:=findValue specifies the value to find, Replacement:=replaceValue specifies the value to replace it with, and LookAt:=xlWhole indicates that it should find whole matches.

Create a pivot table based on data in a worksheet with VBA

Thu, 27 Jul 2023 11:48:27 +0200

Example:

Sub CreatePivotTable()
Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCache As PivotCache
Dim ptRange As Range

'Set the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("Sheet1")

'Set the range of data for the pivot table (adjust the range as per your data)
Set ptRange = ws.Range("A1:B10")

'Create a new pivot cache based on the data range
Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ptRange)

'Create a new pivot table on a new worksheet
Set pt = ptCache.CreatePivotTable(TableDestination:=ws.Cells(1, 4), TableName:="MyPivotTable")
With pt
.PivotFields("Category").Orientation = xlRowField
.AddDataField .PivotFields("Value"), "Sum of Value", xlSum
End With
End Sub

Explanation:

The code defines a subroutine named CreatePivotTable.
It declares variables for the worksheet (ws), pivot table (pt), pivot cache (ptCache), and the range of data for the pivot table (ptRange).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The ptRange variable is set to the range of data for the pivot table (A1:B10 in this example).
A new pivot cache is created based on the data range using ThisWorkbook.PivotCaches.Create.
The pivot table is created on a new worksheet at cell D1 (TableDestination:=ws.Cells(1, 4)) with the name "MyPivotTable" using ptCache.CreatePivotTable.
The With block is used to customize the pivot table.
In this example, the "Category" field is set as a row field, and the "Value" field is added as a data field with the aggregation function xlSum.