Recent content by RayH

The Workbook_SheetChange sub takes two parameters: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) The 'Sh' variable is used like this: For Each c In Sh.Range("I1:I485").Cells With this change the original code works fine. But Brian's updates are more efficient.

As I understand it, you want to be able to store the RC formula in the cell in a VBA string variable. Is that right? This will do that: x=SomeSheet.SomeRange.FormulaR1C1

You can create a pivot table from the data to do this. Select a couple of columns, your reference column along with one other. Drop the 2nd column into the values area and choose 'count' of data'. This will give you a unique list of references along with a count of how many you have of each...

This should give what you need. in G20: (weeks) =INT(ABS((E5-D5))/7) in G21: (days remaining) =INT(((ABS(E5-D5))/7-G20)*7) cannot combine the two as it will cause circular reference (the days calculation uses the week calculation). if decimal places are ok in the weeks calculation remove...

I recognize that code ;) Instead of the Output#2 line why doesn't the Call Shell cmd that you have work? Will DataQuant take a parameter? Call Shell("C:\Program Files\Rocket Software\Rocket Shuttle\Rocket Shuttle for Workstation\eclipse.exe " & Filelocation & Queryname, 1)).

Sub WriteQueryCelltoFile() Dim Filelocation As String Dim Queryname As String Dim ws As Worksheet Dim rownum As Long Dim colnum As Long Filelocation = "C:\queryfolder\" Queryname = "CellQuery.qry" rownum = 1 colnum = 1 Set ws = Worksheets("Sheet1") With ws Open (Filelocation &...

Re: Concatenate Rows of Text I created a macro for you to try. Sub concat() Dim n As Long Dim lastcall As String Dim calltext As String x = 2 ' Destination column number n = 2 ' Source Column number lastcall = Cells(n, 1) ' First Call Number Do While Cells(n, 1) <> "" ' Repeat until the...

WOW, FIVE YEARS!! Has it been this long, I had almost forgotten about this! :D I have no idea whether it worked for him or not but this section using from statement in my post should have returned something. The original code does appear, to me at least, to have a typo like he said. If the...

I have no idea when this change occured. If fact I don't know much about this function at all. Perhaps someone can explain the use of -- in there. The use of this function here doesn't work with out them and I cannot find an explanation in the help.

Isn't OPTION a reserved word? Could it be that?

The arrays are different sizes. This works. =SUMPRODUCT(--(B:B={1,4}),--(C:C={"Up","X"})) I put in an arbitrary value "X" to pad out the 2nd array.

Its a little bit beyond me but have you tried: Application.Visible = False show the appointment form and do stuff then show outlook again Application.Visible = True No idea whether this will work with you program though.

Can be controlled via a macro. Private Sub Document_Open() CommandBars("Status Bar").Visible = False End Sub Alt-F11, then this in the Normal Project, ThisDocument

You can insert a command button and link your macro to it.

Is it this you are after? Click on the 'ab' to create the field, then right click on it, Properties. Choose Current Date in the Type field. See attachment.