Loop Column letter A to Z ??

[al007]

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

 

[Joseph Mc Daid]

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

 

[al007]

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

 

[Joseph Mc Daid]

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

 

[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

 

[al007]

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

 

[al007]

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 Mc Daid]

Try Using
Application.intercept(Selection.SpecialCells(xlCellTypeFormulas),Selection.SpecialCells(xlCellTypeVisible))
to return the range to process.

HTH

Joseph Mc Daid

 

[al007]

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

 

[Joseph Mc Daid]

Instead of using For Each c In
Selection.SpecialCells(xlCellTypeFormulas)
Use: For Each c In
Application.intercept(Selection.SpecialCells(xlCellTypeFormulas),Selection.SpecialCells(xlCellTypeVisible))


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

Joseph Mc Daid

 

[al007]

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

 

[Joseph Mc Daid]

al007,

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

HTH

Joseph Mc Daid

 

[Joseph Mc Daid]

al007,

Can i assume that you have achieved your goal?

Regards,

Joseph Mc Daid

 

[Bob Phillips]

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]

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).SpecialCells(xlCellTypeVisible)
works equally well- I tried it before you fixed the bug)

 

[al007]

Bob,
Got another macro to replace column letters
thxs

 

[al007]

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