User Posts: Bhubon Costa
0
How to Insert Column without Affecting Formulas in Excel (2 Ways)
0

When dealing with a huge data set, it is possible to overlook any data in a column. If you put a column between the data set at that moment, the cell ...

0
How to Add Vertical Gridlines to Excel Chart (2 Easy Methods)
0

Gridlines are horizontal and vertical lines that run through your chart layout to represent axis divisions. It is beneficial to add horizontal or vertical ...

0
Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)
0

The AutoFilter function is one of several Excel features that may also be utilized in VBA. If you have a dataset and wish to filter it based on a cell value, ...

0
Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
0

Year-over-year growth analysis can offer a comprehensive portrayal of a company's financial development. To compare the growth of a company over the last year, ...

0
How to Split Names into Three Columns in Excel (3 Methods)
0

When you have a list of complete names and wish to separate them into first, middle, and last names, you may use Excel features to divide the cells. In this ...

0
How to Extract Text After First Space in Excel (3 Methods)
0

Sometimes, you may need to extract a particular text to get better visualization and comparison. For example, you want to extract only the customers’ names ...

0
How to Use Range with Variable Row and Column with Excel VBA
0

It is time consumable when you want to format cells by selection and work with large data at the same time. But with the help of the VBA, we can program to do ...

0
How to Format Cell and Center Text with Excel VBA (5 Ways)
0

Generally, we can use the Alignment group from the Home ribbon to make a center alignment. Besides, while working with Macro, you may need to do the same task ...

0
[Fix]: Formulas Not Copying Down in Excel Table (3 Solutions)
0

To save the consumption of time, you may need to apply the same formula in a number of columns in an Excel table. But sometimes it won't work for several ...

0
How to Custom Cell Format Number with Text in Excel (4 Ways)
0

You frequently need to add text with numbers to specify its characters and purposes. Microsoft Excel doesn’t offer a built-in function or formula for the ...

0
How to Format a Number in Thousands K and Millions M in Excel (4 Ways)
0

While working with large numbers, it’s not easy to visualize data with thousands and millions. That’s why you may need to put units for better visualization. ...

0
Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
0

When you take out a loan to buy something and wish to return it over time with monthly payments, you may need to know the amount of the monthly payment. In ...

0
Excel VBA: Copy Row If Cell Value Matches (2 Methods)
0

While differentiating data, we need to extract the specific data. So, we have to extract them all based on particular values. We have to choose the VBA over ...

0
How to Change Drop Down List Based on Cell Value in Excel (2 Ways)
0

In order to extract specific data based on specific values, we may need to use the drop-down list. Moreover, we need to co-relate the two or more dependent ...

0
How to Find External Links in Conditional Formatting in Excel (2 Ways)
0

Assume your worksheet contains a large number of external links, and you now have to dynamically identify the cells that contain the links and references. In ...

