macros

  • Thread starter Thread starter Rajan Rai
  • Start date Start date
R

Rajan Rai

Hello

I have simple problem with a simple keyboard macro.
I am coping from internet banking and pasting banking
statements in excel. Unfortunately, the numbers are in
text format because the coping inserts a space infront of
all the numbers. I have tried functions to remove the
space. No luck. so I pressed the following key sequence;
f2, home, delete, enter. Great the leading space is gone.
I then go to record a keyboard macro with same key
storkes. It does not work. What happeens when I run the
macro is that number in the cell where I made the macro is
copied to all other cells. For example:

if the number in a cell is 29.99 and I record the macro
with the above key stokeds and run it in another cell it
copies 29.99 in all the cells. Can anyone tell me what is
wrong. Also, I looked at the macro the keystorkes, they
are wrong. For some reason Excel is not recording
keystorkes properly. Any help is apprecaited. Please
reply to (e-mail address removed). Thank you all.
 
I think what happen is when you record the Macro, it only works with that
particular cell. What you need to do is change the cell reference to
Activecell
e.g. in your macro code, it might appear range(CELL ADDRESS).select for
example, replace with activecell.value

I wrote a some code which may help try and see if it helps.

Option Explicit
Dim myRange As Range
Dim counter As Long
Dim myValue As Double

Sub Convert()

Dim myCells As Long

Set myRange = Range("b2:c8")
myCells = myRange.Count

counter = 1
Do Until counter = myCells + 1
On Error Resume Next
Call doit
counter = counter + 1
Loop
End Sub
Sub doit()
myValue = myRange.Item(counter).Value
myRange.Item(counter).Value = myValue
End Sub
 
Back
Top