Mahfuza Anika Era

About author

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query, and Excel VBA.

Designation

Team Leader at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Civil Engineering, BUET.

Expertise

C, C++, ETABS, AutoCAD, SAP, SketchUp, Microsoft PowerPoint, Microsoft Excel, Microsoft Word, MATLAB

Experience

  • Technical Content Writing
  • Team Management
  • Voluntary Works:
    • Eccentric: Civil Engineering Festival 2018, Organized by the Department of Civil Engineering, BUET.
    • 2nd EW: B National Enviro+ challenge 2017 Organized by Environment Watch: BUET

Summary

  • Currently working as Team Leader of ExcelDemy.
  • Started technical content writing of Excel & VBA for ExcelDemy in March 2023.

Research & Publication

  • “A Study of The Relationship between Undrained Shear Strength and Preconsolidation Pressure of Normally Consolidated Clays” under Dr. Mohammed Kabirul Islam, Professor, Civil Engineering Department, BUET.

Latest Posts From Mahfuza Anika Era

0
How to Do Excel Encoding (Check and Change)

In this Excel tutorial, you will learn how to - Check the encoding of an Excel file - Change the encoding to UTF-8 using the web option - Change the ...

0
How to Delete Sheet in Excel (Single & Multiples Sheets)

In this Excel tutorial, you will learn how to - Delete a sheet in Excel - Delete multiple sheets - Use VBA to delete sheets We have used Microsoft 365 to ...

0
How to Freeze Panes in Excel (Rows/Columns/Multiple Panes)

In Excel, we use the Freeze Panes feature to lock or fix specific rows or columns in your worksheet so that they remain visible as we scroll through the rest ...

0
How to Use Advanced Pivot Table in Excel (25 Tips & Techniques)

PivotTable: Basic Things A PivotTable is a powerful data analysis tool in Microsoft Excel. It allows users to quickly summarize, organize, and gain insights ...

0
How to Return All Rows That Match Criteria in Excel

Returning all rows that match criteria in Excel means showing the rows in a dataset that meet specific conditions. For example, this is a dataset showing ...

0
Excel VBA to Comment Multiple Lines (3 Quick Steps)

If you want to comment on multiple lines in a VBA code to make the code easily understandable, this article can help you to do this in 3 simple steps. ...

1
How to Use Excel UserForm as Date Picker (with Easy Steps)

Here's a dataset of sales data for a grocery shop. In the Date & Time of Sale column, users have to enter the date and time manually. This is not ...

0
Excel VBA: InputBox with Password Mask (2 Examples)

Looking for ways to create InputBox with password mask in Excel VBA? Then, this is the right place for you. To prevent shoulder spying, you should use a ...

0
How to Calculate Stock to Sales Ratio Using Formula in Excel

In this article, I am going to explain how to calculate the Stock to sales ratio step-by-step in Excel using a formula. If you are new to Inventory Management ...

0
How to Use Excel ISBLANK to Identify Blanks in Range

Sometimes, you need to do something with a cell if it is not blank. If you are searching for a Microsoft Excel function that checks if a cell is blank or not, ...

0
How to Continue Excel VBA For Loop (with Examples)

  How to Launch a VBA Editor in Excel Click on the Developer Tab from Excel Ribbon. In case you don’t have the Developer Tab in Excel Ribbon, follow ...

0
How to Create Material Reconciliation Format in Excel

Consider the following dataset of a manufacturing company. We'll use it to demonstrate how to create a material reconciliation template.   How ...

0
How to Create Pareto Chart with Cumulative Percentage in Excel

Pareto charts are used to identify the issues to focus on first in order to make improvements. This article walks readers through a few simple steps on how to ...

0
How to Find Optimal Solution with Linear Programming in Excel

Linear Programming is very useful in practical life in terms of resource optimization. In case you are looking for detailed steps on how to find the optimal ...

