MrExcel Publishing
Your One Stop for Excel Tips & Solutions

learning macros please help


Posted by George on October 23, 2000 5:16 PM

I am new to this so this is probably easy i just
don't get it. I am learning how write my own macros
or at least trying. See my mess below

I want to check if a cell has a currency format
then i want to start a macro to paste special
(which i recorded). I tried to combine them but it
doesn't work. I also want to multiply the active cell
by 106 (to get to yen from dollars).

it doesn't like the cell("format",d31)...line which works in a
straight formula. I would also like to change d31
above to represent any current cell (so i can run it over
the whole spreadsheet).

for example if d31 is $5 and d32 is $7 i want those cells
to be 530 and 742, respectively and to get the format
from the paste special macro i recorded.

Sub findcurrency()
For Each cell In Selection
If (cell("format", d31) = "C0" Or cell("format", d31) = "C1") Then
With cell * 106

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


Posted by Ivan Moala on October 24, 2000 1:53 AM

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

Posted by George on October 24, 2000 11:15 AM

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

Posted by Ivan Moala on October 25, 2000 2:27 AM


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

Posted by George on October 25, 2000 10:18 AM

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.

Posted by Ivan Moala on October 28, 2000 5:28 AM

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

Posted by George on November 13, 2000 2:57 PM

THANKS A BUNCH IVAN

You are very helpful.

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