Search results

Hi, wiklendt, if you use the events that Excel gives to classes your very own way runtime errors will most certainly happen... ;) The procedure you installed is a workbook event and thus must be placed in ThisWorkbook. If you want to change it to a Worksheet-Event change to the according...

Hi, wiklendt, AFAIR Excel is a spreadsheet, not a word processor ;) I like it if I can line up the numbers neatly, dan that´s good enough for me (I´ll use Word if I want more).. The difference between macro and VBA in excel isn´t that strict as in Access because you got the possiblility to...

Hi, =RIGHT("00000" & B2,5) & " " & RIGHT("000" & C2,3) & " " & RIGHT("000000000" & D2,10) & " " & RIGHT("0000" & E2,4) & " " & RIGHT("00000" & F2,5) Ciao, Holger

Hi, Matt, why don´t you use the Autofilter and copy all records using SpecialCells(xlCellTypeVisible) instead of looping? Private Sub Button790_Click() Dim wsOrigin As Worksheet Dim wsDest As Worksheet Dim lngFirstFree As Long Dim lngLastRow As Long Dim lngRowCounter As Long...

Hi, smiler44, ActiveWorkbook.SaveAs pat & dirr & subdirr & fle, FileFormat:=xlNormal xlNormal isn´t the choice for an *.xlsm which would be xlOpenXMLWorkbookMacroEnabled. Please have a look at Use VBA SaveAs in Excel 2007 or use the macro recorder to get the code. Ciao, Holger

Hi, Geoff, as the value has to be inside a given range try this: =IF(AND(B1>=B2*0.9,B1<=B2*1.1),C1,IF(C1=1,2,1)) HTH Holger

Hi, Geoff , if you could use another cell - use column C for this and put a 1 in C1 and the formula into the next ones: =IF(B1=B2,C1,IF(C1=1,2,1)) Then you can adjust to shade the ranges according to 1 and 2 in column C. Ciao, Holger

Hi, smiler44, please have a look at Add buttons to the QAT and customize the images of the buttons by Ron de Bruin. Ciao, Holger

Hi, Joe, no formatting applied by now (and no error handling), only a new sheet added with the data (hopefully ;)). Private Sub FindUniqueItems(UniqueItems As Variant, FilterRange As String) ' returns a list containing all unique items in the filter range Dim TempList() As String Dim...

Hi, Joe, to get the total volume you could use a pivottable to deliver the data. Sorry, no good idea yet for the Evaluation. Do you really just want the frst 4 items listed or go as long as there are different ones? Ciao, Holger

Hi, smiler44, I find it more useful to use an oject for doing so and relying on the object instead of Windows(xxx) or Workbooks(xyz), this is the idea how to go for it: Sub smiler44() Dim wkbToOpen As Workbook Dim strPath As String Dim strFile As String strFile = "smiler44.xls" strPath =...

Hi, smiler44, FileSearch isn´t available in the Application Model of 2007, you could use Dir instead. For an example please see No Application.Filesearch in 2007. Ciao, Holger

Hi, namliam, what about the use of SumProduct (not to be used with entire columns)? Ciao, Holger

Hi, smiler44, the "magic" word is scope: if a variable is dimensioned in a module it is only known in that module. If you dimension a variable with Dim in the head of a module it will only be available to the procedures in that module (except they dimension a local version of the same name)...

Hi, Guirg, you want Strings in these cells? Otherwise you should consider DateSerial or CDate. Sorry but at the moment I don´t have a clue why November does fine and December just causes a runtime error. Ciao, Holger

Hi, Bev, have you tried a go with Conditional Formatting? You could use a constant values instead of building the date by formula. Ciao, Holger

Hi, LadyDi, I´m not very good at guessing but maybe SUMPRODUCT could do a better job. And there are plenty of functions still available which might suit better. ;) Ciao, Holger

Hi, LadyDi, if you can´t use the Average-Function you might use SumIf and CountIf instead and build your formula on these functions. Ciao, Holger

Hi, Guirg, IsEmpty (maybe with the use of Trim to get rid of spaces) or IsNumeric (please mind that Dates are considerd numeric as well)/IsText. If IsEmpty(ActiveCell.Value) Or IsNumeric(activeCell.Value) Then Ciao, Holger

Hi, Guirg, maybe test like this: With wbS.Sheets("Sheet1") LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 .Cells((LastRow), 1).PasteSpecial End With Or even With wbS.Sheets("Sheet1") LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 .Cells((LastRow)...