User Posts: Nahian
0
How to Maintain Excel Header Alignment (3 Easy Steps)
0

The article will show you how to maintain Header Alignment in Excel. When you print a copy, it’s necessary to have a header on the page. We can add some ...

0
How to Use CHAR(32) Formula in Excel (5 Practical Examples)
0

The article will show you an application of using the CHAR function in Excel. Suppose you have a range of IDs in the dataset and you want a space in the middle ...

0
How to Use IFS and AND Functions Together in Excel (3 Examples)
0

The article will show you how to use IFS and AND function together in Excel. The IFS function is an alternative to using the nested IF functions. The advantage ...

0
How to Make All Numbers Positive in Excel (11 Effective Methods)
0

The article will show you how to make all numbers positive in Excel. Sometimes in calculus and coordinate geometry, we have to take the positive value or the ...

0
How to Maintain Accounts in Excel Sheet Format (4 Templates)
0

The article will show you how to maintain accounts in Excel sheet format. It’s important to know how your expenses are going in a month so that you can ...

0
How to Change Bin Range in Excel Histogram (with Quick Steps)
0

The article will show you how to change the bin range in an Excel histogram. A histogram is a graph that illustrates the frequency of some statistical data ...

0
[Solved] Spell Number Not Working in Excel
0

The article will show you how to solve the spell number not working problem in Excel. Basically, Excel doesn’t have any built-in function to spell a number or ...

0
How to Scale Time on X Axis in Excel Chart (3 Quick Methods)
0

The article will show you how to scale time on the X axis in an Excel Chart. Excel charts help us to visualize the change in data graphically which is useful ...

0
How to Color Code Cells in Excel (3 Efficient Methods)
0

The article will provide you with three different methods on how to use color code cells in Excel. Coloring a cell helps us to highlight the data and we may ...

0
Excel VBA to Clear Contents of Named Range (3 Macro Variants)
0

The article will show you how to clear the contents of a Named Range in Excel VBA. If your dataset contains Named Ranges and you need to change the values in ...

0
How to Clear Cell Contents Based on Condition in Excel (7 Ways)
0

The article will show you how to clear cell contents based on condition in Excel. It’s easier to clear the cell contents at once based on conditions than to ...

0
How to Perform Sensitivity Analysis for Capital Budgeting in Excel
0

The article will illustrate how to undertake capital budgeting sensitivity analysis in Excel. Every businessman needs to predict how much money he would get as ...

0
How to Draw AON Network Diagram on Excel
0

The article will show you how to draw AON (Activity-on-Node) network diagram in Excel. The AON diagrams are often important to show the workflow briefly and ...

0
How to Flip Data Vertically in Excel (4 Quick Methods)
0

The article will show you how to flip data vertically in Excel. If a user wants to reverse his data so that the data are stored from bottom to top in the Excel ...

0
How to Create a Table with Headers Using Excel VBA (2 Methods)
0

The article will show you how to create a table with headers using Excel VBA. Sometimes, it’s easier to use a VBA command to create tables with headers instead ...