Browsing All Comments By: Mahfuza Anika Era
  1. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 29, 2024 at 1:36 PM

    Hello TERRY,
    Thank you for your comment. Yes, this is possible to do with selected cells only. To do this, you can use the third method, which is using a VBA code.
    First, you have to select the cells. Then go to the Developer tab >> Visual Basic.

    Then, run the VBA code that is provided in method 3.

    A dialog box will appear, showing the selected cell range. Now, save the file by following the given instructions in method 3.
    Open the file with Notepad or Wordpad, and you will see, the selected cells are only saved with double quotes.

    Regards
    Mahfuza Anika Era
    ExcelDemy

  2. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 29, 2024 at 11:51 AM

    Hello GUILLERMO ALCALA,
    Thank you for your comment. In the initial example, Jim Brown, Robert Smith, and Henry James scored 65 in Science. So if you rank them according to the score of Science, you will get repeated ranks. But, as you can see, these 3 students got different scores in Psychology. So, we have ranked them according to the E column (Psychology). Hence, the rank is not repeated, and they got different ranks according to Psychology score.

    Regards
    Mahfuza Anika Era
    Exceldemy

  3. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 29, 2024 at 11:25 AM

    Hello RUTH MORAN,

    Thank you for your comment. Unfortunately, it is not possible to print an Excel document starting on the third page as number 1. You can leave the first page and start the page number from the second page. But the number on the second page will show “2“.

    Regards
    Mahfuza Anika Era
    Exceldemy

  4. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 28, 2024 at 2:47 PM

    Hi P.Chan,

    The scanner cannot scan or scan wrong information as the text the code is generating is wrong in your case. I have tried the code and it is working properly.

    As for why the code isn’t running properly in your system is a bit complicated to trace. For starters, try troubleshooting and retry the code again.

    Regards
    Niloy
    ExcelDemy

  5. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 28, 2024 at 1:05 PM

    Hello BRAM,
    If you are using the VBA option, the colour of the table header should be automatically changed to the previous colour right after selecting another cell. Please check if you have written the code correctly. Following is the gif for your understanding.

    If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.

    Regards,
    Mahfuza Anika Era
    Exceldemy

  6. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 5:06 PM

    Dear NAM,

    Thank you for engaging with our article and I appreciate your insightful observation. Allow me to address your query and offer clarification on the terminologies utilized in the formulas.

    In the article, the term “Coupon Rate” refers to the fixed coupon rate applied to the principal amount during the last fixed period before the bond transitions to a floating rate. Conversely, “Market Rate” refers to the variable interest rate set by the market for the floating periods.

    I acknowledge your concern regarding the terms, and I wish to explain that the Cash Flow computation entails multiplying the Principal by the Last Fixed Coupon Rate for the initial fixed period. Subsequently, for the floating periods, the Cash Flow is calculated by multiplying the Principal by the respective Market Rates for each year.

    In the Discounted Cash Flow calculation, the appropriate discount rates used are the Market Rates for every corresponding year. This aligns with the procedure of discounting future cash flows to their present value using applicable interest rates.

    I hope this clarification addresses your concerns. If you have any further questions, please feel free to let me know.

    Kind Regards,
    Sumaiya Mirza
    ExcelDemy

  7. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 3:52 PM

    Hello Roland Sprague,

    The MMULT function of Excel represents the matrix multiplication we perform in mathematics. We can use this function if there happens to be two ranges in a sheet representing two matrices and we want to multiply them.
    You can find out more about matrix multiplication in this section of the article. It is helpful for computing in linear algebra, transformation of coordinate systems, population modeling, etc.

    Regards
    Niloy
    ExcelDemy

  8. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 3:49 PM

    Dear BREANNA,

    Thank you for your positive feedback and I appreciate the opportunity to assist you further.

    To accommodate random additional payments, you can follow these steps below:

    First, insert a new column next to your dataset in column I and label it as “Additional Payment.” Next, in each row under the “Additional Payment” column, input the amount of any extra payment made for that particular month. Every month, these extra payments can be random.

    Modify the formulas in “Payment Amount” columns C, E, and G to include the additional payments. For example, update the formulas to incorporate the extra payment like this:

    =IF((D13-$E$4-$D$9-$I13)<=0,($E$4+(D13-$E$4)),($E$4+$D$9+$I13))

    Extend these adjustments to the formulas in columns D, F, and H as well ensuring the formula incorporates the additional payments. Lastly, drag the Fill Handle to AutoFill the formulas in each column for subsequent months.

    Hopefully, this solution has met your requirements. If you have any further questions, please feel free to ask.

    Kind regards,
    Sumaiya Mirza
    ExcelDemy

  9. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 3:33 PM

    Hello BRIAN,

    Thanks for your comment. You can apply two criteria by using the SUMIF function twice in the formula. The formula is:

    =SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<10000)*(SUMIF(B5:B15,B5:B15,C5:C15)>0)/COUNTIF(B5:B15,B5:B15))

    This will count the number of products whose total sum value is greater than 0 and less than 10000.

    Regards
    Mahfuza Anika Era
    ExcelDemy

  10. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 1:29 PM

    Hello ANGEL,

    Thank you for your query. Counting the sheet of the new big table (sheet1) can be ignored. Suppose, you have added a new table named “April” and you want to include it in the big table. Unfortunately, the mother table cannot be updated automatically. What you can do is to create another query after inserting a new sheet and table.

    After adding a new sheet and table, follow the same process to pull data from different sheets using power query. But the problem is the previous query will be included in your new query. To remove that:

    1.Click on the dropdown icon.

    2.Uncheck Query1 >> Press OK.

    The new query is now removed.

    3. Click on the following icon >> Expand >> OK.

    The tables are expanded now. You can remove the marked column as it is not necessary.

    4.Now, close and load the table in a new worksheet. So, the previous mother sheet (sheet 1) will not be included now.

    You have to do this process every time you add a new sheet and table.

    Regards
    Mahfuza Anika Era
    ExcelDemy

  11. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 12:13 PM

    Dear T,

    Thank you very much for your response. It means a lot to us.

    We have changed several parts of the article according to your comment. Hope you will find the article helpful now.

    Thanks
    Wasim Akram
    ExcelDemy

  12. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 11:56 AM

    Hi Zsolt!

    You are most welcome. It seems to me that you are trying to write a decimal number using a comma (,) instead of a decimal point(.). If you write the latitudes and longitudes as 42.328674 and -72.664658 instead of 42,328674 and -72,664658 the function should not return any error and you should get your expected result.

    null

    Regards,
    Nafis
    ExcelDemy

  13. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 11:48 AM

    Hello Andrea!

    You could follow these alternative solutions below to solve your problem:
    Remove any blank cells or non-date entries in the date column. Filter out non-date values and ensure that all cells in the date column contain valid date data.
    Click on the drop-down arrow in the date filter, and check the filtering options. Make sure the desired date range or specific dates are selected. Adjust the filter criteria accordingly.
    Refresh the pivot table to ensure it reflects the most recent changes in your data. Right-click on the pivot table and select Refresh.
    Create a new pivot table and see if the date filter works. If it does, the original pivot table may be corrupted. Copy your settings to the new pivot table or recreate it.
    Ensure that you are using a compatible Excel version for the features you are trying to use. Update Excel to the latest version if possible.
    If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.

    Regards,
    Nafis
    ExcelDemy

  14. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 11:46 AM

    Hi BARRY AYLETT-WARNER,

    At the beginning of Method 1, it was mentioned that “we have named the range of the ‘Fruits’ column with Fruits”, but the process wasn’t shown. Did you use the Named Range feature for your dataset? If not, first use the Named Range and then follow the steps as mentioned. Hope you find this helpful.

    Regards
    Rafiul Hasan
    ExcelDemy

  15. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 11:44 AM

    Hi Gabolete Fane,

    Glad this was helpful to you. “Balance stock” can technically become “opening stock”.
    The balance stock refers to the inventory at the end of the period while the opening stock is the inventory at the beginning of a period. So, if your next period starts right after your current period, the balance stock becomes the opening stock. It depends on the time periods you are considering.

    Regards
    Niloy
    ExcelDemy

  16. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 27, 2024 at 11:40 AM

    Hi Alonso,

    The solutions focus on working with the objects that are creating the issue. If your Excel file is frozen and you can’t perform any task, it is better to force quit the file using the Task Manager. Excel tends to autosave the files in those instances. If it doesn’t autosave, you may need to resort to third-party data recovery tools.
    In case the problem occurs every time you open the file, try repairing the file or opening it in safe mode. If all of that fails and you need to extract the data, try opening the file in other spreadsheet applications like Google Sheets. That should retrieve most of the data and avoid adding the object.

    Regards
    Niloy
    ExcelDemy

  17. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 25, 2024 at 4:23 PM

    Hello YOGESH UTEKAR,
    Thank you for your comment. To show the data for specific columns (For example: A,C,D,I,P) instead of the entire row you can use the following code.

    
    Option Explicit
    
    Sub SearchMultipleSheets()
        Dim wsh As Worksheet, ash As Worksheet
        Dim all As Range, dst As Range
        Dim What As Variant
        Dim columnRange As Range
        
        Set ash = ActiveSheet
        Set dst = ash.Range("A6")
        What = ash.Range("A4").Value
        
        Application.ScreenUpdating = False
        dst.CurrentRegion.Clear
        
        For Each wsh In Worksheets
            If wsh.Name = ash.Name Then GoTo NextSheet
            Set all = SearchAll(wsh.Columns("A"), What, LookAt:=xlWhole)
            If Not all Is Nothing Then
                Set columnRange = Union(wsh.Range("A" & all.Row), wsh.Range("C" & all.Row), wsh.Range("D" & all.Row), wsh.Range("I" & all.Row), wsh.Range("P" & all.Row)) ' Add more columns as needed
                columnRange.Copy dst.Resize(1, columnRange.Columns.Count)
                Set dst = dst.Offset(1, 0)
            End If
    NextSheet:
        Next
        
        Application.ScreenUpdating = True
    End Sub
    

    I have highlighted the portions where I have made changes.

    vba code

    Best Regards,
    Mahfuza Anika Era (ExcelDemy Team)

  18. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 25, 2024 at 12:26 PM

    Hi TRICIA,
    Thank you for your comment. To add the sales of JAN & FEB you can use this formula:

    =SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))+SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L10)*(B5:B14=L7))

    using sumproduct function

    Regards,
    Mahfuza Anika Era
    Exceldemy Team

  19. Reply Mahfuza Anika Era
    Mahfuza Anika Era Feb 25, 2024 at 11:55 AM

    Hi JAMZ,
    Thank you for your comment. There is an error in the second portion of your formula. As your week starts on Monday, you must apply the WEEKNUM function’s return type as 2. Here is the corrected formula:
    =WEEKNUM(B1,2)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1),2)+1

    using weeknum function

    Regards
    Mahfuza Anika Era
    Exceldemy

  20. Reply Mahfuza Anika Era
    Mahfuza Anika Era Dec 11, 2023 at 11:19 AM

    Hello MARIANOLI,
    Thank you so much for your comment. You can fix this problem by changing the date format in your computer’s date and time settings. Change the date format to dd/MM/yy. Hopefully, you will get the dd/mm/yy format for the whole month.

    date and time settings

    Regards
    Mahfuza Anika Era
    ExcelDemy

  21. Reply Mahfuza Anika Era
    Mahfuza Anika Era Dec 6, 2023 at 10:24 AM

    Dear NURIT,
    Thank you so much for your comment. I have changed the date format to United States format in the code. You have to change the VBA code in the subroutine named Private Sub Create_Calender(). Following is the code for your required date format.

    
    Private Sub Create_Calender()
    For i = 1 To 42
    
    If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
    Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
    
    Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
    
    ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
    Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
    
    Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
    End If
    
    If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
    If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
    Controls("C" & (i)).Font.Bold = True
    
    If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
    ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy") = Format(This_Day, "m/d/yy") Then Controls("C" & (i)).SetFocus
    Else
    If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
    Controls("C" & (i)).Font.Bold = False
    End If
    
    Next
    End Sub 

    Here is the screenshot of the new code. I have marked the changes for your better understanding.

    userform datepicker

    Regards
    Mahfuza Anika Era
    ExcelDemy

  22. Reply Mahfuza Anika Era
    Mahfuza Anika Era Oct 5, 2023 at 11:01 AM

    Dear Ahmad,
    Thanks for your query.
    In the VLOOKUP function #N/A error occurs when you type the wrong lookup value or worksheet name. So, check if you have given the lookup value and worksheet name properly.
    Again, VLOOKUP can only look up values to the right of the lookup value. Be careful about this.
    If you need further help, please mention details about your problem.

    Regards
    Mahfuza Anika Era
    ExcelDemy

  23. Reply Mahfuza Anika Era
    Mahfuza Anika Era Aug 28, 2023 at 5:29 PM

    Dear Salad,
    Thanks for your question. Here is the VBA code that will give you your mentioned output.

    Function NumberToWords(ByVal MyNumber)
        Dim TempStr As String
        Dim DecimalPlace As Integer
        Dim Count As Integer
        Dim DecimalSeparator As String
        Dim UnitName As String
        Dim SubUnitName As String
        Dim SubUnitValue As String
        Dim DecimalName As String
        Dim WholeNumberPart As String
        Dim DecimalPart As String
    
        DecimalSeparator = "point"
        UnitName = "Dollars"
        SubUnitName = "Cents"
        SubUnitValue = " "
        DecimalName = "Zero"
    
        MyNumber = Trim(Str(MyNumber))
    
        DecimalPlace = InStr(MyNumber, ".")
    
        If DecimalPlace > 0 Then
            DecimalPart = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
        End If
    
        Count = 1
        Do While MyNumber <> ""
            TempStr = GetHundreds(Right(MyNumber, 3))
            If TempStr <> "" Then
                WholeNumberPart = TempStr & GetUnits(Count) & WholeNumberPart
            End If
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
            Count = Count + 1
        Loop
    
        NumberToWords = WholeNumberPart & IIf(DecimalPart <> "", " and " & DecimalSeparator & " " & DecimalPart & " " & SubUnitName & " " & SubUnitValue, "")
    End Function
    
    Function GetUnits(ByVal Count)
        Select Case Count
            Case 1
                GetUnits = ""
            Case 2
                GetUnits = " Thousand"
            Case 3
                GetUnits = " Million"
            Case 4
                GetUnits = " Billion"
            Case 5
                GetUnits = " Trillion"
            Case Else
                GetUnits = ""
        End Select
    End Function
    
    Function GetHundreds(ByVal MyNumber)
        Dim Result As String
        If Val(MyNumber) = 0 Then Exit Function
        MyNumber = Right("000" & MyNumber, 3)
        If Mid(MyNumber, 1, 1) <> "0" Then
            Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
        End If
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & GetTens(Mid(MyNumber, 2))
        Else
            Result = Result & GetDigit(Mid(MyNumber, 3))
        End If
        GetHundreds = Result
    End Function
    
    Function GetTens(TensText)
        Dim Result As String
        Result = ""           ' Null out the temporary function value.
        If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
            Select Case Val(TensText)
                Case 10: Result = "Ten"
                Case 11: Result = "Eleven"
                Case 12: Result = "Twelve"
                Case 13: Result = "Thirteen"
                Case 14: Result = "Fourteen"
                Case 15: Result = "Fifteen"
                Case 16: Result = "Sixteen"
                Case 17: Result = "Seventeen"
                Case 18: Result = "Eighteen"
                Case 19: Result = "Nineteen"
                Case Else
            End Select
        Else                                 ' If value between 20-99...
            Select Case Val(Left(TensText, 1))
                Case 2: Result = "Twenty "
                Case 3: Result = "Thirty "
                Case 4: Result = "Forty "
                Case 5: Result = "Fifty "
                Case 6: Result = "Sixty "
                Case 7: Result = "Seventy "
                Case 8: Result = "Eighty "
                Case 9: Result = "Ninety "
                Case Else
            End Select
            Result = Result & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
        End If
        GetTens = Result
    End Function
    
    Function GetDigit(Digit)
        Select Case Val(Digit)
            Case 1: GetDigit = "One"
            Case 2: GetDigit = "Two"
            Case 3: GetDigit = "Three"
            Case 4: GetDigit = "Four"
            Case 5: GetDigit = "Five"
            Case 6: GetDigit = "Six"
            Case 7: GetDigit = "Seven"
            Case 8: GetDigit = "Eight"
            Case 9: GetDigit = "Nine"
            Case Else: GetDigit = ""
        End Select
    End Function
    End If

    Following is the output after using the code.

    convert to currency using VBA

    Regards
    Mahfuza Anika Era
    ExcelDemy

  24. Reply Mahfuza Anika Era
    Mahfuza Anika Era Aug 21, 2023 at 2:03 PM

    Dear MARK,
    Thank you for your query.
    Here is the dataset I will use to show the solution to your problem.

    dataset

    After creating a PivotTable, I have copied the PivotTable 5 times. So, there are 5 PivotTables in my worksheet now.

    PivotTable

    Now, we have to create a drop-down menu from the list of PivotTables.

    data validation

    Next, copy this VBA code into your VBA code editor. You have to change three things in this code. These are: the cell address of where you placed the drop-down menu, the filter values, and the field name that you want to filter.

    Sub Apply_Filter_PivotTable()
        Dim pivotTableName As String
        Dim pivotTable As pivotTable
        Dim field As PivotField
        Dim filterValue As String
        
        ' Change the cell address to where you placed the dropdown menu
        pivotTableName = Range("A2").Value
        filterValue = "Cash" ' Change this to the desired filter value
        
        On Error Resume Next
        Set pivotTable = ActiveSheet.PivotTables(pivotTableName)
        On Error GoTo 0
        
        If Not pivotTable Is Nothing Then
            ' Change "Field Name" to the name of the field you want to filter
            Set field = pivotTable.PivotFields("Payment Method")
            
            field.ClearAllFilters
            field.CurrentPage = filterValue
        Else
            MsgBox "Pivot table not found."
        End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$2" Then ' Change to the address of your dropdown cell
            ApplyPivotTableFilter
        End If

    VBA code

    To get the output, select the PivotTable from the drop-down which you want to filter, and then Run the code by pressing the F5 key.
    For your convenience, I have given the Excel file: Filtering PivotTable with drop-down menu.xlsm

    Regards
    Mahfuza Anika Era
    ExcelDemy

  25. Reply Mahfuza Anika Era
    Mahfuza Anika Era Jul 30, 2023 at 11:29 AM

    Dear Stuart,
    I am glad that you find this article informative. Thank you for your query. The VBA code which I have inserted in step 4 is in Sheet1 under Microsoft Excel Objects Section.

    VBA code location in workbook

    Mahfuza Anika Era
    ExcelDemy

  26. Reply Mahfuza Anika Era
    Mahfuza Anika Era Jul 25, 2023 at 12:51 PM

    Dear Yosh,
    Thank you for your query. Yes, you can determine the sum of YTD number. Firstly create a table like the following for each Month’s Sales of “Jimmy” for “Laptop”.
    Copy this formula in cell C21.
    =SUMIFS(INDEX($D$5:$I$16,,MATCH(B21,$D$4:$I$4,0)),$B$5:$B$16,$B$20,$C$5:$C$16,C$20)
    Sales for each Month
    You will get the sales for each month.
    Next, to calculate the “YTD Grandtotal” copy this formula in cell C27.
    =SUM(C21:C26)
    Subtotal of YTD
    I hope this method will solve your problem. Thank you!
    Mahfuza Anika Era
    ExcelDemy

  27. Reply Mahfuza Anika Era
    Mahfuza Anika Era Jul 25, 2023 at 12:13 PM

    Dear Yosh,
    I assume, this question is same as the previous one. You can follow the steps I have given in the previous reply. If you still have any confusion, please leave a comment describing your problem. Thank you!
    Mahfuza Anika Era
    ExcelDemy

  28. Reply Mahfuza Anika Era
    Mahfuza Anika Era May 14, 2023 at 5:12 PM

    Dear L,
    Thank you for your comment. This formula calculates a date value. Here is a breakdown of what each part of the formula does:

    =$B$4-(WEEKDAY(C$3,1)-($AM$7-1))-IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0)+1

    $B$4: This is the reference to cell B4 which contains the date value you enter in Customize Dates Table.
    WEEKDAY(C$3,1): This function calculates the weekday of the date in cell C3. The argument 1 specifies that the weekday numbering should start on Monday (1) instead of Sunday (default value of 0).
    $AM$7: This is a reference to the cell containing a number that specifies the Start Month. (e.g. 1 for Monday, 7 for Sunday).
    IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0): This function checks whether the weekday of the date in cell B4 is earlier in the week than the specified start day of the week. If it is, the function returns 7 (the number of days in a week) to adjust the date calculation later. If not, the function returns 0.
    B4-(WEEKDAY(C3,1)-($AM$7-1))-IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0)+1: This formula subtracts the weekday of the date in cell C3 from the date in cell B4, then adds the start day of the week minus 1, and finally subtracts the result of the IF function. This calculates the first day of the week that contains the date in cell B4. The final +1 adds one day to get the actual start date of the week.
    If you want 4 weeks in one sheet, just add 3 more weeks similarly in the existing sheet. Hope this will help you.
    Regards
    Mahfuza Anika Era
    ExcelDemy

  29. Reply Mahfuza Anika Era
    Mahfuza Anika Era May 11, 2023 at 4:51 PM

    Dear Max,
    Thank you for your query. Changing the currency is not the reason behind the incorrect output of the module 2 code. Actually, the code is not working for the last 3 digits of the whole number part. Here is the modified code of module 1 that may help you. This will give the correct result hopefully.

    Function number_converting_into_currency(ByVal MyNumber)
    Dim x_string As String
    Dim whole_num As Integer
    Dim x_string_pnt
    Dim x_string_Num
    Dim x_pnt As String
    Dim x_numb As String
    Dim x_P() As Variant
    Dim x_DP
    Dim x_cnt As Integer
    Dim x_output, x_T As String
    Dim x_my_len As Integer
    On Error Resume Next
    x_P = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
    x_numb = Trim(Str(MyNumber))
    x_DP = InStr(x_numb, ".")
    x_pnt = ""
    x_string_Num = ""
    If x_DP > 0 Then
    x_pnt = " "
    x_string = Mid(x_numb, x_DP + 1)
    x_string_pnt = Left(x_string, Len(x_numb) - x_DP)
    For whole_num = 1 To Len(x_string_pnt)
    x_string = Mid(x_string_pnt, whole_num, 1)
    x_pnt = x_pnt & get_digit(x_string) & " "
    Next whole_num
    x_numb = Trim(Left(x_numb, x_DP - 1))
    End If
    x_cnt = 0
    x_output = ""
    x_T = ""
    x_my_len = 0
    x_my_len = Int(Len(Str(x_numb)) / 3)
    If (Len(Str(x_numb)) Mod 3) = 0 Then x_my_len = x_my_len - 1
    Do While x_numb <> ""
    If x_my_len = x_cnt Then
    x_T = get_hundred_digit(Right(x_numb, 3), False)
    Else
    If x_cnt = 0 Then
    x_T = get_hundred_digit(Right(x_numb, 3), True)
    Else
    x_T = get_hundred_digit(Right(x_numb, 3), False)
    End If
    End If
    If x_T <> "" Then
    x_output = x_T & x_P(x_cnt) & x_output
    End If
    If Len(x_numb) > 3 Then
    x_numb = Left(x_numb, Len(x_numb) - 3)
    Else
    x_numb = ""
    End If
    x_cnt = x_cnt + 1
    Loop
    If x_DP > 0 Then
    x_output = x_output & "dollars" & x_pnt & "Cents"
    Else
    x_output = x_output & "dollars"
    End If
    number_converting_into_currency = x_output
    End Function
    Function get_hundred_digit(xHDgt, y_b As Boolean)
    Dim x_R_str As String
    Dim x_string_Num As String
    Dim x_string As String
    Dim y_I As Integer
    Dim y_bb As Boolean
    x_string_Num = xHDgt
    x_R_str = ""
    On Error Resume Next
    y_bb = True
    If Val(x_string_Num) = 0 Then Exit Function
    x_string_Num = Right("000" & x_string_Num, 3)
    x_string = Mid(x_string_Num, 1, 1)
    If x_string <> "0" Then
    x_R_str = get_digit(Mid(x_string_Num, 1, 1)) & "Hundred "
    Else
    If y_b Then
    x_R_str = "and "
    y_bb = False
    Else
    x_R_str = " "
    y_bb = False
    End If
    End If
    If Mid(x_string_Num, 2, 2) <> "00" Then
    x_R_str = x_R_str & get_ten_digit(Mid(x_string_Num, 2, 2), y_bb)
    End If
    get_hundred_digit = x_R_str
    End Function
    Function get_ten_digit(x_TDgt, y_b As Boolean)
    Dim x_string As String
    Dim y_I As Integer
    Dim x_array_1() As Variant
    Dim x_array_2() As Variant
    Dim x_T As Boolean
    x_array_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
    x_array_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
    x_string = ""
    x_T = True
    On Error Resume Next
    If Val(Left(x_TDgt, 1)) = 1 Then
    y_I = Val(Right(x_TDgt, 1))
    If y_b Then x_string = "and "
    x_string = x_string & x_array_1(y_I)
    Else
    y_I = Val(Left(x_TDgt, 1))
    If Val(Left(x_TDgt, 1)) > 1 Then
    If y_b Then x_string = "and "
    x_string = x_string & x_array_2(Val(Left(x_TDgt, 1)))
    x_T = False
    End If
    If x_string = "" Then
    If y_b Then
    x_string = " "
    End If
    End If
    If Right(x_TDgt, 1) <> "0" Then
    x_string = x_string & get_digit(Right(x_TDgt, 1))
    End If
    End If
    get_ten_digit = x_string
    End Function
    Function get_digit(xDgt)
    Dim x_string As String
    Dim x_array_1() As Variant
    x_array_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
    x_string = ""
    On Error Resume Next
    x_string = x_array_1(Val(xDgt))
    get_digit = x_string
    End Function

    Copy the code in your module and Run the code.

    I hope you have got your problem solved. Thank you.
    Regards
    Mahfuza Anika Era
    ExcelDemy

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo