Lutfor Rahman Shimanto

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.

Now, adhere to the methods mentioned in this article. Ideally, you will observe the desired results.

Regards
Lutfor Rahman Shimanto

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

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

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:

Sub AdvancedCombinationsFor6Columns()
    
    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

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.

Follow these steps:

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

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

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.

Follow these steps:

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.

Hopefully, this idea will help you reach your goal. Good luck!

Regards
Lutfor Rahman Shimanto

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")
        .Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes
    End With
End Sub

OUTPUT:

Hopefully, the correction resolves your problem. Stay blessed.

Regards
Lutfor Rahman Shimanto

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.

Follow these Steps:

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 EquatorialRadius As Double
    EquatorialRadius = 6378137#


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

    Dim Phi1Rad As Double
    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
    N1 = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(Phi1Rad) ^ 2)

    Dim T1 As Double
    T1 = Tan(Phi1Rad) ^ 2

    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 EquatorialRadius As Double
    EquatorialRadius = 6378137#

    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.

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto

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.

So, type the Indian company names and follow the steps of this article. Good luck!

Regards
Lutfor Rahman Shimanto

Hello ADAM INGLETON

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.

Raised Error on Your End:

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

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.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set masterSheet = ThisWorkbook.Sheets.Add
    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"""

        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "Page_" & pageNumber

        ActiveWorkbook.Queries.Add Name:=queryName, Formula:=queryFormula

        With ws.ListObjects.Add(SourceType:=0, Source:= _
            "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
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = 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
    Application.DisplayAlerts = 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!
Download Solution Workbook

Regards
Lutfor Rahman Shimanto

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

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

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"
        
        imgPath = "C:\Users\Lutfor Rahman\Downloads\Insert Pictures\" & imgName
        
        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
            MsgBox "Image not found in the specified folder."
        End If
    End If

End Sub

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

Regards
Lutfor Rahman Shimanto

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

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

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

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

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

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

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
    
    Set wsTemp = Sheets.Add(After:=ActiveSheet)
    wsTemp.Name = "Temp"
    
    Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
    
    fileDialog.Filters.Clear
    fileDialog.Filters.Add "VCF Files", "*.vcf"
    
    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
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            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
    
    Set outputSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    
    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

Solution Workbook: Download the Workbook used to solve the issue.
DOWNLOAD WORKBOOK

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:

1. You have to run only the ImportVCFRecordWise procedure.

2. You must delete the Output sheet if it exists before running the code.

This concept will assist you in reaching your goal. Good luck!

Regards
Lutfor Rahman Shimanto

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:

AoCgFc5qOKVpyHuiGyPBgzDk8RgQnGGMvNqwcmtxfj7VnHEm-bpqH2GkRpoSJSAD

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.

Download Workbook

Regards
Lutfor Rahman Shimanto

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
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/MSFT?period1=1589932800&period2=1590710400&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"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:
DOWNLOAD WORKBOOK

Regards
Lutfor Rahman Shimanto
ExcelDemy Team

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

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

Dear ABDUL KADAR

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

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

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)

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)

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

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
Mysheet.HPageBreaks.Add Before:=Mysheet.Cells(i, 1)
End If
Next
End Sub