+ Reply to Thread
Results 1 to 6 of 6

Chenge Text in Cell

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Chenge Text in Cell

    Hi. I've manage to create a macro to change a text of a cell in several sheets. The problem is that this workbook i have has 365 sheets started by TT followed by another sheets, but the ones it mathers are the ones started as TT. Now i managed this macro
    Sub Change_Text_String()
    
    ScreenUpdating = False
    
        Sheets("TT1").Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Medium"
        Sheets("TT2").Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Medium"
        Sheets("TT3").Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Medium"
        Sheets("TT4").Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Medium"
        Sheets("TT5").Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Medium"
    ScreenUpdating = True
    
    End Sub
    and put the screen updating so its faster, but i have to write down the same piece of code for each one of the 365 TT sheets which is quite boring. I know there is a way to say in VBA something like For each ws. in this workbook started as "TT*" at this range do this, but dont know how to write it. By the way the range is always the same and all i want is to make a massive change of a cell text thats all.

    Thank you

  2. #2
    Registered User
    Join Date
    07-08-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Chenge Text in Cell

    how about

    Sub aaaa()
    
    Application.Volatile
    
    Dim i As Long
    
    On Error Resume Next
        For i = 1 To Sheets.Count
        Sheets(i).Range("D1").Value = "Medium"
        Next i
    On Error GoTo 0
    
    
    End Sub

  3. #3
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Chenge Text in Cell

    But like this it doesnt change the value to all sheets int he workbook? Because i want to change this value in the worksheets that start as TT only. Workbook as sheets named MENU, TOOLS, TT1, TT2 ... TT365, DIARY, etc.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,607

    Re: Chenge Text in Cell

    Try
    Sub test()
        Dim ws As Worksheet
        For Each ws In Worksheets
            If ws.Name Like "TT*" Then ws.Range("d1").Value = "Medium"
        Next
    End Sub

  5. #5
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Chenge Text in Cell

    @jindon

    this seems more like it. Gonna try it. Thanks both.

  6. #6
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Chenge Text in Cell

    @jindon

    Fantastic. Perfect. Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1