Application.Trim with VBA
 

Hello,

This is my VBA procedure.
I wanted to know whether if was possible to subsitute "(Range("a5"))" with
"a" in order to have a generic and moving range?
Dim a As Long
Dim myRng As Range
x = Mid(Application.Trim(Range("a5")), 12, 20)
For a = 7 To 100
If a Mod 2 = 0 Then
If Cells(a, 1).Value 0 Then
Cells(a, 2).Value = x
Else
Cells(a, 2).Value = 0
End If
End If
Next a



--
Regards,
Jeff

Application.Trim with VBA
 

Hi Jeff,

Perhaps it is the use of "a" that you want to be generic than A5 and
are already using a variable named "a" in you macro. I can't tell what
you want to be more generic, what is to change and what is not to change..

If you want to pick a cell to start with
double-click, right-click
you can use an Event Macro
http://www.mvps.org/dmcritchie/excel/event.htm
in which case Range("A5") would be target



See TrimALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jeff" wrote in message ...
Hello,

This is my VBA procedure.
I wanted to know whether if was possible to substitute "(Range("a5"))" with
"a" in order to have a generic and moving range?
Dim a As Long
Dim myRng As Range
x = Mid(Application.Trim(Range("a5")), 12, 20)
For a = 7 To 100
If a Mod 2 = 0 Then
If Cells(a, 1).Value 0 Then
Cells(a, 2).Value = x
Else
Cells(a, 2).Value = 0
End If
End If
Next a



--
Regards,
Jeff

Application.Trim with VBA
 

I'd suggest passing the range to a generic sub like:

Sub MySubst(Rg as Range)
Dim a As Long
x = Mid(Application.Trim(Rg.Value), 12, 20)
For a = 7 To 100
If a Mod 2 = 0 Then
If Cells(a, 1).Value 0 Then
Cells(a, 2).Value = x
Else
Cells(a, 2).Value = 0
End If
End If
Next a
End Sub

Called like this:

MySubst Range("A5")

--
Jim
"Jeff" wrote in message
...
| Hello,
|
| This is my VBA procedure.
| I wanted to know whether if was possible to subsitute "(Range("a5"))" with
| "a" in order to have a generic and moving range?
| Dim a As Long
| Dim myRng As Range
| x = Mid(Application.Trim(Range("a5")), 12, 20)
| For a = 7 To 100
| If a Mod 2 = 0 Then
| If Cells(a, 1).Value 0 Then
| Cells(a, 2).Value = x
| Else
| Cells(a, 2).Value = 0
| End If
| End If
| Next a
|
|
|
| --
| Regards,
| Jeff
|