User Posts: Al Ikram Amit
0
How to Make House Estimate Format in Excel (5 Easy Steps)
0

The house estimation allows us to create a reasonable budget, which will eventually lead to a proper path toward managing the construction of a house without ...

0
How to Make Good Excel Color Combinations
0

In the analysis and presentation of data, Excel spreadsheets are indispensable. When organizing and analyzing data, we frequently neglect the spreadsheets' ...

1
Excel VBA Worksheet Change Event Based on Multiple Cells
0

Looking for ways to use VBA worksheet change event based on multiple cells in Excel? Then, this is the right place for you. To automate a task in Excel, you ...

1
Excel VBA Set Cell Value in Another Worksheet
0

In this blog post, we'll look at how to do it, how important it is for improving the handling of data, and offer useful examples of how to set cell values ...

0
VBA Evaluate Array Formula
0

In the article, titled VBA Evaluate Array Formula, we'll look at three effective ways to use VBA's Evaluate function to efficiently manipulate data in Excel. ...

0
Excel VBA to Filter Pivot Table Based on Multiple Cell Values
0

For more accurate data analysis, Microsoft Excel VBA provides a powerful method for filtering pivot tables based on multiple cell values. We'll walk you ...

0
VBA Code to Create Pivot Table with Dynamic Range in Excel
0

In this article, you will find way to create a pivot table with dynamic range using VBA code in Excel. You can make sure that your pivot tables respond to ...

0
Excel VBA Declare Global Variable (2 Easy Ways) 
0

We'll look at the step-by-step procedure for declaring global variables in Excel VBA in this blog post. We'll go over the various approaches, like using the ...

0
Excel VBA Private Sub vs Sub (Comparison & Differences)
0

The blog post compares Private Sub and Sub in VBA (Visual Basic for Applications) programming and explains their differences. The blog post will address issues ...

0
How to Use Excel VBA INDEX MATCH from Another Worksheet
0

The topic of this Excel blog post is how to look up data from another worksheet in the same workbook using the INDEX MATCH function in VBA code. A common ...

0
Sparklines Are Not Showing in Excel (6 Simple Solutions)
0

Microsoft Excel has a useful feature called "Sparklines" that enables you to make small graphs or charts inside of individual cells to show data trends. ...

0
[Fixed!] Excel SUMIF with Wildcard Not Working (4 Solutions)
0

This article will discuss 4 possible solutions for Excel SUMIF not working with a wildcard. The SUMIF function often fails to recognize wildcard characters, ...

0
How to Concatenate Different Fonts in Excel (2 Easy Ways)
0

Many Excel users need to combine text from numerous cells into one cell where they use the Excel CONCATENATE function. However, you may want to concatenate ...

0
How to Create Dynamic Pareto Chart in Excel (with Simple Steps)
0

This Excel blog post provides a detailed discussion on how to create a dynamic Pareto chart in Excel. This kind of graph is helpful in determining the key ...

0
How to Create Navigation Buttons in Excel (2 Easy Ways)
0

When dealing with a large number of worksheets in an Excel file, we may need to include buttons on the worksheet to execute macros that let us travel back and ...