Browsing All Comments By: Bhubon Costa
  1. Greetings INSTASAVETUBE,
    Countless thanks for your kind words of praise. The Exceldemy team has always been there to assist and has been working to make knowledge accessible to everyone globally. And without a doubt, you are the kind of great people that empower us.

    Best Regards,
    Bhubon Costa
    (Exceldemy Team)

  2. Greetings Lalit,
    Pleased to hear that your previous query has been resolved.

    We have created an Excel file for you to add different subjects. Just download the file and run the code of Module 1.

    https://www.exceldemy.com/wp-content/uploads/2022/08/Send_Email_Reminder_Lalit.xlsm

    1. You will get an input box to select the subject column as shown in the image below.

    1

    2. Therefore, you will see the emails on display before sending them, with the selected different subjects.

    2

    Please feel free to ask more queries. Our ExcelDemy Team will reach you as soon as possible.

  3. Greetings Lalit,
    You just have to add two lines in between the xMailSections portion:
    Steps:
    1. Add the file path of your attachment with the attachment name.
    2. Apply add attachment command to add the attachment.

    Attached_File = "C:\Users\USER\Downloads\Send_Email_Reminder.xlsm"
    .Attachments.Add Attached_File

    Please, see the image below for a detailed understanding.

    1

    After running the code, you will see results with the attachment available.

    2

    Moreover, we have a dedicated article based on your requirement. Please, go through the article to learn in detail.

    Hope, this will meet your requirement. Please, give us feedback if any furthur query needed. The ExcelDemy team is always in your assistance.

  4. Greetings Emma,
    Let’s us know some info at first.
    1. Can you access the other methods to paste values only?
    2. Can you replace data in a non-blank cell?
    We have gone through every possible solution but couldn’t match the issue exactly. If you kindly share your excel file with us, we will send it back with the solution.
    Email: [email protected]
    Our dedicated ExcelDemy Team is always there to help.

  5. Greetings DANA,
    To update the result after each entry you make, you just need to edit a one-line VBA code. It will update the table range each time you make a new entry in column B.

    The VBA Code:

    Set TableRng = Range(Range("B5"), Range("B5").End(xlDown))

    Now, make new entries and run the program to update the result.
    Hope this will work for you. Please give us feedback if you have any further queries.

  6. Thanks a lot for your appreciation. You can also put your problems here, the ExcelDemy team is always in your assistance.

  7. Thank you MOE, for your generous appreciation!

  8. Greetings Justin,
    #VALUE error occurs when the function doesn’t find any recognized source value. It might have occurred if the degree values were not in degree formats. Follow the steps below to convert the value into degree format first and then apply the function Convert_to_Decimal with our VBA.

    Step 1:
    Add a degree (°) symbol after a number with a keyboard shortcut.

    Alt + 0176

    (Example: 32°).

    Step 2:
    Add a minute (‘) symbol after the minute’s value with the apostrophe (‘) key from the keyboard (Example: 16’).

    Step 3:
    Add a second (“) symbol after the second’s value by pressing

    SHIFT + Apostrophe (')

    (Example: 48″)

    Step 4:
    Your degree format will show like (32°16’48”).
    Finally, apply the Convert_to_Decimal function to the degree value.

    Hope this will work with your issue. You can also mail us the Excel file. We will provide the Excel file with the required solution. Please give us your further feedback.

  9. Greetings David,
    Thanks for your valuable suggestion.

  10. Greetings Nishant,
    To get emails one day prior the deadline date, try the following codes below.

    We just edited the code in the If condition by replacing 1 with 0.

    Previous code:

    If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                xValSendRng = XRcptsEmail.Offset(k - 1).Value

    Modified Code:

    If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 1 Then
                xValSendRng = XRcptsEmail.Offset(k - 1).Value

    Please, give us feedback, if any further query needed. The ExcelDemy team is always in your assistance.

  11. Greetings,
    According to your query, the ExcelDemy team has created an Excel file with the solution. Please provide your email address here, we will send it to you in no time.

    Otherwise, you can just follow the procedures as we have proceeded.

    Step 1:
    a. Our data set range is B4:F8.
    b. The names of the fruits range is C4:F4.
    c. Names of people range is B5:B8.

    Step 2:
    a. In cell C11, create a drop-down list with the range C4:F4.
    b. Give a named range for the numbers of each fruit with its name (Ex: Named range = Mango for C5:C8).
    c. Create another dropdown list dependant to the cell C11. Use the following formula in the Data Validation box from the Data tab to do this.

    =INDIRECT($C$11)

    Step 3:
    a. In cell C15, insert the following formula for Mango.

    =INDEX($B$5:$F$8,IF(IF($C$11=C4,MATCH(C4,$B$4:$F$4,0)," ")=(MATCH($C$11,$B$4:$F$4,0)),MATCH($D$11,C5:C8,0), " "),1)

    b. AutoFill the formula with dragging right for three cells for more three fruits.

    Step 4:
    a. Now, you are done, select any name (Grapes) from the first drop-down list.
    b. Then, select the number (6).
    c. It will result in the person name (Kent).

    Please, provide your further feedback if any queries needed.

  12. Greeting AIN,
    Let’s say you want to add another column containing the CC email addresses. You can just copy the codes from here. Moreover, if you want to get the Excel file containing the codes, just give your email address.

    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 String
    'Declare variables for additional column
        Dim xCCEmail As String
        Dim xCCContent 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 CC mails in another column, insert a input box
        Set xCCContent = Application.InputBox("In your email, choose the column with the CC addresses:", "ExcelDemy", , , , , , 8)
        If xCCContent 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 xCCContent = xCCContent(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 cc emails with the required variables
                xCCEmail = xCCContent.Offset(k - 1).Value
                
                'Create the subject, body and text contents with the required variables
                xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
                
                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
                    .To = xValSendRng
                    .CC = xCCEmail
                    .HTMLBody = xMsg
                    .Display
                    
                    '.Send
                
                End With
                Set xMailSections = Nothing
            End If
        End If
        Next
        Set xCrtOut = Nothing
    End Sub
    
    

    Hope this will work with your requirements.
    Please, keep giving your feedbacks. The ExcelDemy team is always concerned about your queries.

  13. ‘Thank you Dan for your comment. Let’s just your first row is 2 and last row is 7.

    ‘First condition: G = “BID”, H <= 10000.00, I = "Orders, Web" OR ="cXML Orders"

    Sub Hide_Condition1()
    Dim i As Integer
        For i = 2 To 5
    ' Cells (i,7) here 7 means G column and i will be 2, so it will be G2 cell and so on
            If Cells(i, 7).Value = "BID" And Cells(i, 8).Value <= 10000 And _
            (Cells(i, 9).Value = "Orders, Web" Or Cells(i, 9).Value = "cXML Orders") Then
                Rows(i).EntireRow.Hidden = True
            Else
                Rows(i).EntireRow.Hidden = False
            End If
        Next i
    End Sub

    ‘Second condition: G =”MO”, H <10000.00

    Sub Hide_Condition_2()
    Dim i As Integer
        For i = 2 To 5
            If Cells(i, 7).Value = "MO" And Cells(i, 8).Value <= 10000 Then
    
                Rows(i).EntireRow.Hidden = True
            Else
                Rows(i).EntireRow.Hidden = False
            End If
        Next i
    End Sub

    ‘Last Condition: G <10000.00, Apply Color

    Sub Hide_Condition_3()
    Dim i As Integer
        For i = 2 To 5
            If Cells(i, 7).Value < 10000 Then
            Cells(i, 7).Interior.Color = RGB(253, 233, 217)
            End If
        Next i
    End Sub

    ‘Hope this answer your query.

  14. First of all, take cordial greetings from the ExcelDemy team. There could be some possible reasons due to the issue:
    The inverted commas are not properly inserted in your code as they should be in VBA script.
    The Sheet Names might not be inserted in VBA exactly as they are in the Excel file.
    However, we have created an Excel file precisely in accordance with your requirements. We can send the Excel file if you provide your email address.
    Otherwise, you can copy the codes below that we have used to make the Excel file with the right syntax. Please continue to provide us with feedback; ExcelDemy always welcomes your concerns.

    Sub MoveRowBasedOnCellValue()
    ‘Declare Variables
    Dim MAIN As Worksheet
    Dim Project As Worksheet
    Dim Claims As Worksheet
    Dim TaM As Worksheet
    Dim QUOTED As Worksheet
    Dim PM As Worksheet
    Dim FinalRow As Long
    Dim Cell As Range

    ‘Set Variables
    Set MAIN = Sheets(“Dataset2”)
    Set Claims = Sheets(“CLAIMS”)
    Set Project = Sheets(“Project”)
    Set TaM = Sheets(“TaM”)
    Set PM = Sheets(“PM”)
    Set QUOTED = Sheets(“QUOTED”)

    ‘Type a Command to select the entire row
    Selection.EntireRow.Select

    ‘Define destination sheets to move row
    FinalRow1 = MAIN.Range(“A” & MAIN.Rows.Count).End(xlUp).Row
    FinalRow2 = Claims.Range(“A” & Claims.Rows.Count).End(xlUp).Row
    FinalRow3 = Project.Range(“A” & Project.Rows.Count).End(xlUp).Row
    FinalRow4 = TaM.Range(“A” & TaM.Rows.Count).End(xlUp).Row
    FinalRow5 = QUOTED.Range(“A” & QUOTED.Rows.Count).End(xlUp).Row
    FinalRow6 = PM.Range(“A” & PM.Rows.Count).End(xlUp).Row
    With MAIN

    ‘Apply loop for column F until last cell with value
    For Each Cell In .Range(“F1:F” & .Cells(.Rows.Count, “F”).End(xlUp).Row)

    ‘Apply condition to match the “CLAIMS” value
    If Cell.Value = “CLAIMS” Then

    ‘Command to Copy and move to a destination Sheet “CLAIMS”
    .Rows(Cell.Row).Copy Destination:=Claims.Rows(FinalRow2 + 1)
    FinalRow2 = FinalRow2 + 1

    ‘Apply condition to match the “PROJECT” value
    ElseIf Cell.Value = “EQUIPMENT PROJECT” Then

    ‘Command to Copy and move to a destination Sheet “PROJECT”
    .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
    FinalRow3 = FinalRow3 + 1

    ‘Apply condition to match the “PROJECT” value
    ElseIf Cell.Value = “CONTRACTING PROJECT” Then

    ‘Command to Copy and move to a destination Sheet “PROJECT”
    .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
    FinalRow3 = FinalRow3 + 1

    ‘Apply condition to match the “TaM” value
    ElseIf Cell.Value = “T&M” Then

    ‘Command to Copy and move to a destination Sheet “TaM”
    .Rows(Cell.Row).Copy Destination:=TaM.Rows(FinalRow4 + 1)
    FinalRow4 = FinalRow4 + 1

    ‘Apply condition to match the “Quoted” value
    ElseIf Cell.Value = “QUOTED” Then

    ‘Command to Copy and move to a destination Sheet “QUOTED”
    .Rows(Cell.Row).Copy Destination:=QUOTED.Rows(FinalRow5 + 1)
    FinalRow5 = FinalRow5 + 1

    ‘Apply condition to match the “SVC AGR” value
    ElseIf Cell.Value = “SVC AGR” Then

    ‘Command to Copy and move to a destination Sheet “PM”
    .Rows(Cell.Row).Copy Destination:=PM.Rows(FinalRow6 + 1)
    FinalRow6 = FinalRow6 + 1
    End If

    Next Cell

    End With

    End Sub

ExcelDemy
Logo