Search results

Also, try to use the method Find as shown before. The code is faster then whene using Loops. You'll search later to optimize code for maxima whene you've a lot of data and each millisecond will have its importance. regards.

Hi Shan Use Color in place of ColorIndex (I wrote it but you didn't read it)

Hi For Excel 2007 and later, try this array formula(Validate by Ctrl Shift Enter) =IFERROR(INDEX($A$1:$B$251, SMALL(IF($A$1:$A$251="amy",ROW($A$1:$A$251)),ROW(4:4)),2),"") Regards

Hi In C2 this array formula (validate pat Ctrl Shift Enter) {=MAX(IF($A$2:$A$100=A2,$B$2:$B$100,""))-MIN(IF($A$2:$A$100=A2,$B$2:$B$100,""))} Then drag to bottom

Hi SirJB7 Right! even if no effect is observed if we do not return to true in this case. By default, it is set to True when the next code is called

We need to check if LastLig >5. The code wille be: Sub Test() Dim LastCol As Integer, i As Integer Dim Sh As Worksheet Dim LastLig As Long Application.ScreenUpdating = False With Worksheets("Sheet1") LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column Set Sh =...

Thank you very much Sir SirJB7 Me too, without file, I just wrote what I guess doing the job. Waiting for feedback from Jediantman to see what is the result. Regards!

Hi Test this code Sub Test() Dim LastCol As Integer, i As Integer Dim Sh As Worksheet Dim LastLig As Long Application.ScreenUpdating = False With Worksheets("Sheet1") LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column Set Sh = Worksheets("Sheet3") Sh.UsedRange.Clear...

Hi shrivallabha Here my file where I did tests http://speedy.sh/Z75A8/Shrivallabha.xlsx And the image of the sheet http://speedy.sh/7ChXP/Shriva.jpg MOD(FLOOR(1.03,0.1),0.2)=0.2 in my excel :) FLOOR(1.03,0.1)=1 MOD(1,0.2)=0.2 Regards

Hi Use this code (writen in the module of the worksheet). It uses the event Change (ie, if the value of G7 changes, the code will be run automtically) You can adapte the row numbers Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address =...

Application.ScreenUpdating = True Should be at the end. As it means, False desactivate the screen updating. And if you delete the line Application.StatusBar = Format(i / Number_Of_Rows, "0%") what is the time with and without ScreenUpdating?

Hi, here your code without any select or Activate (they make wasting time and code stability) Sub AddGoal() Dim OldCarrier As String, CarrierName As String Dim NewCol As Integer Dim LastLig As Long Application.ScreenUpdating = False CarrierName = InputBox("Name of New Carrier&#34...

Hi, here file and code using Autofilter. (code using then event Change of the worksheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$3" Then With ActiveSheet .AutoFilterMode = False If Target.Value <> "" Then .Range("B5:B" &...

Hi Using filter in vba, if you have a file sample, I can try something by using filter in vba

150K a lot of rows. Did you try by usibng Filter

@Shantraj You wrote "The requirment is if a row in sheet 2 has red color the duplicate row in sheet 1 should apear in red" Yes, it's what the code does. Did you tried it? Your mistake was in the line cell1.EntireRow.Interior.ColorIndex = cell2.EntireRow.Interior.ColorIndex If the...

Yes Roy, Thanks God you're here to show me the way :) Of course, I forgot to mention that formula is an array forumla that should be validate by Ctrl Shift Enter(after translating formula in English). I've edited my post and correct it. Thanks Roy

Hi I suggest this code (more fast if you have a lot of rows) Sub FindDuplicates() Dim Rng As Range, c As Range, v As Range Application.ScreenUpdating = False With Worksheets("Sheet1") Set Rng = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row) End With For Each c In Rng...

HiAnother way by formula In G1 ( refering to file added by Debraj Roy) write this formula. It retrieves the first visible value in column C(after filter or not) {=INDIRECT("C"&MATCH(1,SUBTOTAL(3,OFFSET(C2:C100,ROW(C2:C100)-MIN(ROW(C2:C100)),,1)),0)+1)}

Hi Mike, Deb Another way to make Roy's code (interesting in case of many checkboxes and the way how are named these checkboxes) Private Sub UserForm_Initialize() Dim Tb, Elem Tb = Array("Cat", "Dog", "Rabbit", "Deer") For Each Elem In Tb...

Another way is using sub routine like this Sub Test() Dim Wbk As Workbook Dim MyFile As String Application.ScreenUpdating = False MyFile = "T:FilepathAut2012 Tracker Ann.xlsx" If Dir(MyFile) <> "" Then 'Test if the file exists Set Wbk =...

Hi Jediantman What a big code. To optimze it, use object variables without activing or selecting any thing. Also you can transfer values between workbooks without using Copy/PasteSpecial who needs more ressources. Here your code modified Sub Test() Dim aWbk As Workbook, Wbk As Workbook Dim...

Hi SirJB7 I'll begin from my PS3: Спасибо за поощрение PS2: Thanks for encouragement Right,ranges described at 0 Regards

Hi SirJB7 Congratulations about your formula If will maximum at 2.5, your formula will =MIN(MAX(MROUND(M2,0.2)+0.1,0.7),2.5) Note that formula fails if negative numbers, so to solve this problem =MAX(MROUND(M2,0.2*SIGN(M2))+0.1,0.7) In general if the minima is So (here 0.7) and the maxima...

In M24 you have the data and you need te have corresponding result at column 1 (Formula in N24)and wish to drag formula to data at N25 corresponding to column 2 At N24, this formula =VLOOKUP($M$24,$A$1:$X$200,ROWS($M$23:$M24),FALSE) here column 1 Darg it to N25 you will have...