Registration: Mar 8, 2022

Comments: 63

Total submitted: 143

Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, Python Web Scraping, and Report Writing.

Designation

Excel and VBA content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

BSc in Mechanical Engineering, Islamic University of Technology (IUT).
MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka.

Expertise

Data Analysis with MS Excel, SPSS, Python Web Scraping, Report Writing

Experience

Intern at Bangladesh Power Development Board (BPDB)
Freelancer in Fiverr & Reddit for 1 year

Latest Posts From Rafiul Haq

How to Find a Residual Standard Error in Excel (2 Easy Methods)

Below is a dataset with 3 columns: “Company,” “Advertising,” and “Revenue.” We will find the relationship between advertising and revenue generation for ...

Rafiul Haq Jul 7, 2024

READ MORE +

How to Import Stock Prices into Excel from Google Finance (3 Methods)

We’ll use the data from NASDAQ for the Microsoft Corporation (MSFT) to demonstrate how to import stock prices. Here's how our final table will look like after ...

Rafiul Haq Jun 15, 2024

READ MORE +

How to Create a Training Matrix in Excel (3 Easy Methods)

To demonstrate our methods for creating a training Matrix in Excel, we’ll use the following dataset with 3 columns: “Employee”, “Topic”, and “Date”. ...

Rafiul Haq Jun 18, 2024

READ MORE +

Macro to Send Email from Excel (5 Suitable Examples)

We'll use a simple dataset with a list of people, their emails, and the city they're from to send emails to them. Use Macro to Send Email from Excel: ...

Rafiul Haq Jul 3, 2024

READ MORE +

How to Make MIS Report in Excel for Sales: 3 Methods

Method 1 - Utilizing a PivotTable to Make a MIS Sales Report Steps: Select the cell range B4:F12. From the Insert tab, select PivotTable. A ...

Rafiul Haq Jul 6, 2024

READ MORE +

How to Send Email If Conditions Met in Excel (3 Easy Methods)

This article provides 3 methods to send an email if conditions are met in Excel. To demonstrate our methods, we’ve selected a dataset with 3 columns: “Name”, ...

Rafiul Haq Jun 2, 2024

READ MORE +

Cannot Edit Excel File in Protected View (3 Reasons with Solutions)

We'll use a simple dataset to showcase the most common fixes for not being able to edit a file in Protected View. Cannot Edit Excel File in Protected ...

Rafiul Haq Jul 29, 2024

READ MORE +

Excel VBA: Unprotect a Workbook without using a Password – 2 Examples

The sample workbook is protected with a password. Example 1 - Copy the workbook to a New File to Unprotect it Using VBA Steps: The original file ...

Rafiul Haq Aug 9, 2024

READ MORE +

How to Automate the Goal Seek in Excel – 2 Methods

