User Posts: Lutfor Rahman Shimanto
0
How to Create 3D Scatter Plot in Excel (with Easy Steps)
0

In this article, we'll explore 3D scatter plot in Excel. You will learn a step-by-step procedure to display a 3D scatter chart. Especially these graphs uncover ...

0
How to Apply Cubic Spline Interpolation in Excel (with Easy Steps)
0

When it comes to software, Microsoft Excel is in a league of its own. Thanks to its many useful features, we may fully use any data. This article will cover ...

0
Opportunity Cost Calculator in Excel (3 Suitable Examples)
0

One of the most helpful software you can use is Microsoft Excel. It is possible to do an endless number of things with a dataset by utilizing Excel's ...

0
Number Format Is Not Working in Excel (2 Reasons with Solutions)
0

Microsoft Excel is considered one of the most valuable tools currently accessible. With Excel's tools and capabilities, it is feasible to perform an endless ...

0
Min Max Inventory Calculation in Excel (with Easy Steps)
0

Microsoft Excel is one of the most beneficial programs you can use. Using Excel's features and tools, you can do an almost infinite number of things with a ...

0
How to Use LOOKUP Function Among Multiple Sheets in Excel
0

Microsoft Excel is a helpful program. You can conduct infinite operations on a dataset using Excel's tools and capabilities. Regularly, we must search for data ...

0
How to Use NOT and ISNA Functions in Excel (2 Examples)
0

The tool known as Microsoft Excel is quite helpful. You can execute an infinite number of operations on a dataset using Excel's tools and capabilities. When ...

0
Nested IF and ISERROR Formula in Excel (2 Practical Examples)
0

Microsoft Excel is a practical application. With Excel's tools and capabilities, you can perform an endless number of operations on a dataset. Frequently, we ...

0
How to Interpolate Time Series in Excel (3 Easy Ways)
0

Microsoft Excel is an intuitive tool that more than lives up to its excellent reputation. Thanks to Excel's tools and functions, we can perform various actions ...

0
How to Create Rainflow Counting Algorithm in Excel
1

Microsoft Excel is one of the most valuable tools available. Using Excel's capabilities and tools, a dataset can be utilized in an almost limitless way. This ...

0
How to Use IF Statement with Not Equal To Operator in Excel
0

Microsoft Excel is a practical application. With Excel's tools and capabilities, you can perform an endless number of operations on a dataset. Frequently, we ...

0
How to Plot Histogram with Unequal Class Intervals in Excel
0

Microsoft Excel is one of the most useful software you can get. Using Excel's features and tools, it is possible to do an infinite number of things with a ...

0
[Fixed!] Drop Down List Ignore Blank Not Working in Excel
0

Microsoft Excel is an intuitive tool that more than lives up to its illustrious reputation. We can perform many actions on a dataset thanks to Excel's tools ...

0
How to Count Frequency of Unique Values in a Column in Excel
0

Excel by Microsoft is a user-friendly program that more than lives up to its stellar reputation. We can do various operations on a dataset thanks to Excel's ...

0
Return Non Blank Cells from a Range in Excel (3 Suitable Ways)
0

Microsoft Excel is, in fact, a beneficial program. We can do many things with a given dataset using Excel's tools and features. In Excel, we often need to ...