Browsing All Comments By: Al Ikram Amit
  1. Hi Gracie,

    I see you encountered an issue with the formula provided earlier. The circular reference error occurs because the formula is attempting to reference the same range (M$4:M4) it’s currently located in, which creates a circular dependency.

    To address this, I recommend using a VBA solution to find the top 5 most frequent numbers and their frequencies in the range M4:N9. VBA allows us to perform more complex calculations and avoid circular reference problems.

    To use this VBA code, press Alt+F11 to open the VBA editor in Excel. Then, click Insert>> Module to insert a new module. Copy and paste the code into the module.

    Sub FindTop5FrequentNumbers()
        Dim dataRange As Range
        Dim resultRange As Range
        Dim frequencyRange As Range
        Dim dict As Object
        Dim cell As Range
        Dim i As Integer
        Dim keyArray As Variant    
        ' Set the range that contains your sample data (E2:I57)
        Set dataRange = ThisWorkbook.Worksheets("Sheet1").Range("E2:I57") ' Adjust the sheet name and range as per your data   
        ' Set the range where you want to output the top 5 most frequent numbers (M4:M9)
        Set resultRange = ThisWorkbook.Worksheets("Sheet1").Range("M4:M9") ' Adjust the sheet name and range as per your choice   
        ' Set the range where you want to output the corresponding frequencies (N4:N9)
        Set frequencyRange = ThisWorkbook.Worksheets("Sheet1").Range("N4:N9") ' Adjust the sheet name and range as per your choice  
        ' Create a dictionary to store the frequencies of each number
        Set dict = CreateObject("Scripting.Dictionary")  
        ' Loop through each cell in the data range to count the occurrences of each number
        For Each cell In dataRange
            If IsNumeric(cell.Value) Then
                If dict.Exists(cell.Value) Then
                    dict(cell.Value) = dict(cell.Value) + 1
                Else
                    dict.Add cell.Value, 1
                End If
            End If
        Next cell  
        ' Convert the dictionary keys (numbers) to an array
        keyArray = dict.keys   
        ' Sort the array based on the frequencies in descending order
        For i = LBound(keyArray) To UBound(keyArray) - 1
            For j = i + 1 To UBound(keyArray)
                If dict(keyArray(j)) > dict(keyArray(i)) Then
                    ' Swap elements
                    temp = keyArray(i)
                    keyArray(i) = keyArray(j)
                    keyArray(j) = temp
                End If
            Next j
        Next i
        ' Output the top 5 most frequent numbers and their frequencies to the result and frequency ranges
        For i = 1 To 5
            If i <= UBound(keyArray) + 1 Then
                resultRange.Cells(i, 1).Value = keyArray(i - 1)
                frequencyRange.Cells(i, 1).Value = dict(keyArray(i - 1))
            Else
                resultRange.Cells(i, 1).Value = ""
                frequencyRange.Cells(i, 1).Value = ""
            End If
        Next i
    End Sub

    This VBA code will find the top 5 most frequent numbers in the data range E2:I57 and display them in the range M4:M9 and their corresponding frequencies in N4:N9. Remember to adjust the sheet name(We have used the sheet name “Sheet1“) and range references in the code are matched with your data.


    If you have any further questions or need more assistance, feel free to ask.

    Best regards,
    Al Ikram Amit
    Team ExcelDemy

  2. Dear KEN,

    Thank you for your question. If your data is spread across columns A, B, C, and D, you can still work out the 5 most frequent numbers and their frequencies using Excel formulas. Here’s a step-by-step approach:

    1. Assuming that your data starts from row 5, you can use the following formula in cell F5 to get the 5 most frequent numbers:

    =IFERROR(MODE(IF(COUNTIF(F$4:F4,$A$5:$D$23)={0},$A$5:$A$23)),"")

    1. To calculate the frequency of each of the top 5 most frequent numbers, you can use the following formula in cell G5:

    =COUNTIF($A$5:$D$23,F5)

    1. Copy the formulas down by dragging the fill handle for cells F5:F9 and G5:G9 to get the top 5 most frequent numbers and their frequencies.

    I hope this helps! If you have any further questions, feel free to ask.

    Best regards

    Al Ikram Amit

    Team ExcelDemy

  3. Dear KUNAR,

    Thank you for your comment. It seems that you are encountering an issue with the “Edit Credentials” prompt in Excel. This prompt usually appears when there is a need to specify the connection details or credentials for accessing external data sources. Make sure you have marked the box in the Privacy Levels window.

    If you are still experiencing issues or have any further questions, please let me know, and I’ll be happy to assist you further.

    Best regards

    Al Ikram Amit

    Team ExcelDemy

  4. Dear ASKA,

    Thank you for bringing the mistake to our attention. We apologize for the error in my previous response. You are correct, the name of the table in Excel should be “MyTable,” not “Table1”.

    I apologize for any confusion caused by the incorrect information. If you have any further questions or need assistance with any other topics, please feel free to ask.

    Best regards,

    Al Ikram Amit

    Team ExcelDemy

  5. Dear ATEEB,
    Thank you for reaching out. I’m here to help you with your Excel queries. Regarding your questions about creating alerts and blinking or flashing text in Excel, please find the answers below:
    How to create a pop-up alert in Excel

    To create a pop-up alert in Excel, you can use a combination of VBA (Visual Basic for Applications) code and Excel’s event triggers. Here’s an example code snippet that you can use:

    Copy and paste this code into the sheet module (e.g., press Alt+F11 to open the Visual Basic Editor, find the relevant sheet in the Project Explorer, and double-click on it to open its code window). Replace “$A$1” with the cell reference that should trigger the alert.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then ' Change the cell reference to the desired cell triggering the alert
            MsgBox "Alert: Cell A1 has been changed!" ' Customize the alert message as needed
        End If
    End Sub

    Customize the alert message within the MsgBox function to suit your requirements. After changing cell A1, the alert message will appear.

    How to blink or flash text of a specific cell in Excel

    Excel does not provide a built-in feature to directly make text blink or flash. However, you can achieve a similar effect by using VBA code to toggle the cell’s font color.
    In the following code, we’re toggling the font color between red and black for the specified cell (in this case, cell A1). Adjust the cell reference and customize the number of blinks and the timing (in seconds) to meet your specific requirements.
    Here’s an example code snippet that demonstrates this:

    Sub BlinkText()
    Dim rng As Range
    Set rng = Range("A1") ' Change the cell reference to the desired cell to blink
    For i = 1 To 5 ' Number of times to blink
    rng.Font.Color = RGB(255, 0, 0) ' Change font color to red
    Application.Wait Now + TimeValue("00:00:01") ' Wait for 1 second
    rng.Font.Color = RGB(0, 0, 0) ' Change font color back to black
    Application.Wait Now + TimeValue("00:00:01") ' Wait for 1 second
    Next i
    End Sub
    
    I hope these solutions address your needs. If you have any further questions or need additional assistance, please feel free to ask.
    Best regards
    Al Ikram Amit
    Team ExcelDemy
  6. Dear Fremont,

    Thank you for sharing your experience and the variation you encountered regarding frozen panes in Excel. Your observation highlights an important aspect of freezing panes, particularly when not all initial rows or columns are visible during the freezing process. In such cases, the unfrozen rows or columns that are not displayed can become inaccessible for scrolling.

    Your solution to unfreeze everything is indeed a valid approach to overcome this limitation. By unfreezing all rows and columns, you can regain full control and visibility of the entire worksheet, allowing for seamless scrolling and navigation.

    We appreciate your input and valuable contribution to the discussion. If you have any further questions or need assistance with any other topics related to Excel, please feel free to ask.

    Best regards,

    Al Ikram Amit

    Team ExcelDemy

  7. Dear A,

    Are you using mobile version of Google drive?
    The Settings option in the latest web version of Google drive is as it is in this post. It will be better if you can send a screenshot to our forum (here is our forum link: https://exceldemy.com/forum/). So we can understand what problem you are facing.

    Regards.
    Al Ikram Amit
    Team ExcelDemy

  8. Hello KP MENON,

    Greetings! We appreciate you contacting us and commenting on our Excel blog post with your query. If the VBA code is not working properly in Office 365, you can provide the Excel file here. We will try our best to give the updated version of that VBA code compatible with Office 365. As ExcelDemy is currently providing the best solutions of Excel related problems, feel free to provide your problems in the blogpost.

    Moreover, in the following section, we have provided some troubleshooting options:

    1. When there is a problem with the Excel workbook or the VBA code being executed, the “Run-time error ‘1004’: Application-defined or Object-defined error” commonly occurs. 
    2. References that are either missing or broken could be the result of improperly installed external libraries or references on the laptop running Office 365. Make sure all necessary references are present and correctly selected by checking the VBA project’s references (in the VBA editor, choose “Tools” -> “References”). If any references are missing or are flagged as “MISSING,” you might need to update or reconfigure them to work with Office 365.
    3. Another possibility is that the problem is unique to the file or the environment in which it is being run. To see if the error still occurs, try running the VBA program with Office 365 on a different laptop. 

    You might need to give more information about the VBA code and the events that preceded the error to troubleshoot the specific error more thoroughly. Reviewing the specific line of code where the error appears can also shed light on where the problem originated.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  9. Hello CALEB,

    Greetings! We appreciate you contacting us and commenting on our Excel blog post with your query. We appreciate your interest and are available to help. Enter the following VBA code in your module and correct the range in that code. Hopefully, this will implement the “Enter” function key in Excel which means the code will activate the next blank cell.

    Sub ActivateFirstBlankCell()
        Dim cell As Range
        ' Loop through each cell in the range D5:D11
        For Each cell In Range("D5:D11")
            ' Check if the cell is blank
            If cell.Value = "" Then
                ' Activate the blank cell
                cell.Activate
                Exit Sub ' Exit the loop once the first blank cell is found
            End If
        Next cell
    End Sub

    The result will be like this.

    If you enter =CHAR(13) in cell A1 and =CODE128(A1) in cell B1 in Excel, the following will happen:

    1. Enter will be represented in cell A1 and will display a carriage return character, but it will not be visibly represented. A carriage return is a non-printable control character that represents a line break or the “Enter” key.
    2. In cell B1, the formula =CODE128(A1) will attempt to encode the content of cell A1 as a Code 128 barcode. However, since the content of cell A1 is a non-printable character (carriage return), it may not be directly interpretable by the CODE128 function. The CODE128 function is typically designed to encode printable ASCII characters.

    I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  10. Hello SUERINA JUILINA,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    There are lackings in describing your particular problem. However here is the VBA code that probably can solve your particular problem:

    Sub Page_Number_Selected_Cell()
        Dim mVCount As Integer
        Dim mHCount As Integer
        Dim mVBreak As VPageBreak
        Dim mHBreak As HPageBreak
        Dim mNumPage As Integer    
        mHCount = 1
        mVCount = 1
        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   
        Dim totalPages As Integer
        totalPages = 2
        ActiveCell.Value = "Page " & mNumPage & " of " & totalPages
    End Sub

    Finally, “Page 2 of 2” is shown as a Page Number like in the following image:

    I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  11. Hello MAHIN,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    First of all, you have inserted a leading zero by any of the following means:

    • Converting a number into a Text for adding the leading zero
    • Using the TEXT function to add leading zero

    Now, there could be a few reasons why the SUM function in Excel is failing to deliver any results for your data. Here are some recommendations to aid in troubleshooting the issue:

    • The cell is previously formatted as Text. After converting them into the Number from Home tab the problem can still persist.

    • Try to copy your cells and paste them into the new destination. After pasting it in the new cells Trace Error button will appear. Click on that button and select Convert to Number.

    • Here is the final output after converting it to a number.

    • Look for hidden characters or spaces: On occasion, Excel may fail to recognize values in the cells as numbers because of hidden characters or trailing spaces. Use the CLEAN feature to get rid of any concealed characters. For instance, if your data is in cell A1 and you want to clean it up, you can use the formula “=CLEAN(A1)” in another cell, and then use the SUM function on the cleaned values.
    • Look for any mistakes in other cells: The SUM function may occasionally return null if other cells in the range you’re trying to sum include errors like #VALUE! or #DIV/0!. Examine the other cells in the range, and correct any mistakes you find.

    You should be able to fix the Excel SUM function returning null problem using the abovementioned techniques. I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

    Regards

    Al Ikram Amit

    Team ExcelDemy

  12. Hello KAZEM,

    Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

    The “subscript out of range” error frequently happens when the active sheet does not contain the table with the specified name.

    Please double-check the table name you entered into the InputBox when prompted by the tName variable. Verify again that the table name corresponds to the one on your worksheet.

    Additionally, make sure the table is on the active sheet and not on another sheet. The table is assumed to be present by the code in the active sheet.

    You can try the following troubleshooting steps if the problem continues:

    1. Make sure there are no leading or trailing spaces in the table name and that the spelling is accurate.
    2. Verify that the table is present in the current sheet and not in another sheet.
    3. Make sure the table name is distinct within the worksheet and isn’t shared with any other Excel objects (like named ranges, charts, etc.).
    4. Verify that the appropriate workbook is where you are running the code.

    You should be able to fix the “subscript out of range” error by making sure these things are correct.

    I sincerely hope you find this useful.

    Regards

    Al Ikram Amit

    Team ExcelDemy

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo