Find and Replace with Change in Size and Left Align
Results 1 to 4 of 4

Thread: Find and Replace with Change in Size and Left Align
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Find and Replace with Change in Size and Left Align

    Greetings in ExcelLent World!
    Please help me to find some ways to add some codes to add to change font size and alignment (left or right). Thank You@

    Sub FindReplaceAlignSize()
    Dim i As String
    Dim k As String
    i = "Signature"
    k = "Verified as to the prescribed office hours:"
    'Change the Range A1 and G1
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Columns(1).Replace what:=i, replacement:=k, lookat:=xlWhole, MatchCase:=False
    End With
    End Sub

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,931
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace with Change in Size and Left Align

    Hi,
    try

    Code:
    Sub FindReplaceAlignSize()
        Dim i As String, k As String
        Dim firstaddress As String
        Dim rng As Range
        
        i = "Signature"
        k = "Verified as to the prescribed office hours:"
        
    'Change the Range A1 and G1
        With Sheets("Sheet1").Columns(1)
            Set rng = .Find(what:=i, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not rng Is Nothing Then
                firstaddress = rng.Address
                Do
                    With rng
                        .Value = k
                        .Font.Bold = False
                        .Font.Size = 12
                        .HorizontalAlignment = xlLeft
                    End With
                    Set rng = .FindNext(rng)
                    If rng Is Nothing Then Exit Sub
                Loop While rng.Address <> firstaddress
            End If
        End With
        
    End Sub
    change sheet name shown in RED as required

    Dave

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace with Change in Size and Left Align

    Quote Originally Posted by dmt32 View Post
    Hi,
    try

    Code:
    Sub FindReplaceAlignSize()
        Dim i As String, k As String
        Dim firstaddress As String
        Dim rng As Range
        
        i = "Signature"
        k = "Verified as to the prescribed office hours:"
        
    'Change the Range A1 and G1
        With Sheets("Sheet1").Columns(1)
            Set rng = .Find(what:=i, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not rng Is Nothing Then
                firstaddress = rng.Address
                Do
                    With rng
                        .Value = k
                        .Font.Bold = False
                        .Font.Size = 12
                        .HorizontalAlignment = xlLeft
                    End With
                    Set rng = .FindNext(rng)
                    If rng Is Nothing Then Exit Sub
                Loop While rng.Address <> firstaddress
            End If
        End With
        
    End Sub
    change sheet name shown in RED as required

    Dave
    Nothing happens Sir.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,931
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace with Change in Size and Left Align

    Quote Originally Posted by sirmacademy View Post
    Nothing happens Sir.
    Code will in sheet named "Sheet1" Column 1 - find value defined in variable "i" & replace it with value defined in variable "k"
    It will also allow you to set Font Size, Alignment etc

    If code is not working it is either not looking at correct worksheet (did you rename sheet as stated) or the search value defined in variable i does not exist in the range.

    If still having problems, place copy of you workbook with sample data in a dropbox & provide link to it.

    Dave
    Last edited by dmt32; Jul 4th, 2019 at 08:34 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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