# Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has been working with the ExcelDemy project for more than 1 year. He has written 50+ articles and provided solutions of 60+ comments for ExcelDemy. Currently, he is working as an Excel & VBA Developer and also provides support and solutions in the ExcelDemy Forum. His work and learning interests are in developing various Excel & VBA applications. Outside of work, he enjoys Chess a lot. He is a founding Jahangirnagar University Chess Club member and an internationally rated chess player.

## How to Subtract Dates in Excel to Get Years (7 Simple Methods)

Microsoft Excel is undoubtedly a highly efficient program. Excel's tools and functions allow us to execute endless actions on a dataset. This article discusses ...

## How to Convert Lat Long to UTM in Excel (with Easy Steps)

Excel by Microsoft is, without question, one of the most powerful computer programs available. We can perform an almost infinite computation on any dataset ...

## Automatically Create PowerPoint Slides from Excel (3 Easy Ways)

Microsoft Excel is indeed an extremely effective software. We may perform countless operations on a given dataset using Excel's tools and features. We ...

## Excel Formula for Working with Positive and Negative Numbers

In this Excel tutorial, you will learn how to work with positive and negative numbers using Excel Formula. We will discuss how to do these using Excel ...

## How to Make Excel Cells the Same Height and Width (3 Easy Ways)

In this Excel tutorial, you will learn how to make cells with the same height and width. Excel cells can be made of the same height and width by several ...

## How to Use Excel VLOOKUP to Return Multiple Values Vertically

Excel VLOOKUP function returns only one result that matches the lookup criteria. So, you will get stuck when you have multiple matching values and try to get ...

Browsing All Comments By: Lutfor Rahman Shimanto
Lutfor Rahman Shimanto Jan 26, 2023 at 2:03 PM

Hello Raj
Thank you for reaching out with your comment. You encountered a different result than what was described in the post. I assume that you missed inserting positive numbers in the range C16:C17. However, once those values were included, I discovered a result identical to what is described in the post. Therefore, It’s essential to ensure all intended data is inputted correctly.
Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jan 26, 2023 at 3:31 PM

Hello Charlotte Fahey
Thank you for reporting on this fascinating issue. I experience the same problems when a postal code begins with 0, and it is essential to preserve the leading zero when entering data into the system or application.
The Postal Code column must be formatted as text. Following that, insert the desired data.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jan 29, 2023 at 11:02 AM

Thank you for bringing this issue to my attention, William Wyatt. I understand that you have been experiencing difficulties using formulas on cells formatted as fractions in your workbook. I apologize for any confusion or frustration this may have caused you.
I have gone through this article and did not experience any of your issues. I am using Microsoft 365 to investigate this case. Could you share your workbook with us via email to better understand your situation? I would appreciate it if you could assist me more effectively.
Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jan 31, 2023 at 9:59 AM

Hello DEBB WOLFE
We appreciate your comment. I understand your difficulty, and you can avoid the issue by importing the CVS file into the existing worksheet.
Change the column type in the Power Query window to text, as this article mentions. Next, select the Home tab. Select Close & Load and then Close & Load To at a later time.

As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.

Thus, you will be able to solve the problem.
Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jan 31, 2023 at 4:01 PM

Hello ARON HOLMBERG
Thank you for reporting your issues. To count the number of components for each station, when the Station ID may be in one of three different columns, you can use the SUMPRODUCT function.
=SUMPRODUCT((InputSheet!\$B:\$B=B5)+(InputSheet!\$C:\$C=B5)+(InputSheet!\$D:\$D=B5))
This formula will test each of the three columns for the station ID and return 1 if it’s present in any of the columns and 0 if it’s not. Then, SUMPRODUCT will sum up the results, giving you the components for the station.
This solution is more elegant than creating a new column that combines the three columns, as it avoids the need to manipulate the data. If you would like a copy of the illustrated workbook, please click the link provided below this section.
Best regards,
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Mar 23, 2023 at 11:29 AM

Hello ABIGAYLE PAULSON
Thank you for reporting on this fascinating issue. I have reviewed this article and found an interesting idea to solve your problem. For illustration, let’s walk through 2nd Example. Filtering the dataset (Sheet3) based on cell values on another sheet (Sheet4). The context filters the dataset for Apple or Tomato products.
VBA Code:

``````Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim product1, product2 As Range

If Target.Worksheet.Name = "Sheet4" And (Target.Address = "\$C\$2" Or Target.Address = "\$E\$2") Then
With Worksheets("Sheet4")
Set product1 = .Range("C2")
Set product2 = .Range("E2")
End With
With Worksheets("Sheet3")
With .Range("B4:G13")
.AutoFilter Field:=3, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
End With
End With
End If
End Sub``````

If the changed cell is either C2 or E2, the macro will execute and filter the data in “Sheet3” based on the new values in these cells. Note that the Worksheet_Change event must be placed in the code module for the “Sheet4” worksheet.
The changes we must make to the original code to create the auto-filtering behavior:

1) We have to add a Private Sub Worksheet_Change(ByVal Target As Range) procedure to the code module for the “Sheet4” worksheet. This procedure runs automatically whenever a cell value is changed on this worksheet.
2) We can add an If statement to check whether the changed cell is in Sheet4 and C2 or E2. If so, the macro continues executing; if not, it exits without doing anything.
3) We need to move the Dim statements for product1 and product2 inside the If statement, so they are only declared if the macro is going to run.
4) The rest of the macro code is the same as the original code, so it will apply the same filter to “Sheet3” based on the new values in “Sheet4” whenever the cell value changes.

By adding this Worksheet_Change procedure to the code module, the macro will run automatically whenever a change is made to the specified cells in “Sheet4” without manually opening and running the macro.
Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Dec 5, 2023 at 6:05 PM

Hello KURT

Thank you for reading our article and providing helpful input. We sincerely appreciate your time and effort in reviewing the content and pointing out the calculation error. Your concern about including the \$30 per unit cost in the total has been noted.

We have already taken corrective action and will update the post accordingly. Thank you one more. Best wishes!

Regards
Lutfor Rahman Shimanto
ExcelDemy

Lutfor Rahman Shimanto Dec 5, 2023 at 4:18 PM

Hello LUIS CARRERA

Thanks for sharing your problem. The error you are facing is like the following:

Run-time error ‘1004’
Application-defined or object-defined error

On the line below:

Set RG = RG.Offset(1, 0)

The error arises from setting the range offset outside the worksheet’s boundaries. The range RG starts at cell H5, and when you try to set the offset to (1, 0), it might go beyond the last row of the worksheet.

However, I am presenting an Excel VBA code to handle that error.

Excel VBA Code:

``````

On Error GoTo ErrorHandler

Dim X1, X2, X3, X4, X5, X6 As Range
Dim RG As Range
Dim xStr As String
Dim FN1, FN2, FN3, FN4, FN5, FN6 As Integer
Dim SV1, SV2, SV3, SV4, SV5, SV6 As String

Set X1 = Range("B5:B19")
Set X2 = Range("C5:C19")
Set X3 = Range("D5:D19")
Set X4 = Range("E5:E19")
Set X5 = Range("F5:F19")
Set X6 = Range("G5:G19")

xStr = " - "

Set RG = Range("H5")
For FN1 = 1 To X1.Count
SV1 = X1.Item(FN1).Text
For FN2 = 1 To X2.Count
SV2 = X2.Item(FN2).Text
For FN3 = 1 To X3.Count
SV3 = X3.Item(FN3).Text
For FN4 = 1 To X4.Count
SV4 = X4.Item(FN4).Text
For FN5 = 1 To X5.Count
SV5 = X5.Item(FN5).Text
For FN6 = 1 To X6.Count
SV6 = X6.Item(FN6).Text
RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4 & xStr & SV5 & xStr & SV6
Set RG = RG.Offset(1, 0)
Next
Next
Next
Next
Next
Next

Exit Sub

ErrorHandler:

Exit Sub

End Sub
``````

Hopefully, the code will work perfectly for you. Good luck.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Dec 4, 2023 at 2:42 PM

Hello MATS WIKMAN

Thanks for your nice word. Your appreciation means a lot to us. I am sharing my findings regarding your issues as follows:

>> Excel drop-down lists typically display items vertically in a drop-down that displays either below or above the cell, depending on available space.

>> There is no built-in option in Excel’s Data Validation tool, frequently used to construct drop-down lists, to show the items horizontally within a single cell.

>> The Swedish edition of Excel 365 differs from the English version. In that case, you can seek help from Microsoft’s support resources for the most accurate guidance based on your specific version and language.

Hopefully, the suggestion will help you in reaching your goal. If you still have doubts, you can share your issues with precise details within the ExcelDemy Forum. Good luck!

Regards
Lutfor Rahman Shimanto
ExcelDemy

