How to make Excel recognise that the "" is blank?

[PATSYS]

Hi all,

I have a formula in A1:A10 that returns either a value or a "".

The result will be copied paaste special values in another cell, B1;B10.

Then I will copy B1 and paste specia, value and SKIP BLANKS in C1:C10

Problem is that excel pastes all the cells in C1:C10 i.e. it does not consider any cell as blank although there are soe with no value (as a result of the "" in the original formula).

Is there anyway for force excel to treat those as blank in such a way that it will work when I do paste special SKIP BLANKS?

Thanks

 

[xenou]

I agree with Jonmo. Why is there a problem with a column of numbers that are the result of a formula?

In any case, in your original copy/pasting series of steps, let your formula evaluate to the actual number or text, then copy. Leave the "" out of this altogether. Wouldn't that work too? If anything must be copied, copy the results of Jonmo's formula ...

If this doesn't make sense, maybe even post some sample data - I'd be happy to play around with it -- it does sort of feel like a non-problem that's somehow strangely getting in the way of what should be fairly straighforward.

 

[Peter_SSs]

... post some sample data - I'd be happy to play around with it -- it does sort of feel like a non-problem that's somehow strangely getting in the way of what should be fairly straighforward.

My thoughts exactly!

 

[xenou]

Well, got to playing around with a macro solution, which to me sounds better than copying and pasting - I can sort of see now that you want the values back in their original locations, and this will change them "in place" ... apologies if I missed an application of text to columns here, as suggested earlier.

Macro solution (improvements always welcome):

Sub Convert_Trailing_Negatives()
[COLOR="SeaGreen"]'//Code to change cells with trailing negative signs to normal numbers
'//Example: "3.14-" will become "-3.14"
'//Important: Code operates on whatever range is selected at runtime[/COLOR]

Dim r As Range, c As Range
Dim dblTemp As Double

Set r = GetTextCells(Selection)

If Not r Is Nothing Then
    For Each c In r
        dblTemp = ChangeMe(c)
        If dblTemp <> 0 Then
            c.ClearContents
            c.NumberFormat = "#"
            c.Value = dblTemp
        End If
    Next c
End If

End Sub
'-----------------------------------------------------------------------
Function ChangeMe(ByVal c As Range) As Double
On Error GoTo Handler
If Right(c.Value, 1) = "-" Then
    ChangeMe = -(Replace(c.Value, "-", ""))
End If
Handler:
End Function
'-----------------------------------------------------------------------
Function GetTextCells(ByRef r As Range) As Range
    On Error Resume Next
    Set GetTextCells = r.SpecialCells(xlCellTypeConstants, 22)
End Function

 

[Gadgetgav]

Hi Guys,

Sorry to hijack this thread but I have a similar (but possibly more complicated) issue to the OP. I have a workbook that will be sent out to managers to fill in comments. I don't want them to have access to the actual data so what happens is, they select their name from a drop down and it filters the data (on another sheet) then copies it to their sheet so they can fill in the comments.

The issue is that some of these managers will need to filter further by selecting an employee (as they will have multiple employees with multiple lines of data each). As it stands, I do this by clearing the visible data (on the managers tab) and refilling it with just the selected employee's data. The problem this creates is that it doesn't update the comments to the main data tab.

Ideally what I would like to do is have a vlookup (on a 3rd tab) that gets the comments and puts them on the relevant row (of the unfiltered data) then copy and paste special values with a skip blanks (so it doesn't overwrite any comments that have been placed on other lines) to the main data tab so that if they apply the filter again it will keep their comments. I know how to do this using a VBA loop through each row to check for comments but there will potentially be thousands of rows and the process is already getting slow due to everything else it does first.

What is the best way to either; trick Excel into thinking the Null Strings are blank for skip blanks, or just copying the required values without changing anything else?

p.s. At the time of the copy/paste the tab being copied will be unfiltered and show all data but the main data tab may be filtered so I want to copy each row to the correct counterpart row (e.g. C5 to C5 even if C5 is hidden on the "paste" tab)

Sorry if that was a bit long-winded thanks in advance for any help.

Gav<o></o>