Solved: Find a string and align left

[fredlo2010]

Hi guys,

OK i don't know why every time I want to do something I can never find it online.

I want to create a small function to look for a string and align that cell to the left.

Then I would feed different variables.

The further i got was to find that there was something for find Method in VBA. My macro recorder does not get that.

Sorry for the bother. Playing with these codes is a little bit harder than I thought.

Still persistence is the key.

[jolivanes]

Try this

Sub TurnLeft()
    Dim ws As Worksheet
    Dim x As Range
    Set ws = ActiveSheet
    With ws
        Set x = Cells.Find(What:="10") '<------ Change the "10" to your string you're looking for.
        If Not x Is Nothing Then Range(x.Address).HorizontalAlignment = xlLeft
    End With
End Sub

[Bob Phillips]

Try this

Sub TurnLeft()
    Dim ws As Worksheet
    Dim x As Range
    Set ws = ActiveSheet
    With ws
        Set x = Cells.Find(What:="10") '<------ Change the "10" to your string you're looking for.
        If Not x Is Nothing Then Range(x.Address).HorizontalAlignment = xlLeft
    End With
End Sub

You wrap a With, but aren't using it

[VBA]Sub TurnLeft()
Dim ws As Worksheet
Dim x As Range
Set ws = ActiveSheet
With ws
Set x = .Cells.Find(What:="10") '<------ Change the "10" to your string you're looking for.
If Not x Is Nothing Then .Range(x.Address).HorizontalAlignment = xlLeft
End With
End Sub[/VBA]

[fredlo2010]

Hi guys,

Thanks a lot this works perfectly. I combined this new peice with another code to make a special string in my table bold and aligned to the left.

[VBA]Private Function Header_Transform(rng As Range, ByVal varWhat As String)

Dim ws As Worksheet
Set ws = ActiveSheet

'MAKE MY HEADERS BOLD

rng.Replace What:=varWhat, _
Replacement:=varWhat, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=True

'ALIGN MY HEADERS TO THE LEFT

With ws
Set rng = .Cells.Find(What:=varWhat)
If Not rng Is Nothing Then .Range(rng.Address).HorizontalAlignment = xlLeft
End With

End Function[/VBA]

[VBA]Sub MakeHeadersBold()

Application.ReplaceFormat.Font.FontStyle = "Bold"

Call Header_Transform(Range("A1:A10000"), "Text")
end sub[/VBA]

Tanks once again

[fredlo2010]

Again a spoke too soon it only aligns the first instance.

[VBA]Sub MakeHeadersBold()

Application.ReplaceFormat.Font.FontStyle = "Bold"

Call Header_Transform(Range("A1:A10000"), "text1")
Call Header_Transform(Range("A1:A10000"), "text2")[/VBA]

It will make bold all of them and align to the left only "text1"

[GTO]

The vba help topic has a decent example for the .Find Method. You will see that .FindNext is needed in a loop if the first .Find returns a Range.

[fredlo2010]

I tried this but still does not work.

GTO, thanks for the tip on the Help website. My local computer VBA help was not giving me anything.

[VBA]Private Function Header_Transform(rng As Range, ByVal varWhat As String)

Dim ws As Worksheet
Set ws = ActiveSheet

'MAKE MY HEADERS BOLD

rng.Replace What:=varWhat, _
Replacement:=varWhat, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=True

'ALIGN MY HEADERS TO THE LEFT

With ws
Set rng = .Cells.Find(What:=varWhat)
If Not rng Is Nothing Then
firstaddress = rng.Address
Do
.Range(rng.Address).HorizontalAlignment = xlLeft
Set rng = Cells.FindNext(rng)
Loop While rng Is Nothing And rng.Address <> firstaddress

End If
End With[/VBA]

[jolivanes]

@xld

Of Course.

Thanks

[GTO]

Currently in 2000, so left out a couple of arguments. Maybe:
[VBA]Option Explicit

Sub CallIt()
Dim rngLastRow As Range
Dim rngData As Range
Dim n As Long

With ActiveSheet
Set rngLastRow = RangeFound(.Range("A:A"))
If rngLastRow Is Nothing Then Exit Sub
Set rngData = .Range(.Cells(1), rngLastRow)
For n = 0 To 1
Call ChangeAlignmentOnMatch(rngData, Array("Test", "Blunder")(n))
Next
End With
End Sub

Function ChangeAlignmentOnMatch(Range2LookIn As Range, Text2LookFor)
Dim rngFound As Range
Dim strAddress As String

Set rngFound = RangeFound(Range2LookIn, Text2LookFor, , , xlWhole)
If Not rngFound Is Nothing Then
strAddress = rngFound.Address(0, 0, , -1)
rngFound.HorizontalAlignment = xlLeft

Do
rngFound.HorizontalAlignment = xlLeft
Set rngFound = Range2LookIn.FindNext(rngFound)
Loop While Not rngFound.Address(0, 0, , -1) = strAddress
End If
End Function

Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function[/VBA]

[fredlo2010]

Thanks GTO,

This works perfectly. But i was hoping for something simpler. I have the code yes but I have no idea how it works or how to modify or how to debug it if I even need to.