Lutfor Rahman Shimanto Dec 3, 2023 at 5:51 PM

Hello YVONNE

Thanks for reaching out and sharing your query. You wanted a formula to find duplicates when columns can be suffered, like Laura & China and China & Laura.

I have developed two formulas that will fulfil your requirements using the IF and COUNTIF functions. I will use column C as a Helper column, displaying the result in column D.

Step 1: Select cell D5 => Insert the given formula => Drag the Fill Handle icon to cell D10.

=IF(B5 < C5, B5 & " " & C5, C5 & " " & B5)

Step 2: Select cell D5 => Insert the given formula => Drag the Fill Handle icon to cell D10.

=IF(COUNTIF(\$D\$5:\$D\$10, D5)>1, “Duplicates”, “No Duplicates”)

Hopefully, you have got the solution. Good luck.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 30, 2023 at 7:16 PM

Hello GFIN SUNNY

Thanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your problem.

Unfortunately, using VBA to automate web interactions with sites that require authentication is not easy when it requires login. You may need a more advanced approach to handle authentication, such as sending HTTP requests with the necessary credentials.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 30, 2023 at 5:15 PM

Hello CYNTHIA

Thanks for visiting our blogs and sharing your queries. You want to know how to initialize the weight in the method 3. When investigating your problem, we found that the dataset we previously used does not suit the weighted moving average formula. So, we have modified the article (method 3) for better understanding.

When implementing a weighted moving average formula, it is better to fix the total weight first and distribute the weights among durations. So, please go to method 3 of this article again. Hopefully, you will not have any doubts. Good luck!

Regards
Lutfor Rahman Shimanto
ExcelDemy

Lutfor Rahman Shimanto Nov 30, 2023 at 11:25 AM

Hello BEN

Thanks for reaching out and sharing your problem. The requirements you mentioned can be achieved by following some steps.

Step 1: Create a Drop-down for Subs

Step 2: Find Employees Based on Subs in an Intermediate Column
Select cell F2 => Insert the following formula => Hit Enter.

=FILTER(B2:B16,A2:A16=A23)

Step 3: Create a Drop-down for Employees

Step 4: Display Title and Rate Based on Employee
Select cell C23 => Insert the following formula => Hit Enter.

=IF(B23<>“”,FILTER(C2:D16,B2:B16=B23),””)

Step 5: Hide the Intermediate Column and Insert an Event Procedure
Hide column F => Right-click on the sheet name tab => Click on View Code => Paste the following code in the sheet module => Save.

``````
Private Sub Worksheet_Change(ByVal Target As Range)

Range("B23").ClearContents
End If

End Sub
``````

OUTPUT: Return to the sheet and make desired changes to see the result like the following GIF.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 29, 2023 at 1:12 PM

Hello Greg

Thanks for your nice words. Your appreciation means a lot to us. You wanted to know the formula for adding a Double time column for anything above 12 hours daily.

I am delighted to inform you that I have developed an Excel Formula using the IF and TIME functions to fulfil your requirements.

Step 1: Select cell G11 => Insert the following formula.

=IF(F11-TIME(12,0,0)<0,0,F11-TIME(12,0,0))

Step 2: Hit Enter to see the result, like the image below.

Step 3: Hover over the cursor on the right button corner of cell G11 to see the Fill Handle icon.

Step 4: Drag the Fill Handle icon to cell G18 to copy down the formula.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 27, 2023 at 5:18 PM

Hello HUONGPT

Thanks for sharing your problem. You want to get data from Excel 365 (WEB) to a local Excel application. Fencing data from Excel 365 (WEB) is different from importing data from Google Sheets. I am presenting an Excel VBA sub-procedure. However, you must have valid Windows Security credentials on your own.

Excel VBA Sub-procedure:

``````
Sub ImportDataFromExcel365()

Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim sourceRange As String

Set sourceWorkbook = Workbooks.Open("https://1drv.ms/x/s!AiLbipRfCPXPkWiuZTaSCmdP1XKp?e=5rmfGX")

Set sourceSheet = sourceWorkbook.Sheets("Sheet1")
sourceRange = "A1:B10"

Dim destinationWorkbook As Workbook
Dim destinationSheet As Worksheet
Dim destinationCell As Range

Set destinationWorkbook = ThisWorkbook

Set destinationSheet = destinationWorkbook.Sheets("DestinationSheet")
Set destinationCell = destinationSheet.Range("A1")

With destinationWorkbook.Connections.Add2("Excel365 Query", "", sourceRange, "Excel365", 6)
.OLEDBConnection.BackgroundQuery = False
.OLEDBConnection.RefreshOnFileOpen = False
.Refresh
End With

sourceSheet.UsedRange.Copy destinationCell

End Sub
``````

After Running the code, the Windows Security dialog box will appear => Next, insert the intended username and password => Hit OK.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 26, 2023 at 4:47 PM

Hello ALAN FARTHING

Thanks for reaching out and sharing your problem. I found a Syntax Error within your given code. You have to write the .sort property within the With block.

Corrected Sub-procedure:

``````
Sub Ascending_Order_Sorting()
With ActiveWorkbook.Worksheets("Grid_Reference_Tables").Range("Grid_Reference")
End With
End Sub
``````

OUTPUT:

Hopefully, the correction resolves your problem. Stay blessed.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 23, 2023 at 9:35 AM

Hello ROBERT MCPHEE

Thanks for reaching out and posting your query. You wanted to create a nested formula using the IF function. You were almost there. The formula you have given contains some syntax errors.

Corrected Formula:

=IF(R5=1, F5, IF(R5=2, L5, IF(R5=3, M5, “”)))

If you want to use the formula from another sheet, for example, from any sheet and use the R5, F5, L5, and M5 cells of Sheet1, use the following formula.

=IF(Sheet1!R5=1, Sheet1!F5, IF(Sheet1!R5=2, Sheet1!L5, IF(Sheet1!R5=3, Sheet1!M5, “”)))

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 20, 2023 at 3:16 PM

Hello XU

Thanks for thanking me. Your appreciation means a lot to us. You wanted to convert UTM (UTM Easting, UTM Northing and UTM Zone) to Latitude and Longitude values.

I am presenting some Excel VBA User-Defined functions, and these functions will achieve your goal.

Step 1: Open the VBA Editor window => Paste the following code in a module => Save.

``````
Function CalculateLatitude(UTMNorthing As Double, UTMZone As Integer) As Double

Dim EccentricitySquared As Double
EccentricitySquared = 0.00669438

Dim UTMScaleFactor As Double
UTMScaleFactor = 0.9996

Dim UTMFalseNorthing As Double
If UTMNorthing < 0 Then
UTMFalseNorthing = 10000000
Else
UTMFalseNorthing = 0
End If

Dim M As Double
M = UTMNorthing / UTMScaleFactor - UTMFalseNorthing

Dim Mu As Double
Mu = M / (EquatorialRadius * (1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256))

Phi1Rad = Mu + (3 * EccentricitySquared / 2 - 27 * EccentricitySquared ^ 3 / 32) * Sin(2 * Mu) _
+ (21 * EccentricitySquared ^ 2 / 16 - 55 * EccentricitySquared ^ 4 / 32) * Sin(4 * Mu) _
+ (151 * EccentricitySquared ^ 3 / 96) * Sin(6 * Mu) _
+ (1097 * EccentricitySquared ^ 4 / 512) * Sin(8 * Mu)

Dim phi1 As Double
phi1 = Phi1Rad * 180 / 3.14159265358979

Dim N1 As Double

Dim T1 As Double

Dim C1 As Double
C1 = EccentricitySquared * Cos(Phi1Rad) ^ 2

Dim R1 As Double
R1 = EquatorialRadius * (1 - EccentricitySquared) / ((1 - EccentricitySquared * Sin(Phi1Rad) ^ 2) ^ 1.5)

Dim D As Double
D = (UTMNorthing / (N1 * UTMScaleFactor))

Dim Latitude As Double
Latitude = Phi1Rad - (N1 * Tan(Phi1Rad) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * EccentricitySquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 3 * C1 ^ 2 - 252 * EccentricitySquared) * D ^ 6 / 720)

CalculateLatitude = Latitude * 180 / 3.14159265358979

End Function

Function CalculateLongitude(UTMEasting As Double, UTMZone As Integer) As Double

Dim CentralMeridian As Double
CentralMeridian = (UTMZone - 1) * 6 - 180 + 3

Dim ScaleFactor As Double
ScaleFactor = 0.9996

Dim EccentricitySquared As Double
EccentricitySquared = 0.00669438

Dim UTMEastingShifted As Double
UTMEastingShifted = UTMEasting - 500000

