Sub testrow()
Dim c As Range, i As Integer 'I INTEGER???
Dim ReplaceFrom As String
Dim ReplaceTo As String

ReplaceFrom = InputBox("Letter to be replaced - Same case")
ReplaceTo = InputBox("Letter to be replaced to- Same case:")

For Each c In Selection.SpecialCells(xlCellTypeFormulas)
With c
For i = A To Z ????????
.Formula = Replace(.Formula, i & ReplaceFrom, i &
ReplaceTo)
Next i

.Formula = Replace(.Formula, ReplaceFrom & "$", ReplaceTo &
"$")
.Formula = Replace(.Formula, "(" & ReplaceFrom, "(" &
ReplaceTo)
End With
Next c
End Sub

CAN ANYBODY CORRECT THIS MACRO OR GIVE ME THE CODE WHICH WOULD LOOP
FROM A-Z (ReplaceFrom & ReplaceTo being row numbers)

Thxs

Perhaps you could loop from 65 to 90, and user chr(i) to retrieve the
character. chr(65) returns capital A while chr(90) returns capital Z.

HTH

Joseph Mc Daid

I'm new to xl vba - can u amend my code accordindly as per your
suggestion - Please...!
Thxs

To Expand:

For i = 65 To 90
.Formula = Replace(.Formula, chr(i) & ReplaceFrom, chr(i) &
ReplaceTo)
Next i

In this sufficient to solve your problem?

Joseph Mc Daid

Looking at your code i am uncertain as to what it is trying to achieve.
Perhaps if you provided a detailed explanation as to its purpose. Im
not even certain that the code snippet i provided earlier does anything
sensible, but i provided it in response to the following request: GIVE
ME THE CODE WHICH WOULD LOOP FROM A-Z.

Joseph Mc Daid

thxs !!! it works
I my range code good???
how can I amend my range code to make it work only on selected visible
cells only
pls

The macro is used to replace row numbers in cells containing formula
only
e.g
if cell = =Sheet2!K10*10
Replacing row10 by 20 would only result in =Sheet2!K20*10 (& not
multiplied by 20)

Try Using
Application.intercept(Selection.SpecialCells(xlCel lTypeFormulas),Selection.SpecialCells(xlCellTypeVi sible))
to return the range to process.

HTH

Joseph Mc Daid

where do i place it in my macro - i'm confused as that's all new to me
- how do i do it
thxs

Instead of using For Each c In
Selection.SpecialCells(xlCellTypeFormulas)
Use: For Each c In
Application.intercept(Selection.SpecialCells(xlCel lTypeFormulas),Selection.SpecialCells(xlCellTypeVi sible))


I havent tested this code but it should work bar some sytax error

Joseph Mc Daid

Guess i should stop bothering you - but it does not work - getting a
bug message
regs

Joseph Mc Daid wrote:
Instead of using For Each c In
Selection.SpecialCells(xlCellTypeFormulas)
Use: For Each c In
Application.intercept(Selection.SpecialCells(xlCel lTypeFormulas),Selection.SpecialCells(xlCellTypeVi sible))


I havent tested this code but it should work bar some sytax error

Joseph Mc Daid

al007,

I must apologise, instead of Application.Intercept use
Application.Intersect, it was a typo.

HTH

Joseph Mc Daid

al007,

Can i assume that you have achieved your goal?

Regards,

Joseph Mc Daid

Surely, this won't work. If you try to replace a letter, it might also
replace that letter not in the cell ref, such as the S in sheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"al007" wrote in message
oups.com...
The macro is used to replace row numbers in cells containing formula
only
e.g
if cell = =Sheet2!K10*10
Replacing row10 by 20 would only result in =Sheet2!K20*10 (& not
multiplied by 20)

Joseph,
Have achieved my goal
I'm very grateful for your instant reply & hope you will continue
supporting me for any future topic I might be posting
thxs thxs a lot
(For Each c In
Selection.SpecialCells(xlCellTypeFormulas).Special Cells(xlCellTypeVisible)
works equally well- I tried it before you fixed the bug)





Joseph Mc Daid wrote:
al007,

Can i assume that you have achieved your goal?

Regards,

Joseph Mc Daid

Bob,
Got another macro to replace column letters
thxs

Phillips wrote:
Surely, this won't work. If you try to replace a letter, it might also
replace that letter not in the cell ref, such as the S in sheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"al007" wrote in message
oups.com...
The macro is used to replace row numbers in cells containing formula
only
e.g
if cell = =Sheet2!K10*10
Replacing row10 by 20 would only result in =Sheet2!K20*10 (& not
multiplied by 20)

do you have any macro which will work for both row & column
situation???