The dataset showcases “Base Price”, “VAT, “Service Charge”, and “Total Price”. Change the Total Price to find the Base Price of a product (“VAT” and “Service ...

Rafiul Haq Aug 10, 2024

READ MORE +

Flash Fill Not Working in Excel: 5 Solutions

Method 1 - Flash Fill Not Working in Excel If It Is Turned Off Steps: Press ALT + F, then T to bring up the Excel Options window. Go to the Advanced ...

Rafiul Haq May 22, 2024

READ MORE +

Excel Calculates Difference Between Two Dates in Days: 7 Ways

Method 1 - Using DATEDIF Function to Find Difference Between Two Dates in Days Use the DATEDIF function to calculate the difference between two dates. This ...

Rafiul Haq Jul 6, 2024

READ MORE +

How to Convert CSV to XLSX without Opening (5 Easy Methods)

Method 1 - Use of Text Import Wizard to Convert CSV to XLSX without Opening Steps: From the Data tab >>> select From Text/CSV. The Import ...

Rafiul Haq Jun 23, 2024

READ MORE +

How to Export PDF Comments into an Excel Spreadsheet – 3 Examples

This is an overview. Example 1 - Using the Foxit Reader to Export PDF Comments into an Excel Spreadsheet Steps: In the COMMENT tab >>> ...

Rafiul Haq Aug 9, 2024

READ MORE +

How to Sum a Range of Cells in a Row Using Excel VBA (6 Methods)

To demonstrate our methods, we have a dataset with three columns: “Fruit,” “March Sale,” and “April Sale.” Method 1 - Using the FormulaR1C1 Property ...

Rafiul Haq Jul 5, 2024

READ MORE +

How to Hide Rows Based on Cell Value with Conditional Formatting in Excel: 2 Methods

Method 1 - Changing Text Color to Hide Rows Based on Cell Value with Conditional Formatting Steps: Select the cell range B5:D10. From the Data tab ...

Rafiul Haq Jul 3, 2024

READ MORE +

Browsing All Comments By: Rafiul Haq

Reply
Rafiul Haq Jul 25, 2023 at 3:22 PM

Hello Baijul, Thank you for your question. The Date column needs to be in ascending order. You’re adding an earlier date to the end of the data, that’s why the formula is not working.

You can sort the data or use the following formulas:

Cell C148: =LOOKUP(2,1/($C$140:$C$145>$C$147),$B$140:$B$145)
This returns Samantha

Cell C149: =LOOKUP(2,1/($C$140:$C$145>$C$147),$C$140:$C$145)
This returns November 25, 2022
Reply
Rafiul Haq Jul 4, 2023 at 3:52 PM

If you want to return the price of the second matched value, you can use the following formula that returns the value $715.

=INDEX(FILTER(D58:D63,ISNUMBER(SEARCH(“Lap”,C58:C63))),2)
Reply
Rafiul Haq Jul 4, 2023 at 3:29 PM

profit percentage = (selling price – cost price) / cost price. Our calculation is correct. Additionally, commission is not usually included in the profit calculation.
Reply
Rafiul Haq Jul 4, 2023 at 2:38 PM

Question 6: You need to return the price of the Mobile Phone sold by Aaron. This question is similar to question 3. So, I have asked to use different formula to do so.

Now, 1 is used as a logical value to identify the matching records based on the conditions specified by the formula. This enables the INDEX function to retrieve the desired value from the given range.

(B79=B71:B76)*(C79=C71:C76) returns, {0;1;0;0;0;0}. So, when we put 1 inside the MATCH function, it will return the second value. Thus, we have obtained the value $429.
Reply
Rafiul Haq Mar 22, 2023 at 3:55 PM

Hello, Lori. The file is working correctly on our end. I think you need to “Enable Editing” from the protected view feature of Excel.
Reply
Rafiul Haq Jan 25, 2023 at 1:16 PM

Thank you for your question. Here is your Indian Format.
Reply
Rafiul Haq Jan 25, 2023 at 11:57 AM

Greetings, Nat Troy. We don’t have a large enough dataset to test your problem. Please send us your file to [email protected]. So we can take a closer look at the issue.
Reply
Rafiul Haq Jan 25, 2023 at 11:49 AM
Thank you, Calvin, for your query. I’m replying on behalf of ExcelDemy. You need to select the values of column A and then apply the conditional formatting to them. The condition will be similar to this image. Steps do the formatting is already mentioned in this article.
- Here is the output.
Reply
Rafiul Haq Jan 25, 2023 at 11:31 AM

You can duplicate the months below the first month for multiple months’ data. Moreover, if you need to remove the values, you can just click the cell range and hit the Delete button. I have added the leave record format for four months in the following file. This can be found on the LeaveReord sheet.
Leave Record for User
Reply
Rafiul Haq Jan 24, 2023 at 4:39 PM

Greetings Ethan. I’m sorry to see you’re missing a feature. You can submit feature requests to Microsoft via their website or community forums.
Reply
Rafiul Haq Jan 24, 2023 at 4:07 PM

Hello Andrew, Your question is not clear to us. Can you send the Excel file to [email protected]? So, we can take a closer look at the issue.

Rafiul Haq Jan 24, 2023 at 4:03 PM

You can use the following code to include the subject and CC columns.

Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As Range
    Dim xCCMail As Range
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'To enter the subject mail, insert a input box
    Set xSubEmail = Application.InputBox("In your email, choose the column with the subject text:", "ExcelDemy", , , , , , 8)
    If xSubEmail Is Nothing Then Exit Sub
    'To enter the CC mail, insert a input box
    Set xCCMail = Application.InputBox("In your email, choose the column with the CC column:", "ExcelDemy", , , , , , 8)
    If xCCMail Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    Set xSubEmail = xSubEmail(1)
    Set xCCMail = xCCMail(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xSubEmail.Offset(k - 1).Value
            xCCMail = xCCMail.Offset(k - 1).Value
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .CC = xCCMail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                '.Send
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

The following image shows the output.

Reply
Rafiul Haq Jan 24, 2023 at 1:45 PM

Greetings, Fethiye Kiralik Villa. I am responding on behalf of ExcelDemy. I’ve tested the =hyperlink method, and it works perfectly for me. Make sure you have the latest version of Acrobat. Alternatively, you can try to reset all the settings in Adobe Acrobat Reader.
Reply
Rafiul Haq Jan 24, 2023 at 12:22 PM

Greetings George. I am replying on behalf of ExcelDemy. There was an issue with the code in the file. I have updated the file. This works perfectly fine for a “+” character in the text.
Reply
Rafiul Haq Jan 24, 2023 at 11:49 AM

Thank you, Alberto, for your question. I am replying on behalf of ExcelDemy. You can manually set the waiting time by using-
ActiveWorkbook.RefreshAll
Application.Wait (Now + TimeValue(“0:00:05”))
…..other lines of code

Here, it will wait for five seconds. You modify it to suit your needs.
Reply
Rafiul Haq Jan 24, 2023 at 1:33 AM

Thank you, Gilles Laurence, for your comment. I am replying on behalf of ExcelDemy. The custom number format works perfectly on our end. The French number system uses a comma for a decimal separator and a space for thousand separators. You can change the thousand separators from the Advanced tab on the Excel Options to solve this.
Reply
Rafiul Haq Jan 23, 2023 at 11:35 PM

Greetings, Michael. I’m responding on ExcelDemy’s behalf. Yes, the methods presented work the same way for Excel tables after I converted the provided dataset into a table.
Reply
Rafiul Haq Jan 9, 2023 at 4:22 PM

Hi, Frank!
You can create a helper column and input “Yes” for the sent mails. Then, you can run the VBA code to sent the values without the “Yes” values. That way, the mail will be sent only to those not sent to before.
You can also email us your Excel file with detailed instructions to [email protected], so that we can give you a proper code to solve the problem.
Reply
Rafiul Haq Jan 9, 2023 at 12:32 PM

Thank you, Rob, for your comment. To make the function available all the time, you can copy the code and paste the VBA code in a workbook. Then, save that workbook as Excel Add-in (*.xlam) file. Then, you will need to enable this add-in for a new Excel file. Thus, the function will be available as long as the add-in is enabled. Additionally, you will need to install the font beforehand.

Let’s assume, you have exported the Excel file as “code 128.xlam” file. Then, you will need to enable this (File > Options > Add-ins > select Go).

Then, you will need to enable the exported file name (in our case it is “code 128“) and press OK.

After doing this, whenever you create a new Excel file, you will be able to use this “CODE128()” function without copying the VBA code.
Reply
Rafiul Haq Jan 9, 2023 at 11:28 AM
Hello, EG Barber thank you for your question. You can use the following code and paste inside your sheet (right click on the sheet, select View Code and paste the code there) to get an automatic VBA sum feature.
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim our_cell_range As Range
Set our_cell_range = Range("C5:C10")
If Not Application.Intersect(our_cell_range, Range(Target.Address)) Is Nothing Then
Range("C11") = Application.WorksheetFunction.Sum(our_cell_range)
End If
End Sub
```
The following animated image shows the solution in action.

Rafiul Haq Oct 31, 2022 at 5:59 PM

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

Reply
Rafiul Haq Oct 31, 2022 at 3:33 PM

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.
Reply
Rafiul Haq Oct 31, 2022 at 9:48 AM

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.
Reply
Rafiul Haq Oct 31, 2022 at 9:24 AM
Hello Andreas, you need to add the following line to ignore the error.
```
On Error Resume Next
```

Rafiul Haq Oct 30, 2022 at 2:15 PM

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

Reply
Rafiul Haq Oct 30, 2022 at 9:01 AM

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)
Reply
Rafiul Haq Oct 30, 2022 at 8:47 AM

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)

Rafiul Haq Oct 22, 2022 at 10:21 PM

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.

Reply
Rafiul Haq Oct 10, 2022 at 1:39 PM
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.
Reply
Rafiul Haq Oct 10, 2022 at 10:48 AM
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.
Reply
Rafiul Haq Sep 27, 2022 at 2:34 PM

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.
Reply
Rafiul Haq Sep 5, 2022 at 11:30 AM

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.
Reply
Rafiul Haq Sep 5, 2022 at 11:28 AM

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.
Reply
Rafiul Haq Sep 5, 2022 at 11:26 AM

Hello Kenneth Mayfield. you can download this Excel file which allows users to enter any amounts (including zero payments).
Reply
Rafiul Haq Sep 5, 2022 at 11:25 AM

Hello, Michael Cooper. you can download this Excel file which allows users to enter any amounts (including zero payments).
Reply
Rafiul Haq Sep 4, 2022 at 4:54 PM

We are glad that this article helped you.
Reply
Rafiul Haq Sep 4, 2022 at 4:49 PM

Hello Gerry Candeloro, this works perfectly on our end.

Here the interest is compounded annually, and the payment due is Monthly and Bi Weekly.
Reply
Rafiul Haq Sep 4, 2022 at 4:31 PM

Hey Buck, you can follow this article for no compounding interest.
Reply
Rafiul Haq Sep 4, 2022 at 4:20 PM

Hi Sohel! Your problem is not clear to us. You can email us the problem [email protected]
Reply
Rafiul Haq Sep 4, 2022 at 4:12 PM

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.
Reply
Rafiul Haq Sep 4, 2022 at 2:15 PM

You can split the payment schedule into desired quarters and calculate the payments using that quarters’ interest rates.
Reply
Rafiul Haq Sep 4, 2022 at 2:12 PM

Different credit cards have different grace periods. You can send us your Excel file and we will look into it.
Reply
Rafiul Haq Sep 4, 2022 at 12:25 PM

Hello Ravneet, you can follow this article and adjust your payments on the “Extra Payment” column.
Reply
Rafiul Haq Sep 4, 2022 at 12:10 PM

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.
Reply
Rafiul Haq Aug 24, 2022 at 2:24 PM

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.

Rafiul Haq Aug 24, 2022 at 2:17 PM

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

Reply
Rafiul Haq Aug 23, 2022 at 5:03 PM

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.
Reply
Rafiul Haq Aug 23, 2022 at 10:35 AM

Hi, you can learn about how to hide source data from this article.
Reply
Rafiul Haq Aug 21, 2022 at 3:52 PM

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”.
Reply
Rafiul Haq Aug 21, 2022 at 12:46 PM
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.

Did you follow the steps correctly? If yes, then you can send your Excel file and we can take a look into that.
Reply
Rafiul Haq Aug 17, 2022 at 12:05 PM

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.

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

Rafiul Haq Aug 14, 2022 at 3:21 PM

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

Reply
Rafiul Haq Aug 14, 2022 at 12:31 PM

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.
Reply
Rafiul Haq Aug 11, 2022 at 5:06 PM

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.
Reply
Rafiul Haq Aug 10, 2022 at 11:47 PM
Thank you MOE for your wonderful question. To do this you need to apply the following Custom Cell Format.
```
[<=1000000]  #,"K";[>1000000]   #,,"M"
```
Reply
Rafiul Haq Aug 8, 2022 at 4:50 PM

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.
Reply
Rafiul Haq Aug 8, 2022 at 4:41 PM

Thank you for your reply, Thor.
Reply
Rafiul Haq Aug 8, 2022 at 4:37 PM

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

Rafiul Haq Jul 28, 2022 at 6:27 PM

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

Reply
Rafiul Haq Jul 26, 2022 at 5:09 PM

You are welcome. I am glad that this article was useful to you.
Reply
Rafiul Haq Jul 18, 2022 at 10:35 PM

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

Rafiul Haq May 14, 2022 at 11:35 PM

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

Reply
Rafiul Haq Mar 11, 2022 at 1:03 AM

Thank You.