Dim A1 As Double
A1 = Cos(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A2 As Double
A2 = -Sin(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A3 As Double
A3 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor)

Dim A4 As Double
A4 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 3 * (1 + Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2) / 2

Dim A5 As Double
A5 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 5 * (5 + 3 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 6 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 24

Dim A6 As Double
A6 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 7 * (61 + 90 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 45 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 720

Dim Longitude As Double
Longitude = CentralMeridian + (A1 + A2 + A3 + A4 + A5 + A6) * 180 / 3.14159265358979

CalculateLongitude = Longitude

End Function

Function GetUTMZoneNumber(UTMZone As String) As Integer
GetUTMZoneNumber = Val(Left(UTMZone, Len(UTMZone) - 1))
End Function
``````

Step 2: Return to the sheet => Choose cell E4 => Insert the following formula => And drag the Fill Handle icon to cell E11.

=CalculateLatitude(C4,GetUTMZoneNumber(D4))

Step 3: Choose cell F4 => Insert the following formula => And drag the Fill Handle icon to cell F11.

=CalculateLongitude(B4,GetUTMZoneNumber(D4))

Things to Keep in Mind:
As we cannot use all decimal points for some values (UTM Northing), we may not get the same value (Latitude) like previous one.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 20, 2023 at 3:11 PM

Hello XU

Thanks for reaching out. All the User-Defined functions mentioned in this article calculate UTM Easting, UTM Northing and UTM Zone accurately.

I am comparing the result with an Online UTM Calculator website (LatLong.net) like the image below for demonstration.

If you still have doubts, you can share your dataset in our ExcelDemy Forum. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 19, 2023 at 4:19 PM

Hello SUBBARAMAN

Thanks for reaching out and sharing your requirements. Your appreciation means a lot to us. Thanks once again.

You want to analyze the Indian Stock Markets. To achieve your goal, you only need to use the Indian company names, and the rest of the procedure is described in the article.

Like the following GIF, I generated the intended data for some noted Indian Companies.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 19, 2023 at 12:00 PM

Hello RON SMITH

Thanks for reaching out and sharing your problem. I want to thank MAHFUZA ANIKA ERA for writing such a helpful article. In particular, she developed the calendar within a UserForm.

However, you are having trouble when clicking the time box. After checking that box, you expect to get the time after the date, but the time is not displaying. I went through this article and discovered why you are having trouble displaying the time and date with a cell.

SOLUTION: You must check the time box before choosing dates to display the date and time.

If you want to choose a date and later display time, I am delighted to inform you that I have developed a Change Event Procedure for the check box named Time_Box for that. When the check box is checked, the time will be next to the date; otherwise, the time will not be displayed.

Steps: Paste the following code with the module of UserForm1 => Save.

``````
Private Sub Time_Box_Change()

Dim cellValue As String
cellValue = Selection.Value
If cellValue <> "" Then
If Time_Box.Value = True Then
If InStr(1, cellValue, ":") > 0 Then
Exit Sub
Else
Selection.Value = Selection.Value & ": " & Time()
End If

Else
If InStr(1, cellValue, ":") > 0 Then
Selection.Value = Left(cellValue, InStr(1, cellValue, ":") - 1)
Else
Exit Sub
End If
End If
End If

End Sub
``````

OUTPUT:

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 12, 2023 at 3:48 PM

Thanks for reaching out and posting an exciting query. You are right about the run-time error “13” Type mismatch. This is because the active sheet contains text value within cell B4.

I am delighted to inform you that I have developed other improved sub-procedures that have overcome the issue you mentioned by modifying the previous code. To be specific, I have taken a worksheet object to avoid this error.

Improved Sub-procedures:

Open the VBA Editor window => Click on Insert followed by Module => Paste the following code in the module => Save.

``````
Option Explicit
Public countDown As Date

Sub StartTimer()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Stopwatch")

countDown = Now + TimeValue("00:00:01")
ws.Range("B4") = ws.Range("B4") + TimeValue("00:00:01")
Application.OnTime countDown, "StartTimer"

End Sub

Sub ResetTimer()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Stopwatch")

ws.Range("B4") = TimeValue("00:00:0")

End Sub

Sub StopTimer()
Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
End Sub
``````

OUTPUT of Applying the Improved Sub-procedures:

Hopefully, the idea will resolve your problem. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 8, 2023 at 3:47 PM

Hello KRISH

Thanks for reaching out and posting your question. The mentioned schedule can be maintained in several combinations. However, I am presenting a suitable one that will fulfil your requirements.

SHIFT:
Day Shift (D): 8 AM to 4 PM
Afternoon Shift (A): 4 PM to 12 AM
Night Shift (B): 12 AM to 8 AM

MEMBERS:

You have 3 staff members (A, B, and C) and a Team Leader (D).

Staff members A, B, and C will follow a 5-day on, 2-day off schedule, rotating through the three shifts.

The Team Leader (D) will work the day shift (D) if other staff members have an off day.

Staff Rotation Schedule (7-Day Cycle):
Day 1 (Staff D’s day off):

Day Shift: Staff A works from 8 AM to 4 PM.

Afternoon Shift: Staff B works from 4 PM to 12 AM.

Night Shift: Staff C works from 12 AM to 8 AM.

Day 2 (Staff B’s day off):

Day Shift: Staff D (Team Leader) from 8 AM to 4 PM.

Afternoon Shift: Staff C works from 4 PM to 12 AM.

Night Shift: Staff A works from 12 AM to 8 AM.

Day 3 (Staff C’s day off):

Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

Afternoon Shift: Staff A works from 4 PM to 12 AM.

Night Shift: Staff B works from 12 AM to 8 AM.

Day 4 (Staff A’s day off):

Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

Afternoon Shift: Staff B works from 4 PM to 12 AM.

Night Shift: Staff C works from 12 AM to 8 AM.

Day 5 (Staff A’s day off):

Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

Afternoon Shift: Staff B works from 4 PM to 12 AM.

Night Shift: Staff C (Team Leader) works from 12 AM to 8 AM.

Day 6 (Staff B’s day off):

Day Shift: Staff C works from 8 AM to 4 PM.

Afternoon Shift: Staff A works from 4 PM to 12 AM.

Night Shift: Staff D (Team Leader) works from 12 AM to 8 AM.

Day 7 (Staff C’s day off):

Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

Afternoon Shift: Staff B works from 4 PM to 12 AM.

Night Shift: Staff A works from 12 AM to 8 AM.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 2, 2023 at 12:13 PM

Hello AJK

Thanks for sharing this exciting issue with us. The mentioned site arranges the required information on many pages. When I went through, there were 69 pages (25 records are displayed on each page). However, the page numbers will change over time. You wanted to use the PowerQuery and fetch all the information into a single sheet.

I am delighted to inform you that I have developed an Excel VBA Sub-procedure to fulfil your requirements. Thanks to MD. ABDUR RAHIM RASEL. I developed such an Excel VBA Sub-procedure by considering the idea he had given in the previous comment.

Excel VBA Sub-procedure:

``````
Sub FetchDataFromAllPagesOfAWeb()

Dim pageNumber As Integer
Dim urlBase As String
Dim queryName As String
Dim queryFormula As String
Dim ws As Worksheet
Dim startCell As Range
Dim masterSheet As Worksheet

Application.ScreenUpdating = False

masterSheet.Name = "Master Sheet"

Call deleteQueriesAndConnections

urlBase = "https://www.screener.in/screens/16162/all-stocks/?sort=name&order=asc&page="
pageNumber = 1

Do While pageNumber <= 69

queryName = "Table_" & pageNumber - 1

queryFormula = "let" & Chr(13) & Chr(10) & _
"    Source = Web.Page(Web.Contents(""" & urlBase & pageNumber & """))," & Chr(13) & Chr(10) & _
"    Data0 = Source{0}[Data]," & Chr(13) & Chr(10) & _
"    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""S.No."", Int64.Type}, {""Name"", type text}, {""CMP Rs."", type number}, {""P/E"", type number}, {""Mar Cap Rs.Cr."", type number}, {""Div Yld %"", type number}, {""NP Qtr Rs.Cr."", type number}, {""Qtr Profit Var %"", type number}, {""Sales Qtr Rs.Cr."", type number}, {""Qtr Sales Var %"", type number}, {""ROCE %"", type number}, {""Debt / Eq"", type number}, {""B.V. Rs."", type number}})" & Chr(13) & Chr(10) & _
"in" & Chr(13) & Chr(10) & _
"    #""Changed Type"""

ws.Name = "Page_" & pageNumber

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=\$Workbook\$;Location=""" & queryName & """;Extended Properties=""""" _
, Destination:=ws.Cells(2, 1)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & queryName & "]")
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = queryName
.Refresh BackgroundQuery:=False
End With

ws.UsedRange.Copy Destination:=masterSheet.Cells(masterSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)

'ThisWorkbook.Queries(queryName).Delete

pageNumber = pageNumber + 1
Loop

Dim wsToDelete As Worksheet
For Each wsToDelete In ThisWorkbook.Sheets
If wsToDelete.Name <> "Master Sheet" Then
wsToDelete.Delete
End If
Next wsToDelete

Application.ScreenUpdating = True

End Sub

Sub deleteQueriesAndConnections()

Dim QueriesAndConnections As Object

For Each QueriesAndConnections In ThisWorkbook.Queries
QueriesAndConnections.Delete
Next

End Sub
``````

OUTPUT:

Things to Remember:

1. Add a new sheet and delete the Master Sheet before running the Excel VBA Code.

2. At the end of the code execution, the code will delete all sheets except for the Master Sheet.

Limitations: The code will take 10 to 15 minutes to fetch data from that site. It will depend on the capacity of your computer and internet connection.

I am attaching the solution workbook for better understanding. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Nov 1, 2023 at 12:39 PM

Hello REZA

Thanks for reaching out and posting your comment. You are right about the raised error. If you do not add the Microsoft PowerPoint 16.0 Object Library, you must see the User-Defined Type not defined error.

To solve the issue, follow these:
Step 1: Hover over Tools => You will see the References option.

Step 2: Click on References => In the References – VBAProject window, check Microsoft PowerPoint 16.0 Object Library, and you will see the OK button in the top right corner.

Step 3: After clicking OK, you will be able to run the code like the following GIF.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 31, 2023 at 11:01 AM

Hello MIGHTY

Thanks for reaching out and posting your comment. You are right about the plan described in Method 2, but partially. Here, we could not maintain the proposal date for Task D. As mentioned in the article, we had to delay. The predecessor of Task D was Task C, which had been completed on the 10th of May. In that sense, we can say the plan made is impossible to follow. But, we must keep in mind that if we cannot start the project at the proposed time, it does not mean we are not completing it at the proposed end date. Maybe the project will end at the proposed date.

In project management, it’s common to have tasks that depend on completing other tasks. It’s a fundamental aspect of project planning to identify and manage task dependencies and ensure the project is executed in a logical and efficient order. When you have such dependencies, you need to adjust the schedule accordingly to ensure that tasks are performed in the correct sequence.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 22, 2023 at 2:44 PM

Hello DUSTIN

Thanks for reaching out and posting your query. You want to add a smoothing option while interpolating. You can achieve the goal with Excel formulas. However, I am presenting an Excel VBA User-defined function where you can add a smoothing option.

Assume you have two user-defined functions for applying the Cubic Spline and Cubic Hermite Spline interpolations. You want to create another user-defined function that will have the same parameters as before, including another extra parameter for choosing the smoothing option. Let’s say if p is provided, it will apply the Cubic Spline interpolation. And if q is provided, it will apply the Hermite Spline interpolation. If the Choose Parameter is not provided, it will raise a warning.

Excel VBA Code:

``````
Function InterpolateWithChoice(periodValue As Range, rateValue As Range, xValue As Range, choice As String) As String

If choice = "p" Then
InterpolateWithChoice = CubicSplineIntrp(periodValue, rateValue, xValue)
ElseIf choice = "q" Then
InterpolateWithChoice = HrmtSplines(periodValue, rateValue, xValue)
Else
MsgBox "Invalid Choice! Please use 'p' for Cubic Spline or 'q' for Hermite Spline.", vbExclamation, "Invalid Choice"
InterpolateWithChoice = "#Invalid Choice"
End If

End Function

Function CubicSplineIntrp(periodValue As Range, rateValue As Range, xValue As Range)
CubicSplineIntrp = "Cubic Spline"
End Function

Function HrmtSplines(periodValue As Range, rateValue As Range, xValue As Range)
HrmtSplines = "Hermite Spline"
End Function
``````

OUTPUT:

1. Cubic Spline
Here, we are providing p as the Choose Parameter.

2. Hermite Spline
We are providing q as the Choose Parameter.

3. Error Handling
Now, we are not providing any of the Choose Parameters. As a result, a Warning Window appears.

Press OK => You will get an output like the following image.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 15, 2023 at 6:18 PM

Hello HEATHER

Thanks for reaching out and posting your comment. You are right about the results from using Geography Datatypes and Excel VBA user-defined functions being slightly different. However, using a valid API endpoint in VBA code will give you accurate results as compared to using geography datatypes.

If you are a Microsoft 365 user, I recommend using the first method, where you can use Geography datatype. I have presented the Excel VBA to other Excel users. However, I must admit that using the Geography is more accurate than the Excel VBA user-defined functions.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 15, 2023 at 12:23 PM

Hello BIC

Thanks for reaching out and posting an interesting problem. You want to create a 2nd instance of the existing stopwatch code.

Excel VBA Code (2nd Instance):

``````
Option Explicit
Public countDown As Date
Public countDown2 As Date ' Public variable for 2nd instance.

Sub StartTimer()
countDown = Now + TimeValue("00:00:01")
Range("B4") = Range("B4") + TimeValue("00:00:01")
Application.OnTime countDown, "StartTimer"
End Sub
Sub ResetTimer()
Range("B4") = TimeValue("00:00:0")
End Sub
Sub StopTimer()
Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
End Sub

' Codes to start, reset and stop for 2nd instance.
Sub StartTimer2()
Range("F4").NumberFormat = "h:mm:ss"
countDown2 = Now + TimeValue("00:00:01")
Range("F4") = Range("F4") + TimeValue("00:00:01")
Application.OnTime countDown2, "StartTimer2"
End Sub
Sub ResetTimer2()
Range("F4") = TimeValue("00:00:0")
End Sub
Sub StopTimer2()
On Error Resume Next
Application.OnTime EarliestTime:=countDown2, Procedure:="StartTimer2", Schedule:=False
End Sub
``````

2nd Instance of stopwatch code
OUTPUT:

Hopefully, the idea will fulfil your requirements. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 10, 2023 at 6:45 PM

Hello NIRMAL

Thanks for reaching out and posting your comment. You asked if it is possible to insert images in a particular size in Excel using the same code or not. The answer is Yes. As mentioned in the article, you can easily set the .height and .width properties for customization.

I am going to present a modified version of the given code mentioned in the third method to handle some errors.

Excel VBA Event Procedure:

``````
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim dropdownCell As Range
Dim img As Object
Dim imgPath As String
Dim imgName As String

Set ws = ThisWorkbook.Sheets("method 3")
Set dropdownCell = ws.Range("B4")

If Not Intersect(Target, dropdownCell) Is Nothing And dropdownCell.Value <> "" Then

imgName = dropdownCell.Value & ".jpg"

If Dir(imgPath) <> "" Then
Set img = ws.Pictures.Insert(imgPath)
With img
.Left = ws.Range("B5").Left
.Top = ws.Range("B5").Top
.Width = 70
.Height = 70
End With
Else
End If
End If

End Sub
``````

Hopefully, the idea will fulfil your requirements. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Oct 4, 2023 at 1:58 PM

Hello Meni

Thanks for reaching out and sharing your expertise. I have investigated the formula you have shared and found it very powerful. We can easily separate the texts and numbers with this single formula.

Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!

Regards
Lutfor Rahman Shimanto
ExcelDemy Team

Lutfor Rahman Shimanto Oct 3, 2023 at 11:45 AM

Hello MX A HUSKINS

Thank you for reaching out and posting your comment. You are right about setting up your own valid Bing API to work properly.

The API Key will become an invalid one If 3 months or 10K transactions are reached. The API mentioned in this article may have reached 10 K transactions.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 26, 2023 at 12:15 PM

Hello NATS CO

Thanks for reaching out and posting your comment. You have given us wonderful advice, which is very appreciated. You want to modify the existing formula you have given in such a way that it will look up only a cell with value and ignore all blank cells from bottom to top.

I am delighted to inform you that I have developed such a formula by modifying your given formula. I have also developed a User-defined function using Excel VBA.

Modified Excel Formula:

=LOOKUP(2,1/(FILTER(\$C\$5:\$C\$14,\$C\$5:\$C\$14<>“”)=F5),\$D\$5:\$D\$14)

Excel VBA User-defined Function:
Open the VBA Editor => Paste the following code in a module => Save.

``````
Function CustomLookupNonBlank(FindValue As Variant, LookupRange As Range, MatchRange As Range) As Variant

Dim i As Long

For i = LookupRange.Cells.Count To 1 Step -1
If Not IsEmpty(LookupRange.Cells(i).Value) And LookupRange.Cells(i).Value = FindValue Then
CustomLookupNonBlank = MatchRange.Cells(i).Value
Exit Function
End If
Next i

CustomLookupNonBlank = CVErr(xlErrNA)

End Function
``````

Return to the sheet => Use the user-defined function like other worksheet functions.

I hope the idea will fulfil your requirements. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 13, 2023 at 2:01 PM

Hello AISHWARYA ROY

Thanks for reaching out and posting your query with such clarity. You want to add new rows with certain information using a cell value. Some rows contain text that can be a mixture of three texts: peanut butter and jelly sandwich. You want to add the new rows accordingly and insert values like 1. Peanut butter, 2. Jelly and 3. Bread.

I am delighted to inform you that I have developed an Excel VBA procedure that will fulfil your requirements.

Navigate to Developer >> click on Visual Basic.

Due to this, the VBA Editor window will appear.

1. Hover over Insert and click on Module.

2. Paste the following code and Run.

``````
Sub InsertRowsAndValues()

Dim ws As Worksheet
Dim i As Long
Dim cell As Range
Dim keywordCount As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")

For i = 2 To 100
Set cell = ws.Cells(i, "B")
keywordCount = 0

If InStr(1, cell.Value, "Peanut Butter", vbTextCompare) > 0 Then
keywordCount = keywordCount + 1
End If

If InStr(1, cell.Value, "Jelly Sandwich", vbTextCompare) > 0 Then
keywordCount = keywordCount + 1
End If

If InStr(1, cell.Value, "Bread", vbTextCompare) > 0 Then
keywordCount = keywordCount + 1
End If

If keywordCount > 0 Then
ws.Rows(i + 1 & ":" & i + keywordCount).Insert Shift:=xlDown

For j = 1 To keywordCount
ws.Cells(i + j, "B").Value = j & ". " & Choose(j, "Peanut Butter", "Jelly Sandwich", "Bread")
Next j

i = i + keywordCount
End If
Next i

End Sub
``````

Finally, you will see an output like the following one.

I am also attaching the solution workbook to help you understand better. Good luck!
WORKBOOK

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 12, 2023 at 5:24 PM

Thanks for reaching out and posting your comment. You are right about the Percentage Completion column showing 100%. Thank you once again for noticing the interesting issue. You are also correct about the formula used in the article having no error.

Reason: The Percentage Completion column shows 100% for each task. It is important to note that we are using TODAY as a reference day. All the tasks are completed 100% for the dataset shown in the article according to today’s dates.

Note:

1. If the TODAY function returns a date value between the start and end dates, you will get the intended percentage value.

2. When TODAY is greater than the end date, we will get 100% or more than 100%, meaning the task is complete.

3. In contrast, if TODAY is less than both the start and end dates, we will get negative values or negative percentage values (which means the tasks have not started yet).

Things to Remember:
When inserting date values within the start date and end date columns, ensure all the dates are in the same format.

Solution:
To demonstrate, I am using the dataset mentioned in this article. However, I will use some other date values. Here, you will see an improved version of the formula mentioned in the article to avoid negative values.

Select cell E5 >> apply the following formula >> drag Fill Handle to cell E8.

=IF(((MIN(TODAY(),D5)-C5+1)/(D5-C5+1))<0,0, ((MIN(TODAY(),D5)-C5+1)/(D5-C5+1)))

I hope the idea and concept are crystal clear to you now. Stay Blessed.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 12, 2023 at 3:09 PM

Hello DENNIS

Thanks for reaching out and noticing the problem. You are right about the Date values not being formatted as expected for 1st to 12th.

I am delighted to inform you that I have come up with a solution. You must choose a date format consistently while inserting date values in cells.

In this case, use the exact Date Picker mentioned in the article, which typically chooses the data format as mm-dd-yyyy. Next, you have to create an extra command button that will be responsible for inserting the chosen date value in cell B10. And the Excel VBA Event procedure will convert the date format into dd-mm-yyyy.

VBA Code for CommandButton1: Double-click on the inserted CommandButton1 >> insert the following code and Save. You can also change the destination cell by modifying the code.

``````
Private Sub CommandButton1_Click()

Cells(10, 2) = UserForm1.CsnDate.Caption

End Sub
``````

Excel VBA Event Procedure: Insert the following code in the intended sheet module described in my previous reply.

``````
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range

If Not Intersect(Target, Me.UsedRange) Is Nothing Then

For Each Cell In Target
If IsDate(Cell.Value) Then
Cell.NumberFormat = "dd-mm-yyyy"
End If
Next Cell

End If

End Sub
``````

OUTPUT:

Hopefully, the idea will resolve your issue. Stay Blessed.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 11, 2023 at 12:17 PM

Hello DENNIS

Thank you for staying with ExcelDemy and posting your queries. You previously asked to change the date format to dd-mm-yyyy. MUSIHA MAHFUZA MUKTA provided a solution regarding your date format problem. The solution is working perfectly at my end as well.

After reviewing your requirements, I assume you want to use the date picker to insert date values in a sheet.

I am delighted to inform you that I have developed an Excel VBA Event procedure. All you need to do is keep the event procedure in the sheet module. The process will format any date values into the dd-mm-yyyy format whether you insert date values manually or date picker.

Right-click on the sheet name tab >> click on View Code.

Due to this, the VBA Editor window will appear.
Paste the following code in the sheet module >> click the Save icon.

``````
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range

If Not Intersect(Target, Me.UsedRange) Is Nothing Then

For Each Cell In Target
If IsDate(Cell.Value) Then
Cell.NumberFormat = "dd-mm-yyyy"
End If
Next Cell

End If

End Sub
``````

Choose any cell >> Input date values with any valid date format.

After pressing Enter, we see the date value converted into the dd-mm-yyyy format.

Hopefully, The idea will fulfil your requirements. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Sep 10, 2023 at 3:58 PM

Hello JAMIE

Your appreciation means a lot to us. Thanks for your nice words!

You often click the stop button after you have already stopped the timer. That results in an Error. Thank you once again for noticing the issue. Your claim is correct.

However, I am delighted to inform you that the issue can be avoided with the help of an Error Handler called On Error Resume Next.

All you need to do is to modify the StopTime sub-procedure to fulfil your requirement.

Modified Excel VBA Code:

``````
Option Explicit
Public countDown As Date
Sub StartTimer()
countDown = Now + TimeValue("00:00:01")
Range("B4") = Range("B4") + TimeValue("00:00:01")
Application.OnTime countDown, "StartTimer"
End Sub
Sub ResetTimer()
Range("B4") = TimeValue("00:00:0")
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
End Sub
``````

Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Aug 30, 2023 at 4:58 PM

Hello Hate

Thank you for reaching out and posting your comment. The issue you are mentioning is correct. Thanks once again for noticing the problem.

I am delighted to inform you that our team successfully developed a procedure to make a Pie Chart with subcategories. According to the idea, we have modified the article. I recommend you go through the article again and enjoy it.

Regards
Lutfor Rahman Shimanto
Team ExcelDemy

Lutfor Rahman Shimanto Aug 16, 2023 at 3:22 PM

Dear Faisal

Thanks a ton. Your appreciations mean a lot to us. I am glad you have found the article very helpful.

You mentioned the user-defined functions only work for UTM Zone and UTM Easting on your end. The user-defined function for calculating UTM Northing is not working.

However, the issue you are addressing is unclear to us. On our end, it is working perfectly. I am using Microsoft 365. However, It should not make any difference. The code should work for all versions of Excel.

Please share your workbook containing a dataset with us. You can also post your query and attach the dataset in ExcelDemy Forum. We need more information about your problem, such as if it returns any error or wrong values.

The functions often may return values using scientific notation like 2.34344E+13. To avoid this type of notation, you may use an Event procedure.

Excel VBA Event Procedure:

``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim funcName As String

funcName = "CalculateUTM"

For Each cell In Target
If InStr(1, cell.Formula, funcName) > 0 Then
cell.NumberFormat = "0.00"
End If
Next cell

End Sub``````

I am looking forward to receiving your reply. Stay blessed and Good luck.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Aug 13, 2023 at 12:37 PM

Dear Amir

Sorry for the late reply. Thanks a lot for bringing the issue to us. I have developed some user-defined functions using Excel VBA. These functions take Lat and Long values and return UTM Easting, Northing and Zone. I also have modified the article accordingly. So, I recommend you to go through the article again. You will not be disappointed, I promise.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Aug 13, 2023 at 12:34 PM

Hello MOSES

Thanks for reaching out and posting your comment. You are absolutely right. However, I am delighted to inform you that I have modified the article. After going through the article, you must be able to convert Lat Long to UTM Easting, Northing and Zone. I have developed some user-defined functions using Excel VBA to achieve the goal.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Aug 10, 2023 at 12:41 PM

Hello Dr Timber

I went through this article and did not find any technical issues. In my view, you should reconsider what you have said.

This article is about finding an X intercept. To demonstrate this point, this context considers a practical dataset where the percentage of people still using masks is calculated when the number of Covid Cases becomes Zero. I think you expected the calculation to find X Intercept for Y = 0. That’s what is calculated in those methods. When the writer introduced us to the dataset, it was explicitly mentioned.

Anyway, I am introducing another method that may overcome your confusion. I have calculated the percentage of people who still use masks when the number of Covid Cases becomes Zero. I am using FORECAST.LINEAR Function to predict the percentage. And I get the same result shown in this article.
Excel Formula:

=FORECAST.LINEAR(D18,C5:C16,D5:D16)

The data considered in this article is not Linear. That’s why you get confused. However, these methods must be able to find an X intercept for any Linear data.

Stay safe. And good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jul 2, 2023 at 3:58 PM

Hello ANAND,

Thanks for reaching out and posting your interesting issue. The requirement you mentioned can be done with the help of an Excel VBA code. The code contains two sub-procedures named ImportVCFRecordWise and ExtractData. All you have to do is to run the ImportVCFRecordWise procedure to achieve your goal.

Excel VBA Code:

``````
Sub ImportVCFRecordWise()

Dim wb As Workbook
Dim ws, wsTemp As Worksheet
Dim filePath As String
Dim fileDialog As fileDialog

wsTemp.Name = "Temp"

Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)

fileDialog.Filters.Clear

If fileDialog.Show = -1 Then
filePath = fileDialog.SelectedItems(1)
Set wb = Workbooks.Open(Filename:=filePath)
With wb.Sheets(1)
.UsedRange.Copy wsTemp.Range("A1")
End With
wb.Close SaveChanges:=False
End If

Call ExtractData

For Each ws In ThisWorkbook.Sheets
If ws.Name = "Temp" Then
ws.Delete
Exit For
End If
Next ws

Set fileDialog = Nothing

End Sub

Sub ExtractData()

Dim ws As Worksheet
Dim outputRow As Long
Dim lastRow As Long
Dim outputSheet As Worksheet

Set ws = ThisWorkbook.Sheets("Temp")

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

outputSheet.Name = "Output"
outputSheet.Range("A1").Value = ws.Range("A2").Value
outputSheet.Range("B1").Value = ws.Range("A3").Value
outputSheet.Range("C1").Value = ws.Range("A4").Value
outputSheet.Range("D1").Value = ws.Range("A5").Value
outputSheet.Range("E1").Value = ws.Range("A6").Value
outputSheet.Range("F1").Value = ws.Range("A7").Value
outputSheet.Range("G1").Value = ws.Range("A8").Value

outputRow = 2

Dim vcardData As Variant
vcardData = ws.Range("A1:B" & lastRow).Value

Dim i As Long
For i = 1 To UBound(vcardData, 1)
If vcardData(i, 1) = "BEGIN" And vcardData(i, 2) = "VCARD" Then
outputSheet.Range("A" & outputRow).Value = vcardData(i + 1, 2)
outputSheet.Range("B" & outputRow).Value = vcardData(i + 2, 2)
outputSheet.Range("C" & outputRow).Value = vcardData(i + 3, 2)
outputSheet.Range("D" & outputRow).Value = vcardData(i + 4, 2)
outputSheet.Range("E" & outputRow).Value = vcardData(i + 5, 2)
outputSheet.Range("F" & outputRow).Value = vcardData(i + 6, 2)
outputSheet.Range("G" & outputRow).Value = vcardData(i + 7, 2)
outputRow = outputRow + 1
End If
Next i

outputSheet.UsedRange.EntireColumn.AutoFit

End Sub
``````

VCF File: The VCF file contains some raw data. I am using the data described in this article to be more specific.

Steps:

Press Alt+F11 >> insert the mentioned code >> press F5 or click on Run.

Choose the intended VCF file >> click on OK.

As a result, we will see an output like the following one.

Things to Keep in Mind:

45. 1. You have to run only the ImportVCFRecordWise procedure.
46. 2. You must delete the Output sheet if it exists before running the code.
47. This concept will assist you in reaching your goal. Good luck!

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jun 25, 2023 at 5:04 PM

Thanks for reaching out and posting your query. You mentioned using IBM Notes Version 10 as your email client. You must modify the code to work with IBM Notes instead of Outlook to achieve your goal. I am introducing you to an Excel VBA code that can send emails with the help of IBM Notes.

Excel VBA Code:

``````Sub SendEmailWithIBMNotes()

Dim ws As Worksheet
Dim i, lastRow As Long
Dim NotesApp As Object
Dim NotesMail As Object
Dim RecipientEmail As String

Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lastRow

Set NotesApp = CreateObject("Notes.NotesUIWorkspace")
Set NotesMail = NotesApp.ComposeDocument("", "", "Memo")

RecipientEmail = ws.Range("B" & i).Value

NotesMail.Document.ReplaceItemValue "SendTo", RecipientEmail
NotesMail.Document.ReplaceItemValue "Subject", "Email with IBM Notes"
NotesMail.Document.ReplaceItemValue "Body", "Greetings Everyone," & vbNewLine & "Visit our ExcelDemy page to learn more about the various aspects of Excel. You can also submit your Excel issue to our ExcelDemy Forum." & vbNewLine & "Regards."

Next i

Set NotesMail = Nothing
Set NotesApp = Nothing

End Sub``````

Things to Keep in Mind:

49. 1. You must have IBM Notes installed on your PC.
50. 2. Keep the recipient’s email throughout column B starting from B2 in Sheet1.
51. This idea helps you reach your goal. Don’t hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jun 20, 2023 at 4:44 PM

Hello DeAnna

Thanks for reaching out. To overcome the situation, I will introduce another method. Here you must convert the locations into Geography data types. Later, you will find the Lat Long values for each location. Lastly, you have to call the below User-defined function in a cell.

Excel VBA Code:

``````
Public Function GetTotalDistance2Locations(startlocation As String, endlocation As String, keyvalue As String) As Double

Dim Initial_Value As String, temp_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String
Dim distance As Double

Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
temp_Value = "&destinations="
Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = Initial_Value & startlocation & temp_Value & endlocation & Destination_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")

distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)

GetTotalDistance2Locations = distance

End Function
``````

API:

STEPS:
Select range B3:B4 >> go to Data tab >> click on Geography.

Choose cell C3 >> apply the below formula >> drag the Fill Handle to C4.

=B3.Latitude &”, “&B3.Longitude

Press Alt+F11 >> go to Insert >> click on Module >> insert the mentioned code.

Choose cell C9 >> apply the below formula.

=GetTotalDistance2Locations(C3,C4,A7)

This concept will assist you in reaching your goal. I’ve also attached the Solution Workbook to help you understand it better. Best wishes.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Jun 20, 2023 at 1:00 PM

Hi D KELLY,
Thanks for reaching out! I’d be happy to help you with your code and provide a solution. To solve this issue, we built a procedure called GetLatLong and a user-defined function named GetTotalDistance3Locations using VBA. You can input your desired locations in the GetLatLong procedure. The model calculates the total distance for three locations. Please click the link underneath this section to get a copy of the illustration workbook.
Best regards,
Lutfor Rahman Shimanto
(ExcelDemy Team)

Lutfor Rahman Shimanto May 21, 2023 at 2:18 PM

Hello PCLOUD

Greetings from our website! Thank you for sharing your question on the platform. There is a way to resolve your submitted query of finding the maximum value of the Close price in a defined period instead of the whole table. I can assist you with an Excel PowerQuery code to reach your goal.

PowerQuery Code:

``````let
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}}),
MaxClosePrice = List.Max(#"Changed Type"[Close])
in
MaxClosePrice
``````

The line MaxClosePrice = List.Max(#”Changed Type”[Close]) calculates the maximum value of the Close column in the transformed table. The result is assigned to the variable MaxClosePrice.

I hope this will achieve your goal. I am also giving you the Workbook used to investigate your issue to help you understand better. Feel free to contact us again with any other inquiries or concerns.

WORKBOOK:

Regards
Lutfor Rahman Shimanto
ExcelDemy Team

Lutfor Rahman Shimanto May 16, 2023 at 1:46 PM

Hello SAMIR

Thanks for reaching out and posting your query. Regarding your question, I can assist you with a User-defined function programmed in Excel VBA that will take three arguments. Among these arguments, the first and second will be the Latitude and Longitude of the start and end location. And the third argument must be an API Key. I am giving you an API for demonstration which should work. However, you may use your API as well as the third argument. I am attaching the Workbook used to investigate the described issue.

Excel VBA Code:

``````
Public Function GetTotalDistance2Locations(startlocation As String, endlocation As String, keyvalue As String) As Double

Dim Initial_Value As String, temp_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String
Dim distance As Double

Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
temp_Value = "&destinations="
Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = Initial_Value & startlocation & temp_Value & endlocation & Destination_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")

distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)

GetTotalDistance2Locations = distance

End Function
``````

OUTPUT:

WORKBOOK:

SOLUTION WORKBOOK

The User-defined function and API will meet your requirement. Don’t hesitate to contact us again with any other inquiries or concerns.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto May 14, 2023 at 4:47 PM

Hello DREW7STER,

Greetings from our website! Thank you for posting your question on the platform. There is a way to solve your submitted problem of finding the unique values and their frequency. I am providing an Excel VBA code compatible with several unique values, as many as you want to be more specific.

Excel VBA Code:

``````
Sub FindUniqueValues()

Dim uniqueValues As Variant
Dim i As Long
Dim j As Long
Dim freq As Long

uniqueValues = getUniqueValues(Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row))

Range("E4:F" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents

For i = LBound(uniqueValues) To UBound(uniqueValues)
freq = 0
For j = 4 To Range("B" & Rows.Count).End(xlUp).Row
If Range("B" & j).Value = uniqueValues(i) Then
freq = freq + 1
End If
Next j
Range("E" & (i + 4)).Value = uniqueValues(i)
Range("F" & (i + 4)).Value = freq
Next i

End Sub

Function getUniqueValues(rng As Range) As Variant

Dim uniqueValues() As Variant
Dim cellValue As Variant
Dim i As Long, j As Long
Dim isUnique As Boolean

ReDim uniqueValues(1 To 1)
uniqueValues(1) = rng.Cells(1, 1).Value

For i = 2 To rng.Cells.Count
cellValue = rng.Cells(i, 1).Value
If Not IsEmpty(cellValue) Then
isUnique = True
For j = 1 To UBound(uniqueValues)
If uniqueValues(j) = cellValue Then
isUnique = False
Exit For
End If
Next j

If isUnique Then
ReDim Preserve uniqueValues(1 To UBound(uniqueValues) + 1)
uniqueValues(UBound(uniqueValues)) = cellValue
End If
End If
Next i

getUniqueValues = uniqueValues

End Function
``````

I hope this will achieve your goal. Feel free to contact us again with any other inquiries or concerns.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto May 14, 2023 at 2:48 PM

Hello CAR,

It is great to see you again. I hope this reply finds you well. As you requested, I have reviewed your code and found some Syntax errors. But overall, the algorithm was close to achieving your goal.

However, I am introducing a more efficient way of doing the same task with a better algorithm that should be compatible with large datasets.

EXCEL VBA CODE:

``````

Sub MoveRowsInSheetsBasedOnValuesNew()

Dim wsSource As Worksheet, wsDest As Worksheet
Dim i As Long, lastRow As Long, destRow As Long

Set wsSource = ThisWorkbook.Worksheets("Acquisition")
lastRow = wsSource.Cells(wsSource.Rows.Count, "I").End(xlUp).Row

For i = 3 To lastRow

If wsSource.Cells(i, "I").Value = "9.Projectnumber assigned" Then

Set wsDest = ThisWorkbook.Worksheets("Projects")
destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1

wsSource.Rows(i).Copy wsDest.Rows(destRow)

ElseIf wsSource.Cells(i, "I").Value = "10.Finished" Then

Set wsDest = ThisWorkbook.Worksheets("Finished Projects")
destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1

wsSource.Rows(i).Copy wsDest.Rows(destRow)

ElseIf wsSource.Cells(i, "I").Value = "1.Cancelled" Then

Set wsDest = ThisWorkbook.Worksheets("Cancelled Rejected Proposals")
destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1

wsSource.Rows(i).Copy wsDest.Rows(destRow)

ElseIf wsSource.Cells(i, "I").Value = "2.Rejected" Then

Set wsDest = ThisWorkbook.Worksheets("Cancelled Rejected Proposals")
destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1

wsSource.Rows(i).Copy wsDest.Rows(destRow)

End If

Next i

End Sub

``````

I hope this will achieve your goal. I am also giving you the Solution workbook to help you understand better.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto May 11, 2023 at 10:27 AM

Thank you once again for your trust in us, CAR. Please do not hesitate to reach out if you need further assistance.

Regards
Team ExcelDemy

Lutfor Rahman Shimanto May 2, 2023 at 1:02 PM

Hello DOUGLAS FORMAN,

Thanks for reaching out and sharing your exciting query. When a given condition is met, we can use data bars and conditional formatting to change the color of a cell at a time. I can help you with Excel VBA code that loops through the K column and applies your given formula. Latterly, it utilizes an If Conditional to format each cell. When iterating, if the cell contains 1, that means 100%Yes” It formats the fill color of the cell as Green. On the other hand, it uses a Data Bars feature. To get a better understanding of the issue, I am going to share the Workbook used in exploring your problem.

Excel VBA Code:

``````
Sub DatabarAndConditionalFormatting()

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

Dim rng As Range
Set rng = Range("K2:K" & lastRow)

For Each cell In rng
cell.Formula = "=IFERROR(COUNTIF(B" & cell.Row & ":J" & cell.Row & ",""Yes"")/COUNTA(\$B\$1:\$J\$1),0)"
cell.NumberFormat = "0.00%"
If cell.Value = 1 Then
cell.Interior.Color = RGB(0, 255, 0)
Else
cell.FormatConditions(cell.FormatConditions.Count).ShowValue = True

With cell.FormatConditions(1).BarColor
.Color = 13012579
End With
End If
Next cell

End Sub
``````

INPUT:

OUTPUT:

WORKBOOK: Data Bars And Conditional Formatting

Feel free to contact us again with any other inquiries or concerns.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Lutfor Rahman Shimanto Apr 13, 2023 at 12:12 PM

Greetings from our website! Thank you for posting your question on the platform. As requested, I can assist you with an Excel formula using the IF and COUNTIFS functions that determine how many products a Region makes even if regardless of the product name (sometimes the product column is blank against a Region).

FORMULA:

=COUNTIFS(C5:C12, B15, D5:D12, IF(C15<>“”, C15, “”))

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Apr 13, 2023 at 11:18 AM

Hello CAR

Thank you for reaching out to us on our website. You can use a single module to move rows between sheets based on various values. You may do this by using an IF statement inside a FOR loop that iterates through the rows in Sheet1, verifies the value, and then moves the entire row to the proper destination sheet based on the value. The intended code is given below.

``````
Sub MoveRowsInSheetsBasedOnValues()

Dim wsSource As Worksheet, wsDest As Worksheet
Dim i As Long, lastRow As Long, destRow As Long

Set wsSource = ThisWorkbook.Worksheets("Sheet1")
lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

Set wsDest = ThisWorkbook.Worksheets("Sheet2")
destRow = 1

For i = 1 To lastRow
If wsSource.Cells(i, "B").Value = "A" Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row + 1
End If
Next i

Set wsDest = ThisWorkbook.Worksheets("Sheet3")
destRow = 1

For i = 1 To lastRow
If wsSource.Cells(i, "B").Value = "B" Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row + 1
End If
Next i

End Sub
``````

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Apr 6, 2023 at 11:42 AM

Hello Ruby,

Greetings from our website. You have encountered a warning message from the Microsoft Excel window. The error message “You’ve entered too few arguments for this function” means not providing enough arguments or inputs for a particular formula or function to work correctly. To resolve this error, check the formula or function you’re using and ensure you’ve provided all the required arguments.

This article explains how we can highlight cells with more than three duplicates. But, if you need help understanding this post, you can follow another article on the ExcelDemy website link given below.
How to Do Conditional Formatting in Excel [Ultimate Guide]

Later go through this article to better understand and highlight if there are more than three duplicates. Good luck.

Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Mar 30, 2023 at 3:01 PM

Greetings from our website! Thank you for posting your question on the platform. I can assist you with an Excel VBA code that creates new workbooks and allows you to specify names for those files. Furthermore, you can choose a location to store those files. Note that the user can cancel the folder selection dialogue. If they do, the code will end without making any new workbooks.

I am attaching the Workbook used to explore your issue. You can have it by clicking the link below.

INPUT:

In the sheet named Sheet1, there is a list of names.

OUTPUT:

the desired workbooks are created utilizing the Excel VBA code mentioned below.

VBA CODE:

``````Sub CreateWorkbookFromListOfCells()

Dim wb As Workbook
Dim wbNames As Variant
Dim i As Integer
Dim folderPath As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select folder to save workbooks"
If .Show = True Then
folderPath = .SelectedItems(1)
Else
Exit Sub
End If
End With

wbNames = ThisWorkbook.Sheets(1).Range("B4:B8").Value

For i = 1 To UBound(wbNames, 1)
wb.SaveAs Filename:=folderPath & "\" & wbNames(i, 1)
wb.Close
Next i

End Sub``````

NOTE:

1) I am using the Application.FileDialog method, this code asks the user to choose a folder where the new workbooks will be saved. Then, it puts the path to the selected folder in the folderPath variable.

2) Using the folderPath variable and the Workbook’s name from the wbNames array, the code saves each new Workbook with its name in the chosen folder.

3) Lastly, remember to modify the VBA code when you want to work with the other ranges. Don’t hesitate to contact us if you face any other issues.

Regards
Lutfor Rahman Shimanto (ExcelDemy Team)

Lutfor Rahman Shimanto Mar 30, 2023 at 1:26 PM

Hello Patrick,

Thank you for reaching out with your issue. You encountered a Run-time error. The “Run-time error â€˜9â€™: Subscript out of range” error occurs because you did not modify the VBA code and forgot to insert the intended sheet name. You are misled somehow by the first step of the third method. The post says to go to the Insert tab and choose Headers and Footer from the Text group, and then execute the code mentioned in the third method; that’s all.

I can provide three Excel VBA codes to avoid the error you experienced unintentionally. All you need to do is choose the intended sheet as the active sheet and run a sub-procedure from the below list.

First Method: Insert Page Number in Footer Using Excel VBA

``````Sub AddPageNumberInFooter()

ActiveWindow.View = xlPageLayoutView
ActiveSheet.PageSetup.CenterFooter = "Page &P"

End Sub``````

Second Method: VBA to Insert Total Page Number in Footer

``````Sub AddPageNumberWithTotalInFooter()

ActiveWindow.View = xlPageLayoutView
ActiveSheet.PageSetup.CenterFooter = "&P / &N"

End Sub``````

Third Method: Insert Page Number in Selected Cell

``````Sub PageNumberSelectedCell()

Dim mVCount As Integer
Dim mHCount As Integer
Dim mVBreak As VPageBreak
Dim mHBreak As HPageBreak
Dim mNumPage As Integer

mHCount = 1
mVCount = 1

ActiveWindow.View = xlPageLayoutView

If ActiveSheet.PageSetup.Order = xlDownThenOver Then
mHCount = ActiveSheet.HPageBreaks.Count + 1
Else
mVCount = ActiveSheet.VPageBreaks.Count + 1
End If

mNumPage = 1

For Each mVBreak In ActiveSheet.VPageBreaks
If mVBreak.Location.Column > ActiveCell.Column Then Exit For
mNumPage = mNumPage + mHCount
Next

For Each mHBreak In ActiveSheet.HPageBreaks
If mHBreak.Location.Row > ActiveCell.Row Then Exit For
mNumPage = mNumPage + mVCount
Next

ActiveCell = "Page " & mNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

End Sub``````

Regards
Lutfor Rahman Shimanto (ExcelDemy Team)

Lutfor Rahman Shimanto Mar 25, 2023 at 10:58 AM

Hello DYLAN,

Thank you for reaching out to us on our website. I understand that you have a value assigned to each member and are trying to split them into equal groups while ensuring each group’s total weight is within 1. Based on your requirements, I can assist you with this by applying an Excel VBA code.

``````Sub SplitIntoGroups()
Dim lastRow As Integer, totalValue As Double, group As Integer
Dim assignedValue As Double
Dim groupTotal As Double, i As Integer
Dim uniqueValues As Variant

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
groupTotal = 0
group = 1

For i = 2 To lastRow
assignedValue = Cells(i, 2).value
If groupTotal + assignedValue <= 1 Then
groupTotal = groupTotal + assignedValue
Cells(i, 3).value = "Group " & group
Else
group = group + 1
Cells(i, 3).value = "Group " & group
groupTotal = assignedValue
End If
Next i

uniqueValues = GetUniqueValues()
Range("E1").value = "Unique Group"
Range("F1").value = "Total Weighted Value"
End Sub

Function GetUniqueValues() As Variant
Dim ws As Worksheet
Set ws = ActiveSheet

Dim uniqueValues As Variant

Range("E:E").ClearContents
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
uniqueValues = Application.WorksheetFunction.Unique(ws.Range("C2:C" & lastRow))
ws.Cells(2, 5).Resize(UBound(uniqueValues), 1).value = uniqueValues

lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

Range("F:F").ClearContents
Range("F2").Formula = "=SUMIFS(\$B:\$B,\$C:\$C,\$E2)"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

GetUniqueValues = uniqueValues
End Function ``````

The above VBA code splits a list of items into groups based on their assigned value and calculates the total weighted value for each unique group. It loops through each row of the worksheet, sets each item to a group based on the total weighted value of the group, and creates a new group if the current group's total weighted value plus the contemporary item's assigned value is greater than 1. The GetUniqueValues function calculates the unique groups and their total weighted values by using the Unique and SUMIFS functions to generate an array of unique values in column C and calculate the total weighted value for each group.

1) Inserting Required Values:

2) Displaying Final Output:

Thank you again for reaching out to us, and I look forward to hearing from you soon.

Best regards,
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Mar 23, 2023 at 1:37 PM

Hello KEVIN

Greetings from our website! Thank you for posting your question on the platform. I have yet to experience the issue you described personally. However, I can offer some insights to help you troubleshoot the problem.

The circular reference error may occur because the formula references the cell containing the procedure, creating an infinite loop. To avoid this, you can modify the system to refer to a different cell that doesn’t have the formula.

Excel’s built-in Error Checking feature can help identify and resolve circular references. To access this feature, go to the Formulas tab in the Excel ribbon, click Error Checking, and then click Circular References. Excel will then highlight any circular references in your workbook and suggest resolving them.

Providing any prominent solution without glancing at the workbook is challenging. You can share your workbook with us via Exceldemy Forum to better understand your situation.

Regards
Lutfor Rahman Shimanto (ExcelDemy Team)

Lutfor Rahman Shimanto Mar 12, 2023 at 11:40 AM

Hello DAVID,
Thank you for reaching out with your comment. You encountered a different problem than what was described in the article. There could be several reasons why the problem is happening. Some of your add-ins may mess up the delete sheet feature, which may not work in your version of Microsoft Excel. To fix this, you should update your program.
You can use an Excel VBA code to delete a sheet by inputting the sheet name.
Excel VBA Code:

``````Sub DeleteSheet()
Dim sheetName As String
sheetName = InputBox("Enter the name of the sheet you want to delete:")
If sheetName <> "" Then
If WorksheetExists(sheetName) Then
Sheets(sheetName).Delete
Else
MsgBox "The workbook has no sheet with the provided name."
End If
End If
End Sub
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Object
If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
WorksheetExists = Not sht Is Nothing
On Error GoTo 0
End Function
``````

The VBA code and suggestions will solve your issue. Good luck.
Regards,
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Feb 6, 2023 at 12:00 PM

Hello AYESHA
Thanks for your query. I have analyzed the workbook Rafiul Haq shared. A prominent approach to avoid data overlapping is to create the employee record separately for each month. So don’t hesitate to make the Employee Leave description for each month.
Regards
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto Feb 5, 2023 at 4:51 PM

Hello ERICH NAGY
I appreciate your time in reading the article. I’m delighted you found the information helpful and learnt something new. Your attention to detail is really appreciated, and you are correct. The appropriate range should be the C5:C20 range instead of D5:D20. An immediate correction will be made. Once again, We appreciate your comments and support. Continue reading our blog, and we aim to offer you further helpful information soon.
Regards
Lutfor Rahman Shimanto (ExcelDemy Team)

Lutfor Rahman Shimanto Feb 5, 2023 at 3:55 PM

Hello ERIC SCHOENTHALER,
Thank you for your nice words! Great that you found our website and information helpful and relevant to your goal. I’m glad we could provide you with the information you were looking for. Regarding your question, you can use the following VBA code to insert a specific amount of page breaks starting at a particular line.
Regards
Lutfor Rahman Shimanto

``````Sub SpecificAmountOfPageBreaksWithStartingLine()
Dim Lastrow As Long
Dim Mysheet As Worksheet
Set Mysheet = Application.ActiveSheet
Row = Application.InputBox("Enter Amount of Row Number", TitleId, "", Type:=1)
NumBreaks = Application.InputBox("Enter The Number of Page Breaks", TitleId, "", Type:=1)
StartPoint = Application.InputBox("Enter The Starting Line Number", TitleId, "", Type:=1)
Mysheet.ResetAllPageBreaks
Lastrow = Mysheet.Range("B1").SpecialCells(xlCellTypeLastCell).Row
For i = StartPoint To Lastrow Step Row
If i <= StartPoint + (Row * (NumBreaks - 1)) Then
End If
Next
End Sub``````