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