Browsing All Comments By: Lutfor Rahman Shimanto
  1. Reply Avatar photo
    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

  2. Reply Avatar photo
    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.
    converting into text format
    Now, adhere to the methods mentioned in this article. Ideally, you will observe the desired results.
    The desired output with leading-zero
    Regards
    Lutfor Rahman Shimanto

  3. Reply Avatar photo
    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

  4. Reply Avatar photo
    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.
    Loading the cvs data
    As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.
    Choosing the Existing worksheet from the Import Data window
    Thus, you will be able to solve the problem.
    Regards
    Lutfor Rahman Shimanto

  5. Reply Avatar photo
    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.
    https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx
    Best regards,
    Lutfor Rahman Shimanto

  6. Reply Avatar photo
    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

  7. Reply Avatar photo
    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

  8. Reply Avatar photo
    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.

    Open the VBA editor

    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
    

    Insert the intended code and Run

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

    Output of running VBA code

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

    Regards
    Lutfor Rahman Shimanto

  9. Reply Avatar photo
    Lutfor Rahman Shimanto Sep 12, 2023 at 5:24 PM

    Hello Nadeem Khatri

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

    Select cell E5 and apply the given formula

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

    Regards
    Lutfor Rahman Shimanto

  10. Reply Avatar photo
    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:

    Click on the CommandButton1 and see the result in cell B10

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

    Regards
    Lutfor Rahman Shimanto

  11. Reply Avatar photo
    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.

    Right-click on sheet name tab and go to 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
    

    Paste code in sheet module and Save

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

    insert a date value with valid date format

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

    Final output of formatting date values to dd-mm-yyyy

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

    Regards
    Lutfor Rahman Shimanto

  12. Reply Avatar photo
    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

  13. Reply Avatar photo
    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

  14. Reply Avatar photo
    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

  15. Reply Avatar photo
    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

  16. Reply Avatar photo
    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

  17. Reply Avatar photo
    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)

    Finding X Intercept with Article Dataset

    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.

    X Intercept with Linear Data

    Stay safe. And good luck!

    Regards
    Lutfor Rahman Shimanto

  18. Reply Avatar photo
    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
        
        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.

    VCF File Contents

    Steps:

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

    VBA Editor window

    Choose the intended VCF file >> click on OK.

    Select VCF file using file dialog

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

    Final Output importing VCF file

    Things to Keep in Mind:

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

    Regards
    Lutfor Rahman Shimanto

  22. Reply Avatar photo
    Lutfor Rahman Shimanto Jun 25, 2023 at 5:04 PM

    Hello ASAD

    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:

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

    Regards
    Lutfor Rahman Shimanto

  26. Reply Avatar photo
    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:

    AoCgFc5qOKVpyHuiGyPBgzDk8RgQnGGMvNqwcmtxfj7VnHEm-bpqH2GkRpoSJSAD

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

    Geography Data Types

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

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

    Lat Long Values

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

    VBE Module

    Choose cell C9 >> apply the below formula.

    =GetTotalDistance2Locations(C3,C4,A7)

    Calculate Distance

    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

  27. Reply Avatar photo
    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.
    Download Workbook
    Best regards,
    Lutfor Rahman Shimanto
    (ExcelDemy Team)

  28. Reply Avatar photo
    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
        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

  29. Reply Avatar photo
    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:

    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

  30. Reply Avatar photo
    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

  31. Reply Avatar photo
    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.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto

  32. Reply Avatar photo
    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

  33. Reply Avatar photo
    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.AddDatabar
                cell.FormatConditions(cell.FormatConditions.Count).ShowValue = True
                
                 With cell.FormatConditions(1).BarColor
                    .Color = 13012579
                    .TintAndShade = 0
                End With
            End If
        Next cell
    
    End Sub
    

    INPUT: INPUT-DOUGLAS FORMAN

    OUTPUT: OUTPUT-DOUGLAS FORMAN

    WORKBOOK: Data Bars And Conditional Formatting

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

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  34. Reply Avatar photo
    Lutfor Rahman Shimanto Apr 13, 2023 at 12:12 PM

    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

  35. Reply Avatar photo
    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

  36. Reply Avatar photo
    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

  37. Reply Avatar photo
    Lutfor Rahman Shimanto Mar 30, 2023 at 3:01 PM

    Hello PRACHI DABHADE

    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.

    Download Workbook:

    I am attaching the Workbook used to explore your issue. You can have it by clicking the link below.
    https://www.exceldemy.com/wp-content/uploads/2023/03/PRACHI-DABHADE.xlsm

    INPUT:

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

    A list of names to create new workbooks

    OUTPUT:

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

    Output of creating workbook from a list of cells

    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)
            Set wb = Workbooks.Add
            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)

  38. Reply Avatar photo
    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)

  39. Reply Avatar photo
    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:
    Inserting Intended Values

    2) Displaying Final Output:
    Final Output

    3) Download Practice Workbook
    https://www.exceldemy.com/wp-content/uploads/2023/03/Dylans-Request.xlsm

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

    Best regards,
    Lutfor Rahman Shimanto

  40. Reply Avatar photo
    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)

  41. Reply Avatar photo
    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
                Application.DisplayAlerts = False
                Sheets(sheetName).Delete
                Application.DisplayAlerts = True
            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

  42. Reply Avatar photo
    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

  43. Reply Avatar photo
    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)

  44. Reply Avatar photo
    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
    Mysheet.HPageBreaks.Add Before:=Mysheet.Cells(i, 1)
    End If
    Next
    End Sub
  45. Reply Avatar photo
    Lutfor Rahman Shimanto Feb 2, 2023 at 5:27 PM

    Thank you so much for sharing this tip, Andy! I sincerely appreciate the time you took to share your expertise and assist others with this problem. Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!
    Regards
    Lutfor Rahman Shimanto

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo