Problem when using SpecialCells

[HeadshotSam]

Hi,

I have been attempting to use SpecialCells in VBA to extract all the numbers in a column (of formulas) and 'paste' in another sheet.

The problem I'm having is the SpecialCells argument is only reading in the first instance where there is a number.

Code:

Option Explicit

Sub SpecialCellsTest()

Dim countCells As Integer
Dim cellsToExamine As Range
Dim cellsToPaste As Range

Application.ScreenUpdating = False

countCells = Range("count_cells").Value

Set cellsToExamine = Range("examine_cells")
Set cellsToPaste = cellsToExamine.Specialcells(xlCellTypeFormulas, xlNumbers)

Sheets(3).Range("A3:A" & 2 + countCells) = cellsToPaste.value

Application.ScreenUpdating = True

End Sub

In this code I have count_cells as a count of the numbers in the range and examine_cells as a column of cells with a mixture of unique numbers and repeated text.

Example Range to carry out the code on could be

1
Apple
2
3
Apple
4
Apple
Apple
5
6
7
etc.

(I am running into this problem in Excel 2007 and Excel 2010 both running Windows 7)

Thanks,
HSS

 

[Rick Rothstein]

Is this what you're after

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
    .Copy Range("B1")
    If .Areas.Count = 1 Then
        Range("B1").Resize(.Count).Value = Range("B1").Resize(.Count).Value
    End If
End With

No, because if the cells are contiguous, the formula copy as formulas meaning relative references get updated to the new cell location. The code I posted insures only the values from the original cells copies across.

 

[Jonmo1]

Yep, you're right...

I think we're trying to turn water into wine....

 

[Rick Rothstein]

Yep, you're right...

I think we're trying to turn water into wine....

There is another way (and it avoid the possible error that SpecialCells can generate and the handling of which has been ignored in my code)...

Range("B1:B11") = Application.Transpose(Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(ISNUMBER(A1:A11),A1:A11,"""")"))))))
Range("B1:B11").Replace "#N/A", "", xlWhole

 

[HeadshotSam]

Thanks All!

Carrying it out as a copy paste rather than setting the range in VBA worked great.

I guess if i needed to manipulate it in the sub I would need to loop through all the areas. Or simply take the range once I have carried out the copy paste already.

Anyway, this works just fine for my purposes at the moment, huge thanks to you all.

HSS