User Posts: Rafiul Haq
0
Excel Exercises for Students with Answers
0

In this article, you will get ten Excel exercises for students with answers. All of the problems are beginner friendly. In order to solve the problems, you ...

0
Excel VLOOKUP Exercises: 10 Tricky Problems
0

In this article, you will get ten Excel VLOOKUP exercises to practice. Most of the problems are easy to solve. However, you will need a little bit of ...

0
Excel VBA Error Handling Best Practice
0

In this article, we are going to discuss the best practice for Excel VBA error handling. One will need advanced knowledge of Excel to understand the ...

0
Best Practices for Linking Excel Spreadsheets
0

In this article, you will learn about best practices for linking Excel spreadsheets. This is very easy to understand; a basic understanding of Excel will be ...

0
Advanced Excel Exercises with Solutions PDF
0

In this article, you will find advanced Excel exercises with solutions in PDF format. You need to possess advanced Excel knowledge to solve these problems. You ...

1
Excel Data Entry Practice Exercises PDF
0

In this article, you will solve four practice Excel exercises in data entry, which will be provided in PDF format. Additionally, you will get an Excel file ...

0
Excel Data for Pivot Table Practice
0

In this article, you will get an Excel data file for pivot table practice. There will also be seven practice tasks. If you have intermediate level knowledge of ...

0
Nested IF Function Practice Exercises in Excel
0

In this article, you will get an Excel file to with practice exercises using the nested IF function. There are four exercises for you to solve. The problems ...

0
Excel Data for Practice Free Download
0

In this article, you can download an Excel file with data for practice for free. In this file, there are five questions for you to solve. The first four ...

0
Excel Practice Exercises PDF with Answers
0

In this article, we will provide you with 11 Excel practice exercises in PDF format with answers. Additionally, you will get an Excel file where you can try to ...

0
What Is the Best Practice for Financial Modeling in Excel?
0

In this article, you will learn about the best practice for financial modeling in Excel. In order to comprehend this article, you need to possess some basic ...

1
Sample Excel File with Employee Data for Practice
0

In this example, you will get a sample Excel file with employee data for practice. The problems are beginner friendly, with a little bit of knowledge about ...

0
How to Convert Green Triangle to Number in Excel (6 Easy Ways)
0

A green triangle will appear in the top-left corner of the cell when Excel detects an error in any cell. In the case of a number, this appears when that number ...

0
How to Set Column Width in Inches in Excel (with Easy Steps)
0

We frequently need to adjust the column width in Excel. In this article, you will learn about two quick steps for how to set column width in inches in Excel. ...

0
How to Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)
0

An unix timestamp indicates how much time has passed since January 1, 1970 (epoch time). When the timestamp has 11 digits, it displays seconds; when it has 13 ...

