Thank You so Much, Dear Mumps.
I wish you all the best.
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
Display More
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
Display More
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
Display More
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
Display More
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.
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.