learning macros please help

Range("1.data entry'!$B$29").Select Selection.Copy Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False

George
It doesn't like the cell("format",d31) because it
is not part of the VBA syntax....what you are after
is a VBA equavalent OR the use of the Native applications formulas which are available via
Application.Worksheetformulas........having said this
The worksheet function for this is not avail.
But have a look any way for worksheet functions
you can use, be aware how ever that when doing or
designing big projects that there can be a overhead
or lag time to pay when over using these functions
and a VBA option may be preferable. Anyway back to
your problem.....you are on the right track for
learning VBA ie. use of the macro recorder and looking
at the code and trying to combine.....just a bit more practice and also looking at what is happening.
Be aware that the macro recorder only records what
you do....ie. hard codes your actions and probably
puts more code in the macro then is what is required, also some actions are not recorded.

Have a look at one solution to your problem.
This solution will seach the activesheet for data
of the type constant....i.e Not formulas or text
or errors......

Sub ChangeCurr()
Dim CurRg 'The Currency Data Range
Dim oCell 'Each cell in this range
Dim Fmt As String'The cell format

'Set CurRg to ALL cells with Constants
Set CurRg = Selection.SpecialCells(xlCellTypeConstants, 1)
'Check each cell in the currency range of data
For Each oCell In CurRg.Cells
'What format is this cell
Fmt = oCell.NumberFormat
'Now check to see if it is a currency
If Fmt Like "*$*" Then
'If it is then multiply and change format
With oCell
.Value = oCell * 106
.NumberFormat = "General"
End With
End If
Next oCell
End Sub

..HTH

Ivan

Range("1.data entry'!$B$29").Select Selection.Copy Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False

Thanks Ivan. Hopefully this site will pull me along in the learning curve

I copied this in and it works as is.
I am trying to tweek it somewhat to
also do formulas (i figured out how to do formulas or
constants, but not both yet) and I want to format the cells
with a custom format. (i don't even know if this
is possible, but i will keep trying).

thanks again

To do formulas & constants then just combine eg

Sub ChangeCurr()
Dim CurRg, FrmRg, CurRg_FrmRg
Dim oCell
Dim Fmt As String

'Set CurRg to ALL cells with Constants
Set CurRg = Selection.SpecialCells(xlCellTypeConstants, 1)
'Set FrmRg to ALL cells with Formulas
Set FrmRg = Selection.SpecialCells(xlCellTypeFormulas, 23)

'Now combine
Set CurRg_FrmRg = Application.Union(CurRg, FrmRg)
'Check each cell in the currency range of data
For Each oCell In CurRg_FrmRg.Cells
'What format is this cell
Fmt = oCell.NumberFormat
'Now check to see if it is a currency
If Fmt Like "*$*" Then
'If it is then multiply and change format
With oCell
.Value = oCell * 106
.NumberFormat = "General"
End With
End If
Next oCell
End Sub

To use custom formate then look up formats
or custom formats

Ivan

Thanks again for your help.

This works perfectly for changing the current values.
Is there a way to keep the formula in the cell and add
a piece to it?
For example =a5*b5, i would like to make it =a5*b5*c5.
Essentially add a given cell "c5" to ALL formulas
that have a currecny format.

Just a few changes gets it to work as defined ??
Please get back if not correct.

Sub ChangeCurr()
Dim CurRg, FrmRg, CurRg_FrmRg
Dim oCell
Dim Fmt As String

'Set CurRg to ALL cells with Constants
Set CurRg = Selection.SpecialCells(xlCellTypeConstants, 1)
'Set FrmRg to ALL cells with Formulas
Set FrmRg = Selection.SpecialCells(xlCellTypeFormulas, 23)

'Now combine
Set CurRg_FrmRg = Application.Union(CurRg, FrmRg)
'Check each cell in the currency range of data
For Each oCell In CurRg_FrmRg.Cells
'What format is this cell
Fmt = oCell.NumberFormat
'Now check to see if it is a currency
If Fmt Like "*$*" Then
If oCell.HasFormula = True Then
'If it is then multiply and change format
With oCell
.Formula = oCell.Formula & "*C6"
End With
Else
With oCell
.Value = oCell * 106
End With
End If
End If
Next oCell
End Sub

THANKS A BUNCH IVAN

You are very helpful.

I will continue to learn this stuff and hopefully I can help others as well