Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
I'm new to xl vba - can u amend my code accordindly as per your
suggestion - Please...! Thxs |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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) |
Loop Column letter A to Z ??
Try Using
Application.intercept(Selection.SpecialCells(xlCel lTypeFormulas),Selection.SpecialCells(xlCellTypeVi sible)) to return the range to process. HTH Joseph Mc Daid |
Loop Column letter A to Z ??
where do i place it in my macro - i'm confused as that's all new to me
- how do i do it thxs |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
al007,
I must apologise, instead of Application.Intercept use Application.Intersect, it was a typo. HTH Joseph Mc Daid |
Loop Column letter A to Z ??
al007,
Can i assume that you have achieved your goal? Regards, Joseph Mc Daid |
Loop Column letter A to Z ??
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) |
Loop Column letter A to Z ??
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 |
Loop Column letter A to Z ??
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) |
Loop Column letter A to Z ??
do you have any macro which will work for both row & column
situation??? |
All times are GMT +1. The time now is 05:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com