Another approach using dictionary [code] Private Sub UserForm_Initialize() Dim arr As Variant arr = GetDistinct(Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)) ListBox1.List =...
Thanks a lot PNRao for this great solution Another approach using collection [code] Private Sub UserForm_Initialize() Dim ws As Worksheet...
Thanks vaali... this is the data sheet1 data EID NAME PAY PayDay 14123 Joe1 $1,234.00 29-Jun-17 14124 Joe2...
HI, Here is the required macro to search a field and if value found copy the whole row into another...
I found a solution like this :Records in between two specific dates easily can be filtered using drop-down lists ....
I found a solution as follows: Watch the video to see how it's done and download example file : https://youtu.be/SZduAoOtiS0
I have sorted the above problem. Code is given below... [code] Sub seperate() Call CleanSheets MsgBox "1 of 10 Cleaning...
I have the below code which will calculate the formula for a particular cell range. The range mentioned in formula will have numeral which will be in red color.
What I want to do is, I want the formula to ignore the cells that has “colored text”.
Sheets("WBR45").Range("AE103").Formula = "=PERCENTILE.INC(TP!$A$3:$D$30,99%)*24"
Just edit that part
rng.Address(, , , True)
Not clear but if you need to detect if the cell has colored text or not you can use something like that using loops
Dim cel As Range
For Each cel In Range("A1:A10")
If cel.Font.Color <> 0 Then
If the cell has colored text, then that cell should not be included in the formula. The cell with only black colored text should be taken for calculation
I think you have to loop the range first and store the cells ranges that match your criteria first then use this stored range in the formula
I am not sure .. Can you tell us what the formula do exactly?
The formula calculates the percentile from sheet “TP” and paste it in cell in WBR45 sheet
Can you upload sample of your workbook?
http://www.filedropper.com/wbrsample – Check TP sheet
Dim cel As Range
Dim rng As Range
Dim arr As Variant
Dim i As Long
Application.ScreenUpdating = False
For Each cel In Sheets("TP").Range("A3:D30")
If cel.Font.Color = 0 Then
If rng Is Nothing Then
Set rng = cel
Set rng = Union(cel, rng)
ReDim arr(rng.Count - 1)
If Not rng Is Nothing Then
For Each cel In rng
arr(i) = cel
i = i + 1
Sheets("TP").Range("AH1").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
Set rng = Sheets("TP").Range("AH1:AH" & Sheets("TP").Cells(Rows.Count, "AH").End(xlUp).Row)
Sheets("WBR45").Range("AE103").Formula = "=PERCENTILE.INC(" & rng.Address & ",99%)*24"
Sheets("WBR45").Range("AE103").Value = Sheets("WBR45").Range("AE103").Value
Application.ScreenUpdating = True
Application defined or Object defined error in line – Sheets("WBR45").Range("AE103").Formula = "=PERCENTILE.INC(" & rng.Address & ",99%)*24"
& rng.Address &
I have edited the code. Check the new one
Awesome!! This is exactly what I wanted.
But there is a small glitch…the calculation is not correct…I mean the result in the destination is in thousands and it should not be like that..it will be a two digits and few decimals..If i apply the formula directly I’m getting the correct answer
I dont find the above line in the code..should I add this?
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).