Browsing All Comments By: Nahian
  1. Hi VBANEWB, thanks for reaching out. Here, we declared Count variable first. So the VBA automatically accepts Count2 as a similar variable. So we didn’t need to declare it separately.

  2. Hi Bates, Thanks for reaching out. Could you please share your dataset? That way, I might find the solution to your problem. Because, rows and columns both represent 2 dimensional array. It’s not generally possible to convert them to a 3d array.

  3. Hello Mr. Gurry, thank you so much for the feedback. I have updated the article according to your suggestions. What I did before is that I tried to use the xlOr operator for two different AutoFilter Field. But this can be done by With Statement and I showed the process in Method 1. Here I changed the conditions too. Please check again if there’s anything you want to add.

  4. Hello sir, the process has been disabled since May 31. But to be sure, please check your Google account if you have the option ‘Less secure app access’ available. You can find this option from Manage Account >> Security >> Less secure app access. If you enable this option, then you can send Mails without Outlook.

  5. Hi Msirhc, thanks for reaching out. This actually happens because you haven’t typed the proper column in the first input box that shows up after running the code. The rows are inserted based on values, so if you select the first column (The week column), it won’t work, because that column contains text, not values. The second and third column of the dataset contain values. But the sales column contains irrelevant large values. So you should type 2 in the first input box if you want to use this dataset.

  6. Hey Praveen, thanks for reaching out. This XML files are actually in version 1 format.

  7. Hi BZ, thanks for your response. You can reference the cells from one sheet to another workbook. That way, you can automatically update data in the new workbook when you make a change in the existing worksheet. And to update with condition, you need to use conditional function like the IF, AND, OR, EXACT etc. functions.

  8. Hello Shan, thanks for reaching out. It would be good if you share your code and the dataset file so that I could understand your problem clearly. What I understand from your comment, I think it is not the problem. You need to get your hidden rows back after you delete the filtered data. And to do that, just press CTRL+SHIFT+L. The command will remove the filter and return your hidden rows back.

  9. Hi Cavallino, thanks for reaching out. The good news is, you can send emails by VBA using Outlook. Please check this article “How to Automatically Send Emails Based on Date“. You can also find similar articles on our website. Hope this will help you.

  10. Hi Dabrowski, thanks for reaching out. If you remove the ‘& LastRowFind(“convertxml”)’ part from the 7th line of the code. It will solve your problem. The function ‘LastRowFind’ causes to generate that extra file.

  11. Hey Bharat, thanks for reaching out. Could you specify the code that face problem using it?

  12. That’s great Griffin, I hope you get benefited from our articles.

  13. Hi Tanya, thanks for reaching out. I understand your problem. It may not be possible to send the Email just by running the code and wait for the day to come. But I can help you on sending email at a certain time in a day. The 3rd code sends email in the current date when you run it. You can add a scheduler in the code. The email will be sent at the time you set in the code. I added the code below. You can see that the time here is 16:18:00. So if you run the code before 16:18, and keep the corresponding Excel file open, the email will be sent at 16:18. You don’t need to rerun this code during this time. Hope this helps you a bit. I’ll be working on your problem too. If I get a solution, I’ll immediately add that in your reply.

    Option Explicit
    Private Sub Workbook_Open()
    Application.OnTime TimeValue(“16:18:00”), “SendEmail03”
    End Sub
    Sub SendEmail03()
    Dim Date_Range As Range
    Dim rng As Range
    Set Date_Range = Range(“B5:B10”)
    For Each rng In Date_Range
    If rng.Value = Date Then
    Dim Subject, Send_From, Send_To, _
    Cc, Bcc, Body As String
    Dim Email_Obj, Single_Mail As Variant
    Subject = “Hello there!”
    Send_From = “[email protected]
    Send_To = “[email protected]
    Cc = “[email protected]
    Bcc = “”
    Body = “Hope you are enjoying the article”
    On Error GoTo debugs
    Set Email_Obj = CreateObject(“Outlook.Application”)
    Set Single_Mail = Email_Obj.CreateItem(0)
    With Single_Mail
    .Subject = Subject
    .to = Send_To
    .Cc = Cc
    .Bcc = Bcc
    .Body = Body
    .Send
    End With
    End If
    Next
    Exit Sub
    debugs:
    If Err.Description <> “” Then MsgBox Err.Description
    End Sub

  14. Hi Bolton, thanks for reaching out. I think you need to use different code for your solution. The following code will create a user defined function to sum up the data based on the background color of cells.

    Function SumBasedOnColor(mn_cell_color As Range, mn_range As Range)
    Dim mn_sum As Long
    Dim mn_colorIndex As Integer
    mn_colorIndex = mn_cell_color.Interior.ColorIndex
    For Each mn_CI In mn_range
    If mn_CI.Interior.ColorIndex = mn_colorIndex Then
    mn_sum = WorksheetFunction.Sum(mn_CI, mn_sum)
    End If
    Next mn_CI
    SumBasedOnColor = mn_sum
    End Function

    After that, use the function like the following image.

    There’s something important that you have to keep in mind. We referenced the cell F4 here and this cell background color was filled with yellow. The cell you reference should have the background color matched with the cells containing data.

  15. Hi Samad, thank you for reaching out. Fortunately there is a way to automate sheet name based on a cell value. In the image, you can see that I have a cell value in A1. I also have values in A1 cell of the other sheets. I will change the name of the corresponding sheets based on the value in A1.

    Now, type the following code in a VBA module and run it.

    Sub ChangeSheetName()
    Dim mn_Worksheet As Worksheet
    For Each mn_Worksheet In Sheets
    mn_Worksheet.Name = mn_Worksheet.Range("A1")
    Next mn_Worksheet
    End Sub

    You can see that the name of the sheet changes according to the cell value of A1. If you store sheet names in another cell such as B5, you need put this cell reference instead of A1 in the VBA code. Otherwise you will encounter errors.

  16. Hi Steve, we are extremely sorry for your trouble. Named ranges are pretty useful when we need to use a range repeatedly. A lot of our readers are okay with it. Going through the dataset can help anyone to understand the defined range of a named range. But we are taking your feedback sincerely. We’ll apply your suggestion in the upcoming articles.

  17. Hi Kinney, thanks for your response. The formula was actually written in cell E6. It was a typing mistake in the description of the process. We are extremely sorry for you to have trouble on this matter.

  18. Hi Shilpa, thanks for the response. Here’s the solution to your question no 1

    You can simply create a criteria similar to the methods of this article using dates. Suppose you want to see the sales information after May. Please watch the following image for the process. I created the criteria in G6 cell.

     

    In order to solve the second question of your comment, please apply the method described in the Section 3.2 of this article.
    Hope this helps to solve your queries.

  19. Hi Mary, thanks for your response. You can definitely apply this process in a single sheet. However we use different sheets to show the different processes for the same output. And it also seemed convenient to use separate sheets to show the application of using the data validation list for different conditions. So different sheets for different methods were used in this article. Hope that provides you with the answer to your question.

  20. Hello sir, thank you for the feedback. We are working hard on this matter, I hope we will provide you the solution in the upcoming tutorial pretty soon.

  21. Hi Pranav, thanks for the query. You can simply use the code of the second method of the article in this regard. Just follow the procedure after you run the code. Hope that helps you.

  22. Hi! Thanks for asking. As the article was to provide some templates, I didn’t go in detail with the formula. Here, Invoice_Info is a named range. It refers to the range B12:J17 of the ‘template 1’ sheet. To create a named range, you need to select your desired range of cells, then go to Data Tab >> Define Name and after that, give a name of that range. The advantage is that, you can use that range by inserting it’s name anywhere in the worksheet or workbook. You don’t have to select the range every time you use it in a formula. Hope this solves your problem.

  23. Thank you Sir! I hope you will find my other articles useful and enjoyable too. Please visit this Link to explore more!

  24. hi Chris, thanks for your response! A possible reason could be that you may not have the original or latest version of Adobe. If that’s the case, please install that. Hope this helps.

  25. hi, thanks for asking Liss. It will be better for you to use the code of the second method. You may not be able to set a reminder using VBA, but you can set a date interval using it. If you go through the explanation, you will see that I created a 7 days interval in the code (you can change it according to your convenience). If you want to remind your employee that he should finish his job within a week, you can use the dataset and VBA code of the second method. You cannot send emails on a particular date. Suppose you run this code on 15th July. You have a date range of 15th July to 25th July. The employees who have to finish their task within 16th to 22nd July will get your mail. Those who have to finish within 23rd to 25th won’t get your mail. However, you can make a schedule to send your mail in a particular time of the day. Just put the following statement at the beginning of the code of the second method. Hope this helps
    Option Explicit
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("hh:mm:ss"), "SendEmail02" 'put the time when you want to send the email
    End Sub 'you must keep open your excel file until then after you run the code

  26. hello there, thanks for asking. I understand your problem. Although I haven’t found the exact solution to your problem yet, I can help you on sending email at a certain time in a day. The 3rd code sends email in the current date when you run it. You can add a scheduler in the code. The email will be sent at the time you set in the code. I added the code below. You can see that the time here is 16:18:00. So if you run the code before 16:18, and keep the corresponding Excel file open, the email will be sent at 16:18. You don’t need to rerun this code during this time. Hope this helps you a bit. I’ll be working on your problem too. If I get a solution, I’ll immediately add that in your reply.

    Option Explicit
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("16:18:00"), "SendEmail03"
    End Sub
    Sub SendEmail03()
    Dim Date_Range As Range
    Dim rng As Range
    Set Date_Range = Range("B5:B10")
    For Each rng In Date_Range
    If rng.Value = Date Then
    Dim Subject, Send_From, Send_To, _
    Cc, Bcc, Body As String
    Dim Email_Obj, Single_Mail As Variant
    Subject = "Hello there!"
    Send_From = "[email protected]"
    Send_To = "[email protected]"
    Cc = "[email protected]"
    Bcc = ""
    Body = "Hope you are enjoying the article"
    On Error GoTo debugs
    Set Email_Obj = CreateObject("Outlook.Application")
    Set Single_Mail = Email_Obj.CreateItem(0)
    With Single_Mail
    .Subject = Subject
    .to = Send_To
    .Cc = Cc
    .Bcc = Bcc
    .Body = Body
    .Send
    End With
    End If
    Next
    Exit Sub
    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    End Sub

ExcelDemy
Logo