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

 

[JoeMo]

Seems that your cells to paste are not in a contiguous range so you need to loop through each area of the non-contiguous range.

 

[pgc01]

Hi

The assignment will only consider the first area of the range.

Try instead copy paste, ex.:

Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers).Copy
Range("B1").PasteSpecial xlPasteValues

 

[Rick Rothstein]

Try instead copy paste, ex.:

Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers).Copy
Range("B1").PasteSpecial xlPasteValues

You can simplify that by making it a one-liner...

Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers).Copy Range("B1")

 

[Jonmo1]

You can simplify that by making it a one-liner...

Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers).Copy Range("B1")

That would paste everything, values and formats AND formulas..

 

[Rick Rothstein]

That would paste everything, values and formats AND formulas..

Actually, the formulas do not seem to copy, but you are right... the formats and values do.

 

[Jonmo1]

That's interesting that the formuals don't get pasted...

 

[pgc01]

You can simplify that by making it a one-liner...

Hi Rick

You're right. I thought the OP only wanted the values, since he was doing a direct value assignment.

Your one-liner will not only copy values but also also copy formats, comments and data validation.

 

[JoeMo]

That's interesting that the formuals don't get pasted...

Formula cells that return numbers seem to paste as formulas in my simple test. If the xlNumbers argument is omitted, all formulas are pasted as formulas.

 

[Rick Rothstein]

Formula cells that return numbers seem to paste as formulas in my simple test. If the xlNumbers argument is omitted, all formulas are pasted as formulas.

You first sentence is not entirely clear. What was your simple test... omitting the xlNumbers or the statement that I posted in Message #4. It almost sounds like you are say that what I posted in Message #4 is copying the formulas for you.