Posts by mamun.ges

Thank You so Much, Dear Mumps.

I wish you all the best.

Thank You S O

I am attaching the file (First Sample File.xlsm) for better understanding.

Thank you again.

Hi.

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.

Hi KjBox, I am Attaching a sample data file Test.xlsm with the VBA code in it.

Please remove the comments in "I" then initiate the command button to see the result.

Please read the Yellow mark sentences to know my expectation.

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.

Sub DataToComment()
Dim LRow As Long, aCell As Range, bCell As Range, cCell As Range, ws As Worksheet
    Set ws = ActiveSheet
      With ws
         For LRow = 9 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(LRow, 9).Value <> "" Then
                
    Dim theComment As String
                theComment = ""
    Dim xComment As Comment
                                
                For Each aCell In Intersect(Range("W:W"), .Rows(LRow)).Cells
                For Each bCell In Intersect(Range("X:X"), .Rows(LRow)).Cells
                For Each cCell In Intersect(Range("Y:Y"), .Rows(LRow)).Cells
                    theComment = theComment & "Product ID: = " & aCell.Value & " Product Amount: = " & bCell.Value & " Product Paid Amount: = " & cCell.Value
                Next
                    Next
                        Next
    
            With .Cells(LRow, 9)
                .ClearComments
                .AddComment theComment
            
            With .Comment.Shape.TextFrame
            .AutoSize = True
            .Characters.Font.Name = "Times New Roman"
            .Characters.Font.Size = 14
            
            End With
                End With
                    End If
            Next LRow
            End With
End Sub

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)

Sub CellToComment()
    Dim xc As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
    For xr = 9 To 1200
    Set CheckRange = Sheets("Bill").Range("W9:Y1200")
    If CheckRange Is Nothing Then
    .Cells(xr, 9).ClearComments
    
    Else
    .Cells(xr, 9).ClearComments
    .Cells(xr, 9).AddComment .Cells(xr, 23).Value & " Product ID:, " & .Cells(xr, 24).Value & " Product Amount:, " & .Cells(xr, 25).Value & " Product Paid Amount:. "


    End If
    Next
    End With
    
    End Sub

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.

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
    
    Range("Table1[[#Headers],[Unique]]").Select
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[Unique]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Thank You All.

Hi,

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

    Range("Table1[[#Headers],[Unique]]").Select
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[Unique]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks to All for the Support.

Hi,

You want to know how the data get into the P10:P1000

Here in W10 =CONCATENATE(B10,O10) and in B10 I have ID and O10 =IF(B10="","",MONTH(H10) & YEAR(H10))

Hope it works.

Thanks

Hi,

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.

Thanks 6StringJazzer,

I am using office 13 in-home and 16 at my workstation.

No, I am not using office 365.

It will be more helpful If it can be solved by VBA.

For learning new things I am very eager. Will you show How a Formula work at this point?

I am attaching a sample file Time_Table.zip.

Hope better help.

Thanks and best regards.

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.

Thanks Royuk

Yes, It is the faster way but I need to make some analysis based on that sheet and which result shows up in another sheet. I need data for the specific cell as mentioned earlier.

Thanks.

Thank You KjBox,

Very pleased that you are here. Sometimes simple things are very hard to achieve.

Actually, On selection, in between Range J10:J1000 is not showing but if I use the above code then it works but the looping through the range takes a long time.

Now the issue solve. Thank you Kjbox.

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.