Search results

Hi, Brain, why change the application? Sub CarysW2() 'to make things simple both arrays are taken from the very same worksheet 'should work woth sheets on 2 workbooks as well Dim myarray As Variant Dim myStores As Variant Dim lngRowCounter As Long Dim lngColCounter As Long Dim lngColMax As...

Hi, CarysW, utilsing an array: Sub CarysW() Dim myarray As Variant Dim lngRowCounter As Long Dim lngColCounter As Long Dim lngColMax As Long Dim lngTargetRow As Long With Sheets("Sheet1").Range("A1") myarray = .CurrentRegion lngColMax = .CurrentRegion.Columns.Count End With...

Hi, tinynoo, maybe use ActiveCell.NumberFormat = "[h]:mm" in VBA or format the cells userdefined showing the format. Ciao, Holger

Hi, Rabbitoh, relate on a column where data is stored for every record (I assumed this to be Column A): With Range("A5:G" & Cells(Rows.Count, "A").End(xlUp).Row) .Sort Key1:=Range("B5"), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _...

Hi, I think it´s a totally different subject to the original threadwhich was to paste Formulae (by use of VBA). You ask for a Formula solution. The easiest way for me would to sum up the original data (using Sumproduct) instead fo copying the values and then summing up. And thinking about it...

Hi, E6: address is relative $E$6: address is absolute $E6: Column E is fixed E$6: Row 6 is fixed You´ll see the dfifference if you copy the formulas to different rows and/or columns. ;) Ciao, Holger

Hi, tinynoo, make use of the Autofilter for it (could be achieved without macros as well). Code goes into a normal module, starts the Autofilter, puts the items into the columns and then deletes all visible rows from row 2 to the end. At the end the Autofilter is resolved. Please mind that...

Hi, dusty, no - standard is rows to the top and columns to the left with no spaces in between. Maybe you could use graphics instead but you won´t be able to change values or formulas this way. Ciao, Holger

Hi, Dusty, I do have some problems understanding what you are after. Maybe the VBA Code Range("B2").EntireColumn.Hidden = (WorksheetFunction.CountIf(Range("A3:A" & Rows.Count), Range("B2")) > 0) may give you a hint where to go (if a column is hidden you can´t use the column itself for any...

Hi, Terry, I´d used that routine for a list and then run the rest. Please find a small sample just looping through the dates in Column A: Private Sub FindUniqueItems(UniqueItems As Variant, FilterRange As String) ' returns a list containing all unique items in the filter range Dim...

Hi, Private Sub CommandButton1_Click() With Sheets("Team DB") .Range("A3:D94").Sort _ Key1:=.Range("D3"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub Ciao, Holger

Hi, matt, I would use the autofilter for that (on column G) and copy all visible cells except for the headings. A macro recording of what I did: Sub Makro1() ' ' Makro1 Makro ' Makro am 05.08.2009 von HaHoBe aufgezeichnet ' ' Selection.AutoFilter Selection.AutoFilter Field:=7...

Hi, echorley, additional spaces? =IF(TRIM(A2)=TRIM(B2),"Yes","No") Ciao, Holger

Hi, David, have a try with Application.GetOpenFilename: Sub ParcelListingFormat() Dim varName As Variant Dim strAktVerz As String strAktVerz = CurDir varName = Application.GetOpenFilename("Text Files (*.txt), *.txt") If varName <> False Then Workbooks.OpenText Filename:=varName _...

Hi, Terry, maybe use the code from ErlandsenData: Option Explicit Option Base 1 Function FINDUNIQUE(InputRange As Variant, SortMode As Integer, Transp As Boolean) As Variant ' returns an array containing all the unique values in InputRange ' SortMode=0 : no sorting SortMode=1 : sort...

Hi, WR, try this: Sub wailingrecluse_Find() 'Find is trestriucted to Columns("A:A") of the active sheet 'Change this for your needs as well as the strSearch Dim rngFound As Range Dim strSearch As String Dim strAddress As String Range("A1").Select strSearch = 0 Set rngFound =...

Hi, try this code: Selection.AutoFilter Field:=2, _ Criteria1:=">" & CDbl(DateValue(Worksheets("Report").Range("B4").Text)), _ Operator:=xlAnd, _ Criteria2:="<" & CDbl(DateValue(Worksheets("Report").Range("B5").Text)) Ciao, Holger

Hi, Mr. B, AFAIK each instance of Excel running on a machine is isolated from each other one so only workbooks in one instance will ask for any action before closing down (Application.Quit) while all other instances are not affected. We´re talking about Excel Application.Quit and not Windows...

Hi, smiler, this should get you going: Sub smiler() Dim blnPersonal As Boolean Dim wb As Workbook For Each wb In Workbooks 'check for the personal macro workbook If InStr(1, "personl", wb.Name) > 0 Then blnPersonal = True Next wb ' as a booelan value of TRUE delivers -1 subtraction is...

Hi, wiklendt, use the proper event in the right place with correct parameter? :) Ciao, Holger