Posts by mamun.ges


    I have worksheets with lots of data and various related certificates in the drive location. The fact is I want to create a hyperlink. Now, how the hyperlink create?

    The File Location folder is D:\Certificate\ where the file name is like 5. T-234363.12_02_2022 in a specific file format ( Sl. Ref. Date)

    The cell range is 10 to 1200.

    Now in my worksheet in cell AD10 if I input 12/02/2022 then the system look at the file location for the "*.*.12_02_2022" file. If found then it creates a hyperlink to this cell and the Ref number (T-234363) will be input on the Cell AC10 As T-234363.

    If any suggestion or expert opnion in this regard will be highly appreciated. Can it be done by vba? Hope someone kind-hearted will help me in this regard.

    Thanks in Advance

    Hi, Hope All Are Fine.

    I have a worksheet where I want to input date to a specific column of the selected cell if I click on a command button.

    The selection data range F10 to F1200 & the date input column is AW

    The procedure is :

    If I select some cell in between F10 to F1200. Then I click on a Command Button. After clicking on the command button, a message asks for date input permission. If I click yes then an input box opens to enter a date where the default date will be today. The date will be input on the specific column of the row of the selected cell. If No then it skips No date input.

    I hope someone helps me in this regard.

    Thanks in Advance.

    Thanks, KjBox for the help and the beautiful code.

    Though I am initially getting a “Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.

    But after changing the ActiveSheet. to Me. the error resolves and the code works like a charm.

    Best wishes to you and really appreciate the help.

    Thanks again.

    Thanks, KjBox for your kind feedback,

    I tried your code but in some cells, it gives the comment but in some cells, it does not though there is value existing in "WXY"

    I can't find the problem in your code. Will you recheck the code?

    Though I tried to establish a code which result is good but it takes a long time to initiate. The code is below.

    I will be happy if you check the below code too.

    Thanks and best regards.

    Hi, Thanks for your feedback.

    I need a code for doing the above condition to fulfill.

    I found a code & made some changes to the code but failed. (Modified code)

    In the above code, it creates the comment for all the data of range ("W9:Y1200). I want a code that creates comments only for those which have value in the range ("W9:Y1200).

    Can anyone help in this context?

    Hope someone is ready to help which is very appreciating.

    Thanks in Advance.

    Hi All,

    I searched many forums but did not get any expected results. Below I am sharing my thoughts, hope someone could help me in this context.

    In my worksheet (Worksheet Name Bill), I want Cell "I" will show the value of "W, X & Y"

    My Comments range is I9:I1200

    My data range is "W, X & Y" from 9 to 1200

    In comments the comment will show as " Product ID: "W" value, Product Amount: X value & Product Paid Amount: "Y" Value.

    If No data in "W, X & Y" then no comment.

    If data changes then the comment updates automatically but no need of old values to show in a comment.

    (If the code run in the module will be best for me)

    Hope I am able to describe it. Help on this would be much appreciated.

    Thanks in advance

    Hi, I am Looking for a solution. know the fact but could not arrange the code in the right way. Hope someone helps me.

    I have two cells "O20" and "Q20"

    If "O20" and "Q20" both have non-number values or are empty then it will run code

    If "O20" and "Q20" both have number values then it will run code

    If "O20" is empty or has a non-number value and "Q20" have number values then it will exit or show error info

    If "O20" has number values and "Q20" is empty or has a non-number value then it will exit or show error info

    I tried

    If IsEmpty(Range("O20").Value) = False Or IsEmpty(Range("Q20").Value) = True Then GoTo ErrorHandle
    If IsEmpty(Range("O20").Value) = True Or IsEmpty(Range("Q20").Value) = False Then GoTo ErrorHandle
    If IsEmpty(Range("O20").Value) = True Or IsEmpty(Range("Q20").Value) = True Then
    If IsEmpty(Range("O20").Value) = False Or IsEmpty(Range("Q20").Value) = False Then

    but failed to achieve the result.

    Hope someone help.

    Thanks in advance

    Hi, Here I attached my sample File Sample_Done.xlsm format to which I applied the above code to get the unique value in sort form.

    Hope It will be easy to understand for others.

    Thank You All.


    I am able to solve the problem. just adding the below code before End Sub.

    Thanks to All for the Support.


    As answer of your question this data is related to other function and reference.It is made with two cell data combination to make it unique. In calculation sheet data is occurring several times. But in Report sheet it form as unique and shorted as ascending order. Yes this data is in text form and want sort as text form.

    The code is given earlier. Used as command button in standard module.

    Thanks and appreciate your help in this regard.

    Hi, All.

    Hope everyone is well.

    I am using the below code to get unique values from one sheet to another by a command button. Now the fact is I want that unique value in a sort from in ascending order.

    But failed to do it. Hope someone looks through my code and helps me to achieve the result.

    I will really appreciate the kind help & concern of anyone.

    Here In Name Manager of the workbook.

    "Name: Extract, Value: Unique, Refers To: =Report!$W$9, Scope: Report"

    Sub Get_Unique()
    Dim Sh1 As Worksheet
        Dim rng As Range
        Dim Sh2 As Worksheet
        Set Sh1 = Sheets("Calculation")
        Set rng = Sh1.Range("P2000:P" & Sh1.Range("P1000").End(xlUp).Row)
        Set Sh2 = Sheets("Report")
        rng.Cells(1, 1).Copy Sh2.Cells(1, 1)
        rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh2.Range("W9"), Unique:=True
    End Sub

    Thanks in Advance.

    Hi, Hope everybody is fine.

    I am using a code to input the same data to the same cell

    Private Sub CommandButton1_Click()
        Dim names As Range
        Set names = Range("J10:J15")
        Dim cell As Range
        For Each cell In names
            cell.Value = cell.Value
        Next cell
    End Sub

    But the problem is I need to expand the range up to J10 to J1000.

    When I did the code takes an unexpectedly long time though not all the calls in J10 to J1000 had value.

    So can anyone help me how to modify the code only for the cell having value and to work faster.

    Thanks and Best Regards.

    Thanks, RoyUK,

    Actually, I data moved from Sheet 4 to 3 as summation based on ID, After that other sheet2 works based on the value of sheet 3.

    In that case, PivotTable does not fulfill my objective. Yes there is a problem result for 133428 is incorrect because those values formatted differently

    And I change all the values as [hh]:mm:ss formation.

    Can It do by formula or VBA ?

    Thanks again and appreciate your support as always.