Posts by mamun.ges

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.

Thanks, RoyUK for your response.

If it is possible with VBA then I think it will be best.

Here I attached my file Time_Table.zip

Thank you in Advance for the help.

Hi All

Hope everyone is fine.

I searched many forums and google it but did not get the expected result.

Facts:

I have two worksheets, In Sheet4 Range L10:L1000, M10:M1000 & N10:N1000 have data and Matching Column is P10:P1000

Now In Sheet 3 W10 Value check through Sheet4 P10:P1000 if Matched found then it sums the matched value L10:L1000 data to H10, M10:M1000 data I10, and N10:N1000 data to J10.

It goes the same as from W10 to last available data in the W10:W column in sheet 3.

Can It Do with VBA coding?

If so, then it will be so great for me and highly appreciated.

Thanks in Advance.

Dear KjBox,

I am really impressed by the work you did for me. It works like a magic.

"Sometimes giving thanks is very little as appreciation. "

May God grant you a long and happy life.

Thanks and Best Regards

From the previous post.

The Frist time it updates.

Then I changed the monthly rent in the update sheet of the same entry and update

then 16048 is not found in the network listing also not found by the search.

Hope you understand, sorry for the poor description.

Thanks and best regards

Thanks, Kjbox,

I have attached the file again.

In this, in the Network listing, you see 16048 two Entries. Then in the update sheet, you see the data I wanted to update.

Now if I click on the update then something happens that is not clear to me.

I do not understand the cause. Please see what is happening?

Thanks and Best regards.

Matching To the exect word will be best I think so.

Thanks Again.

Yes, Kjbox my sample file has no formula. Sorry for that As I thought VBA will not affect the formula.

As it affects the formula so my search function is not working.

Thank you for the feedback.

Thank You KjBox,

Your code works fine but when I click to update it removes all the formulas in Sheet1 or Tabel1 also It changes other cell values.

Suppose I have a particular row. The code changes all the values of that particular row or it duplicates some other row.

I here attached my work file for your kind consideration. For Upload.xlsm

Thanks and best regards

Thanks, RoyUK for updating the title of the thread.

I have attached a Sample.xlsx File.

The color yellow is used to show the matching cell.

Sorry, If I am unable to describe my thoughts to you.

Thanks and always appreciate your help.