I was hoping to draw a line between that article you recommended me and whats I was doing. So i could use it later.

Maybe I am thinking the solution is simpler than it actually is. I want to get things done a learn at the same time

Thanks

[racksjackson]

[vba]public Sub FindSomeText()
If InStr("Look n search in this string", "look") = 0 Then
MsgBox "no match String"
Else
MsgBox "got one match"
End If
End Sub[/vba]
You could use excel Application find and search function.

[snb]

[VBA]Private Function Header_Transform(rng As Range, ByVal varWhat As String)

On Error Resume Next

Do

With rng.Find(varWhar, , xlValues, xlPart)

.HorizontalAlignment = xlLeft
.Value = Replace(.Value, varWhat,left(varWhat,1) & "_" & mid(varWhat,2))
End With
Loop Until Err.Number <> 0

rng.Replace "_", "",xlpart
End Function[/VBA]

[GTO]

...GTO, thanks for the tip on the Help website. My local computer VBA help was not giving me anything....

What year of Excel are you using? In Windows or Mac?

This works perfectly. But i was hoping for something simpler. I have the code yes but I have no idea how it works or how to modify or how to debug it if I even need to....

Until we see what the issue is with your access to the Help topics, I am not sure what to suggest. In the meantime, notice the comment of xld in post #3? At post #7 however, you are doing the same thing Bob mentioned; that is, you are not referencing what is in the With line by including a preceding Full Stop (.) at:

[vba]Set rng = Cells.FindNext(rng) [/vba]

Also, I think it would be helpful if you stayed on course until what we are trying to do is accomplished, vs. switching bits of it whilst we are midstream. In #5, we are looking in A1:A10000; in #7 we have changed to looking all over the sheet. My suggestion at #9 sort of combines the two, by looking in Column A, but only from A1 to the last cell with data.

Continuing with #7:
[vba]Loop While rng Is Nothing And rng.Address <> FirstAddress[/vba]
...is incorrect. The vba Help example shows 'Loop While Not...'.

Let us think through the tests. With the AND, we are only going to loop if both tests return True. The first test returns True if Nothing (ie - no range (a cell in this case)) is returned from .Find. That is, that .Find failed to find the value sought in the Range being searched. Well now, if we made it into the loop in the first place, as we are not changing the value sought, .Find will never fail to return a Range (even if it is just the same cell over and over).

As you wrote it, we will never loop as 'Loop While rng Is Nothing' will fail immediately.

Now it is probably worth mentioning that if we were searching for all values of, let us say "Sam" and replacing each occurrence with "Becky", then testing for Is Nothing would be sensible, as eventually "Sam" will not be found. In this case, we would want to loop as long as rng is Not Nothing, or:
[vba]Loop While Not rng Is Nothing[/vba]

Since we are not changing the returned cells' values though, we could skip that test and just test for FirstAddress.
[vba]Loop While rng.Address <> FirstAddress[/vba]

A final note on the vba help example - at least in 2000 (and I believe it stayed the same therafter), the help example is:

Find Method Example
 
This example finds all cells in the range A1:A500 on worksheet one that contain the value 2, and then it makes those cells gray.
 
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

As mentioned, unless I am really missing something (it happens), if we are not changing the value of what is sought, then I would just do the second test. If I am altering the value of what is sought, then just the first test.

As far as reading through my or anyone's code, take the time to step through it (F8), with the locals window displayed. A lot can be deciphered by watching what is happening as the code executes line-by-line. In the case where some of the variables are not local, there is certainly nothing wrong with making temporary local copies of the variables in each procedure, so that you can see what they are doing. Finally, if a seemingly complex bit of code has my eyes spinning, I figure out how to get just the part I don't understand into a simpler example.

Hope that helps,

Mark

[fredlo2010]

Hi GTO,

Wow thanks a lot for your post. As always as helpful as it can get. I feel I have some explaining to do though.

1. Regarding my help files I worded it out incorrectly.

My local computer VBA help was not giving me anything....

Should have been " My local computer VBA help was not giving me any good example." I do get the help files but they are very thin and general.

2. I am sorry about the range switching. What happens is that I was trying to incorporate the sub to a function so in the range was actually defined depending on my specification. Look at #4

[VBA]Private Function Header_Transform(rng As Range, ByVal varWhat As String)

Call Header_Transform(Range("A1:A10000"), "text1")[/VBA]

But you are right I should have not done that.

3. Thanks for all the tips. Things look a lot clearer. I had no idea on how to use the watch window. This is awesome now I can see how variables are evaluated and my objects...

I also learned about arrays and how to assign them to variables. Here is my finished code:


[VBA]Sub HeaderTransform()

Dim c As Range
Dim n As Long
Dim FirstAddress As Variant
Dim varArray As Variant


varArray = Array("Text1", "Text2", "Text3", "Text4")

With Worksheets("MyWorksheets").Range("A1:A500")

For n = 0 To 3
Set c = .Find(varArray(n), LookIn:=xlValues)

If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.HorizontalAlignment = True
c.Font.Bold = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
Next
End With
[/VBA]

the comment did help me a lot. This is a solved matter