Special Cells Constants

[finspa]

Hi all

I have a question about special cells.

I am using Selection.SpecialCells(xlCellTypeConstants, 1).Select to copy / paste special - multiply to switch between GB£ and US$ in a large worksheet.

The problem is, the code is altering dates and percentages that should stay erm, constant.

Is there a way to tell the function to only look at certain fields, i.e. to ignore the dates and percentages?

My guess is that isn't easy as there is no real way to tell the difference between a number and a percentage value, so plan b would be to specify the columns that need updating. Is there an easy way to combine xlcelltyoeconstants and specified columns only, bearing in mind they are not necessarily next to each other. Example (cols B:P, U:V, AD:AJ, AL).select

Many thanks

 

[Jerry Sullivan]

Hi finspa,

If you are able to specify the columns, then your plan b could be done like this...

 With ActiveSheet
   Set rMyColumns = .Range("B:P,U:V,AD:AJ,AL:AL")
   Set rConstants = rMyColumns.SpecialCells(xlCellTypeConstants, 1)
 End With
 
 If Not rConstants Is Nothing Then
    '....your code continues

If the specific columns or ranges can't be identified at design time, an alternative would be to step through each cell of the range returned by SpecialCells, and only process those cells that have a NumberFormat property that doesn't end in "%". If you chose this option, it might be simpler to have VBA do the math on those cells instead of trying to do PasteSpecial with Multiply on this subset of your selection.

 

[finspa]

Hi finspa,

If you are able to specify the columns, then your plan b could be done like this...

 With ActiveSheet
   Set rMyColumns = .Range("B:P,U:V,AD:AJ,AL:AL")
   Set rConstants = rMyColumns.SpecialCells(xlCellTypeConstants, 1)
 End With
 
 If Not rConstants Is Nothing Then
    '....your code continues

If the specific columns or ranges can't be identified at design time, an alternative would be to step through each cell of the range returned by SpecialCells, and only process those cells that have a NumberFormat property that doesn't end in "%". If you chose this option, it might be simpler to have VBA do the math on those cells instead of trying to do PasteSpecial with Multiply on this subset of your selection.

Many thanks Jerry
Sorry for being a bit dumb but how would that fit with my code..

Application.Goto Reference:="Rate"
 Selection.Copy
 Selection.SpecialCells(xlCellTypeConstants, 1).Select
 Selection.PasteSpecial Paste:=xlValues, Operation:=xlDivide, SkipBlanks:= _
 False, Transpose:=False
 End Sub

 

[Jerry Sullivan]

Here's some code you can try...

Sub ConvertConstants()

 Dim rConstants As Range, rMyColumns As Range
   
 With ActiveSheet
   Set rMyColumns = .Range("B:P,U:V,AD:AJ,AL:AL")
   Set rConstants = rMyColumns.SpecialCells(xlCellTypeConstants, 1)
 End With
 
 If Not rConstants Is Nothing Then
   Application.Goto Reference:="Rate"
   Selection.Copy
   rConstants.PasteSpecial Paste:=xlValues, Operation:=xlDivide, _
      SkipBlanks:=False, Transpose:=False
 End If
 
End Sub