# Posts by Robert B

• ## Copy Column To New Sheet Based On Conditions

Re: Copy Column To New Sheet Based On Conditions

Hi

Create a Pivot Table to get a list of Departments, then use VLOOKUP with the Pivot Items as the lookup values and the data in Sheet as the lookup range.

HTH

Robert

• ## Tying Two Documents Together

Re: Tying Two Documents Together

Hi

Welcome to the forum.

In the completely different document, select the cell you wish to be completed and simply enter = (this will allow the selection of another cell in another workbook) then select the source cell, then press Enter.

HTH

Robert

• ## Calculating Difference Between Two Times

Re: Calculating Difference Between Two Times

Hi

I suspect that even with the Column width set at 255 all you will see will be #####..

You could try a conditional formula like

=IF(A2>A1,-(A1-A2),(A1-A2)

which removes the problem of the ###s but shows a positive solution whereas the result is negative.

The only way I can think of to get around this would be by VBA

Code
If Range("A2") < Range("A1") Then
Range("A3").Formula = Range("A1").Value - Range("A2").Value
Else
Range("A3").Formula = -(Range("A1").Value - Range("A2").Value)
Range("A3").Interior.ColorIndex = 3
End If

which still shows a positive value but highlights the cell in red, (I think)
HTH

Robert

• ## Format Cells As Fractions Of Seconds

Re: Format Cells As Fractions Of Seconds

Hi

Set the format in Custom Formats as mm:ss.00

HTH

Robert

• ## Last Used Cell

Re: Last Used Cell

Hi

this will identify the last used cell

Code
LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell)

The Row & Column numbers associated with the last cell can be found by

Code
LastRow = LastCell.Row
LastCol = LastCell.Column

Each assumes that the variables have been properly declared.

HTH

Robert

• ## Finding Values In Separate Workbooks

Re: Formula For Finding Values In Separate Workbooks

Hi

Try VLOOKUP, the wizard will guide you through the various entries.

HTH

Robert

• ## Now-time Changes In All Cells

Re: Now-time Changes In All Cells

Hi

if you wish to have the value rather than the variable each time the NOW function is entered you could include

Code
selection.Copy
selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

after the VB which creates the function

HTH

Robert

• ## Automatic Sort After Filter

Re: Automatic Sort After Filter

Hi

You could either sort the data before the filter is executed, or use Adavanced Filter, writing the filtered data to a new worksheet and sorting that.

HTH

Robert

• ## Null To Nullstring From Recordset To Array

Re: Null To Nullstring From Recordset To Array

Hi

you could replace the Null values with zero-length strings

Robert

• ## Pivot Table Columns In Month Order

Re: Pivot Table Columns In Month Order

Hi Rich

thanks for that, I don't have the book you mention but it sounds worth a try.

I eventually, this morning, solved the problem by creating a custom list of the month sin the order I needed and added a bit to the macro which sort sthe columns according to the custom list.

Regards

Robert

• ## Code Error

Re: Code Error

Hi

I assume your query will always return something even if it is Null, so try

Code
Dim Last_Row As Long
Last_Row = Range("A65535").End(xlUp).Row
If LastRow =<1 Then
Goto 10
End If
Range("I2:BU2").AutoFill Destination:=Range("I2:BU" & Last_Row), Type:=xlFillDefault

10

if this is not the case then try

Robert

• ## Time To String

Re: Time To String

Hi

generally, if an ' is placed before a numeric value the entry is rendered as a text string. Maybe this could be applied to your line of code

Code
F = d & ":" & Format(e)

so that it becomes

Code
F = "'" & d & ":" & Format(e)

although I am not sure how the next line of your code would react.

Robert

• ## Pivot Table Columns In Month Order

Re: Pivot Table Columns In Month Order

Its all done new each month with a macro which imports data from my accounts package. This data has a date in dd/mm/yyyy format, which the macro converts to Month, and it is this that is the column heading.

Robert

• ## Pivot Table Columns In Month Order

Hi All

I have a couple of Pivot Tables that I create each month summarising my Income and Expenditure. The only problem I have is making the columns appear in the correct order, i.e. where July is month1 through June(the following year) being month 12 to coincide with my financial year. Oddly enough last month (January) was fine, following December. This month January and February appear in columns 1 & 2.

Anybody got any suggestions

Thanks

Robert

• ## Pivot Tables With Vba

Re: Pivot Tables With Vba

Hi

you might find it more flexible to use VBA in the form below, rather than a recorded macro. This code will create a Pivot Table based on the data in the ActiveSheet, given that you identify the sources of the Rows,Column, Pages and Data.

Each time this is run a new Pivot will be created in a new Sheet

If you were to post your workbook containing the data I could fill in the headings etc.

HTH

Robert

• ## Code Error

Re: Code Error

Hi

not quite sure what you mean by the Dim function not performing. Dim is simply a way of declaring a variable. Do you mean that you don't want the LastRow variable to be set, and that the subsequent code should not run if LastRow is Null or zero?

Robert

• ## Variable Rows Macro

Re: Variable Rows Macro

Hi

Try Subtotals, at each Change in Name(?), function Sum on column B

Robert

• ## Identifying An Unknown Range

Re: Identifying An Unknown Range

Hi

try

Code
lr = Activecell.SpecialCells(xlCellTypeLastCell).Row

HTH

Robert

• ## Place Username In Directory Path

Re: Place Username In Directory Path

Hi

if I understand you correctly, replace

Code
"C:\Documents and Settings\XXXXXX\My Documents\Salary Review Data 2007 - " & country & ".xls" _

with

Code
"C:\Documents and Settings\" & User & "\My Documents\Salary Review Data 2007 - " & country & ".xls" _

Robert

• ## remove Last Character If "/" ; In A Whole Datasheet

Re: remove Last Character If &quot;/&quot; ; In A Whole Datasheet

Hi

when you say line do you mean record, and is it the last "/" in the last field of that record you wish to delete?

Robert