VBA help to Find Specific text within cell then resize (Not Autofit)

[XfortunaX]

Hi,

I am stuck on what I think should be a very easy part of code but I cannot get it to go and it happens to be the last piece needed.

I was hoping to find "Ticket Number" within column E. If the cell contains "Ticket Number" then resize the row height to 112.50 or 150 pixels and resize the column width to 31.00 or 222 pixels. Each table has a different length and I start on row 2.

Any help is appreciated.

Thanks!

 

[revcanon]

Try this:

    LastRow = Range("E" & Rows.Count).End(xlUp).Row 'I am ssuming you have a value in every row in column E
    Dim i As Integer
    For i = 2 To LastRow Step 1 'skips your header row
    If Range("E" & i).Value = "Ticket Number" Then
        Columns("E:E").ColumnWidth = 31
        Rows(i).RowHeight = 112.5
    Else
    End If
    Next i

 

[XfortunaX]

Thank you for taking the time Revcanon to respond!

Yes, all of the cells in column E have a value.

The code provided runs through but still is not picking up "Ticket Number". I provided the image below to see how the sheet is set up. The issue is there are about 1200 rows to sort through and only a few with Ticket Number that need the special formatting.

Line 4 is how the row looks when sheet is downloaded. I expanded row 1 to show the amount of information in one cell. I played with turning 'Wrap Text' on and off but it did not solve the issue.

[/URL][/IMG]

Once again, thank you for any information you can provide.

 

[revcanon]

I was assuming the entire contents of the cell would be "Ticket Number". This should look for anyone that contains "Ticket Number" anywhere in the cell:

    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim rngx As Range
    Dim i As Integer
    For i = 2 To LastRow Step 1 'skips your header row
    Set rngx = Range("E" & i).Find("Ticket Number", lookat:=xlPart)
    If Not rngx Is Nothing Then
        Columns("E:E").ColumnWidth = 31
        Rows(i).RowHeight = 112.5
    Else
    End If
    Next i

Let me know if that works for you.

 

[XfortunaX]

Yes, that was it. Thank you so much!

 

[revcanon]

After looking it over, i realized a minor change needed to be made to make this go from bottom to the top to avoid missing rows.

    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim rngx As Range
    Dim i As Integer
    For i = LastRow to 2 Step -1 'skips your header row
    Set rngx = Range("E" & i).Find("Ticket Number", lookat:=xlPart)
    If Not rngx Is Nothing Then
        Columns("E:E").ColumnWidth = 31
        Rows(i).RowHeight = 112.5
    Else
    End If
    Next i

That should resolve any missed rows the first code would have missed.