Browsing All Comments By: Rafiul Haq
  1. Hello Robin. This code should work for you. It will make the image fit in the output cell, keep the aspect ratio constant, and the image will not disappear.

    Option Explicit
    Sub URLPhotoInsert2()
    Dim cPhoto As String
    Dim cPicture As Shape
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5:C10")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then GoTo line33
            Set cPicture = ActiveSheet.Shapes.AddPicture(cPhoto, _
            msoFalse, msoTrue, Columns(cItem.Column).Left, _
            Rows(cItem.Row).Top, -1, -1)
        If cPicture.Width > cItem.Width Then cPicture.Width = cItem.Width
        If cPicture.Height > cItem.Height Then cPicture.Height = cItem.Height
            With cPicture
                .Top = Rows(cItem.Row).Top + (Rows(cItem.Row).Height - .Height) / 2
                .Left = Columns(cItem.Column).Left + (.TopLeftCell.Width - .Width) / 2
                .LockAspectRatio = msoTrue
                .Placement = xlMoveAndSize
            End With
    line33:
             cPhoto = ""
        Next
    End Sub
  2. Hello Danielle D, Thank you for your question. For the first question, you can add a line break using the CHAR function. CHAR(10) to be more specific. There are some feature limitations in the HYPERLINK method. You need to use the other methods to do more advanced stuff.

    Then, for the second question, if you send your Excel file to [email protected], we will try to modify the VBA code according to your needs.

  3. Hello Bill Allcock, Excel formulas can’t move data. So, we’re making a copy of the value. You need to use VBA to do so. Moreover, you can send us your sample Excel file to [email protected] and we will try to solve your problem using VBA.

  4. Hello Andreas, you need to add the following line to ignore the error.

    On Error Resume Next
  5. Thank you Anthony for your question. Let’s assume you have this data in 4 sheets and you want to copy the rows that has conflict.

    Now, the output should contain only the “yes” from the 4 sheets:

    To copy values from 4 sheets to a new sheet, use the following VBA code

    Option Explicit
    Sub Copy_Conflict_Values_New_Sheet()
    Dim xRange As Range
    Dim End_Row, SheetNumber As Integer
    Worksheets("Output").Range("X5:Z100").ClearContents
    For SheetNumber = 1 To 4
    Worksheets(SheetNumber).Activate
        For Each xRange In Range("Z4:Z" & Cells(Rows.Count, "Z").End(xlUp).Row)
        End_Row = Worksheets("Output").Range("Z" & Worksheets("Output").Rows.Count).End(xlUp).Row
        If xRange.Value = "Yes" Then
                Rows(xRange.Row).Copy Destination:=Worksheets("Output").Rows(End_Row + 1)
                End_Row = End_Row + 1
        End If
        Next
    Next SheetNumber
    End Sub
  6. Paul, we appreciate your analysis. That 1 represents column_number, which is an optional argument. You can omit that in the original formula and it will still return Ricky Ben.

    =INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0),0),1)

  7. Thanks for your formula.
    Just a reminder, if anyone wants to copy and paste this formula, the quotes need to be straight (“”), not curly (“”). Otherwise, they will get the #N/A error.

    1. Excel 365
    =FIND(MID(C5,LOOKUP(9^9,FIND(“/”,C5,SEQUENCE(LEN(C5)))),9^9),C5)

    2. Earlier Versions
    =FIND(MID(C5,LOOKUP(9^9,FIND(“/”,C6,ROW(1:100))),9^9),C5)

  8. You can use this code to keep the image proportion.

    Sub Keeping_Aspect_Ratio_Constant()
    Dim cPhoto As String
    Dim cPicture As Picture
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5:C7")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then GoTo line33
        Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
            With cPicture
            .ShapeRange.LockAspectRatio = msoTrue
            .Width = cItem.Width
            If .Height > cItem.Height Then .Height = cItem.Height
            .Top = Rows(cItem.Row).Top + (Rows(cItem.Row).Height - .Height) / 2
            .Left = Columns(cItem.Column).Left + (.TopLeftCell.Width - .Width) / 2
            .Placement = xlMoveAndSize
            End With
    line33:
             cPhoto = ""
    Next
    End Sub
    

    Here is the output.

  9. Hello Claudiu, thank you for your question. The following steps will execute the VBA code whenever you change the dates.

    Press ALT+F11 to bring up the VBA window. Then, right click on “Sheet1” and select View Code.

    Then, type the following VBA code. This code will call the SendReminderMail Sub whenever, a value changes in the cell range D5:D7.

    Private Sub Worksheet_Change(ByVal xTarget As Range)
    If Not Intersect(xTarget, Range("D5:D7")) Is Nothing Then
    Call SendReminderMail
    End If
    End Sub

    After that, we’ve set the cell ranges instead of using the input box. For example, the code for xDueDate is changed to:

    Set XDueDate = Range("D5:D7")

    Then, when you change the date, it will automatically execute the macro.

    However, if this doesn’t solve your problem, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

  10. Thank you, Dan, for your comment. If the values are in text format, then the COUNTIF function will return zero. You can see that we have used a IF formula, which returns TRUE when the value from column B is greater than five. Notice the output is in text format.

    • Now, if we want to count the number of FALSE in the column C, it will return zero.

    • There are two solutions for this issue. Firstly, you can add asterisks (*) with the criteria.

    • Another way is to change the formula in column C to return TRUE or FALSE as Boolean values.

    • Then, the COUNTIF function will return the correct value.

    However, if this doesn’t solve your problem, you can mail us your Excel file to: [email protected], and we’ll try to solve it as soon as possible.

  11. Hello Cielo. Thank you for your question. I have tested the function on Excel 365, 2021, and 2013 versions. On first two version, you simply press ENTER and the formula will be spilled. However, in Excel 2013 if you press that, it will only show the first value.

    Here, we have typed the formula in cell C5.

    Then, pressed CTRL+SHIFT+ENTER. But, only the first value is seen.

    To solve this, you need to follow these steps:
    1. Select the cell ranges C5:C8 first.

    2. Type the formula =MODE.MULT(B5:B11)

    3. Press CTRL+SHIFT+ENTER. So, it will show all the mode values. Additionally, we have selected four cells, but there are only three outputs. Therefore, there is a “#N/A” for that reason.

    Solution to the Issue: MODE.MULT Function Not Working

  12. Hello Andrew, you can download this Excel file which allows users to enter any amounts (including zero payments). Here, you need to input your value in Total Periods cell.

    Zero Payments

  13. you can download this Excel file which allows users to enter any amounts (including zero payments). Moreover, you can see amount to be paid at the last balance, which is 11,368.

    Balloon Payment

  14. Hello Kenneth Mayfield. you can download this Excel file which allows users to enter any amounts (including zero payments).

    Zero Payments

  15. Hello, Michael Cooper. you can download this Excel file which allows users to enter any amounts (including zero payments).

    Zero Payments

  16. We are glad that this article helped you.

  17. Hello Gerry Candeloro, this works perfectly on our end.

    Comment Solution

    Here the interest is compounded annually, and the payment due is Monthly and Bi Weekly.

  18. Hey Buck, you can follow this article for no compounding interest.

  19. Hi Sohel! Your problem is not clear to us. You can email us the problem [email protected]

  20. Hello Elizabeth D Jones, the template was available on the top of the article. If you still cannot find the link, you can download from here.

  21. You can split the payment schedule into desired quarters and calculate the payments using that quarters’ interest rates.

  22. Different credit cards have different grace periods. You can send us your Excel file and we will look into it.

  23. Hello Ravneet, you can follow this article and adjust your payments on the “Extra Payment” column.

  24. Hi Lisa. You can use the YEARFRAC function to calculate the days difference in fraction of that year. Then, you can multiply the result with the annual interest payment (in this case, scheduled monthly payment*12) to include the pro-rata interest.

  25. I have modified the VBA code from the second method to handle blank cells for your dataset. Please check the solution to your first comment.

  26. Hello Amilia. Thank you for your question. You can use the following VBA code to ignore blank cells within the range.

    Sub URLPhotoInsert2()
    Dim cPhoto As String
    Dim cPicture As Picture
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5:C10")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then GoTo line33
            Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)        
            With cPicture
                .ShapeRange.LockAspectRatio = msoFalse
                .Width = cItem.Width
                .Height = cItem.Height
                .Top = Rows(cItem.Row).Top
                .Left = Columns(cItem.Column).Left
                .Placement = xlMoveAndSize            
            End With
    line33:
             cPhoto = ""        
        Next
    End Sub

    Solution to Blank Cell Problem

  27. Hello Missy, If you change the list it will change the results, both are the same. Your question “can I then edit the column with the book title in it?” is not clear to us. It will be better for us, if you create a sample Excel file of what you want and share it with us. Thanks.

  28. Hi, you can learn about how to hide source data from this article.

  29. Thank you Lyn for your comment.

    You can try the ZIP method to remove password from the Excel file.

    This may not work with Excel 365 version and you will need to use an “Excel password recovery utility”.

  30. Thank you Chelsea for your comment. We have checked the code and it’s working perfectly on our end. Here, we have set the date as

    =DATE(2022,8,21)

    and 1 as the increment.

    VBA Code Working OK

    Did you follow the steps correctly? If yes, then you can send your Excel file and we can take a look into that.

  31. Thank you Brian for your comment.

    Actually, we believe you are right. However, in Excel the NORMDIST function shows the probability mass function when it is set to False. We have kept the original naming as it.

    NORMDIST Function

    Moreover, if you go through the official documentation, you will notice it is written as “density function” in the latter part. So, chances are it may be displayed as ”probability mass function” but actually it is calculating the ”continuous values”.

    NORMDIST Function Official Doc

    • You can modify the code to convert Pipe Delimited CSV files to XLSX using the Command Line.
    '======================================
    ' Convert CSV to xlsx Command Line
    '======================================
    
    srccsvfile = Wscript.Arguments(0) 
    tgtxlsfile = Wscript.Arguments(1)
    
    'Create an Excel worksheet where you want to place the file
    
    On Error Resume Next 
    'Set a variable and define
    Set ObjectiveFileExcel = GetObject(,"Excel.Application")
    
    'If condition for creating new instance if the find is not found
    If Err.Number = 429 Then '> 0
    
    'Set a command to create new instance 
    Set ObjectiveFileExcel = CreateObject("Excel.Application")
    End If
    
    ObjectiveFileExcel.Visible = false
    ObjectiveFileExcel.displayalerts=false
    
    'Give a command to import CSV into Excel
    Set ImportWbk = ObjectiveFileExcel.Workbooks.open(srccsvfile)
    Set ImportWbk1 = ImportWbk.Worksheets(1)
    
    'Code for Pipe Delimiter
    With ImportWbk1.QueryTables.Add("TEXT;" & srccsvfile, ImportWbk1.Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh False
    End With
    
    'Set a command to adjust column widths
    Set ObjectiveRng = ImportWbk1.UsedRange
    ObjectiveRng.EntireColumn.Autofit()
    ' ObjectiveFileExcel.Columns(intColumns).AutoFit()
    'Next
    
    'Give TRUE statement to set the column Headings Bold
    ObjectiveFileExcel.Rows(1).Font.Bold = TRUE
    
    'Give TRUE statement to freeze panes for the Header Row
    With ObjectiveFileExcel.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
    End With
    ObjectiveFileExcel.ActiveWindow.FreezePanes = True
    
    'Apply AutoFilter to Heading Row
    ObjectiveFileExcel.Rows(1).AutoFilter
    
    'Make Header row Interior Cyanide
    ObjectiveFileExcel.Rows(1).Interior.ColorIndex = 31
    
    'Save the worksheet
    ImportWbk1.SaveAs tgtxlsfile, 51
    
    'Make free the Lock on worksheetsheet
    ObjectiveFileExcel.Quit()
    Set ImportWbk1 = Nothing
    Set ImportWbk = Nothing
    Set ObjectiveFileExcel = Nothing
    • Then, type this on the PowerShell and hit ENTER.
    .\convert-csv-to-excel C:\Users\Admin\Desktop\source.csv C:\Users\Admin\Desktop\converted.xlsx
  32. You can do that by following these three steps:
    1. You can insert any files using the Insert > Object command.

    2. Save as PDF and Email Attachment.

    3. Apply this code on a VBA Button.

  33. Hey Ash, thank you for your question.

    Suppose, we have these three items in cell Z2 as a Dropdown List:
    X
    Y
    Z
    Then, if we choose X, then it should display as a list in AA2 cell:
    P
    Q
    R

    Now, to do that, select cell range that contains PQR (eg. T1:T3), then use Name Range
    PQR as X. Similarly, name the ranges as Y and Z for their corresponding cell ranges.

    Next, use Data Validation on cell AA2.
    Allow: List
    Source: =INDIRECT(Z2)

    Then, it should do what you want.

    TLDR; Use Name Range and INDIRECT function inside Data Validation.

  34. Thank you MOE for your wonderful question. To do this you need to apply the following Custom Cell Format.

    [<=1000000]  #,"K";[>1000000]   #,,"M"
  35. You can do this by combining IF function with your formula.
    Create multiple tables for your price information for the years. Refer the year to a cell and the use it to change the VLOOKUP’s “table_array”.

    For example the year is on cell F4,

    now you can type, =IF(F4=2023,IFERROR(VLOOKUP(B5,’Material Pricing’!B5:D7,3,0),””),IF(F4=2024,IFERROR(VLOOKUP(B5,’Material Pricing’!B13:D15,3,0),””),””))

    If the year is 2023, then the “table_array” will be B5:D7
    IF 2024, it will be B13:D15.
    Else it will return a blank.

    To know more about this, you can read this.

  36. Thank you for your reply, Thor.

  37. Thank you for your question. You need to use Underscore (“_”) between two words in the Name Range.

  38. Thank you for your question.

    In my second code, instead of “ActiveSheet.Pictures.Insert” you can try using “ActiveSheet.Shapes.AddPicture”, which should embed the images in the Excel file and it will not disappear.

    the modified VBA code from the second method will be:

    Sub URLPhotoInsert2()
    
    Dim cPhoto As String
    Dim cPicture As Shape
    Dim cRange As Range
    Dim cItem As Range
    
    Set cRange = Range("C5:C10")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then Exit Sub
            Set cPicture = ActiveSheet.Shapes.AddPicture(cPhoto, _
            msoFalse, msoTrue, Columns(cItem.Column).Left, _
            Rows(cItem.Row).Top, cItem.Width, cItem.Height)
            
            With cPicture
                .LockAspectRatio = msoFalse
                .Placement = xlMoveAndSize
            End With
            
        Next
    
    End Sub
  39. You are welcome. I am glad that this article was useful to you.

  40. You need to change Google Drive’s Shared URL when inputting it into the cell.
    For example, if I share an image the URL will be -> “https://drive.google.com/file/d/18r34oAVY8-bicTmf3CaTIumuHp_hqZxH/view?usp=sharing”.
    Then, I need to change it to -> “https://drive.google.com/uc?export=view&id=18r34oAVY8-bicTmf3CaTIumuHp_hqZxH” (putting the values after d/ inside the id values of the URL).

    And for your second question, I think the URL is not correct, if I remove the image resolution info from the URL, then it works perfectly in my second code.

    Your URL -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel-1024×477.png”.
    it should be -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel.png”.

  41. According to your example, Set cRange = Range(“C5, C8, C11”)
    Then, you’ll need to change the height & width to MergeArea
    .Width = cItem.MergeArea.Width
    .Height = cItem.MergeArea.Height

    Full Code >

    Sub URLPhotoInsert2()
    Dim cPhoto As String
    Dim cPicture As Picture
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5, C8, C11")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then Exit Sub
            Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
            With cPicture
                .ShapeRange.LockAspectRatio = msoFalse
                .Width = cItem.MergeArea.Width
                .Height = cItem.MergeArea.Height
                .Top = Rows(cItem.Row).Top
                .Left = Columns(cItem.Column).Left
                .Placement = xlMoveAndSize
            End With
        Next
    End Sub

    The output will be this: https://ibb.co/KXHVzSC

  42. Thank You.

ExcelDemy
Logo