Search first row for specific string, if found - percentage entire column

[jmoney1440p]

Hi there, I am looking for assistance in writing VBA code to do the following.

1) Loop through the first row of every column
2) I want to identify every column where the first row contains the string "rate" in any variation.
3) Select all columns w/ "rate" in the first row and percentage their contents with decimal moved 2 places.

I work with large amounts of data and currently have to manually "percentage-ize" the columns that contain rates in them. I would like a macro that reads the first row (column header) and checks if the word "rate" is present, if true, percentage-ize the entire column.

I've tried something like this below, but my "rng" is returning an error, and I don't know too much about VBA.

Sub RateCheck()

Dim rng As Range
Dim cl As Object
Dim strMatch As String

strMatch = "rate"
Set rng = ActiveWorkbook.UsedRange.Rows(1).Cells

For Each cl In rng

If InStr(cl, strMatch) > 0 Then
cl.Columns.Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
End If
Next


End Sub

 

[Osvaldo Palmeiro]

Hi. Try this:

Sub RateCheck()
 Dim rng As Range, rngAdd As String
  Set rng = Rows(1).Find("rate")
   If Not rng Is Nothing Then
    rngAdd = rng.Address
     Do
      With Columns(rng.Column)
       .Style = "Percent"
       .NumberFormat = "0.00%"
      End With
      Set rng = Rows(1).FindNext(rng)
     Loop While Not rng Is Nothing And rng.Address <> rngAdd
   End If
End Sub

 

[jmoney1440p]

Hi. Try this:

Sub RateCheck()
Dim rng As Range, rngAdd As String
  Set rng = Rows(1).Find("rate")
   If Not rng Is Nothing Then
    rngAdd = rng.Address
     Do
      With Columns(rng.Column)
       .Style = "Percent"
       .NumberFormat = "0.00%"
      End With
      Set rng = Rows(1).FindNext(rng)
     Loop While Not rng Is Nothing And rng.Address <> rngAdd
   End If
End Sub

Thank you so much, Osvaldo, you are a legend.

 

[jmoney1440p]

Hi. Try this:

Sub RateCheck()
Dim rng As Range, rngAdd As String
  Set rng = Rows(1).Find("rate")
   If Not rng Is Nothing Then
    rngAdd = rng.Address
     Do
      With Columns(rng.Column)
       .Style = "Percent"
       .NumberFormat = "0.00%"
      End With
      Set rng = Rows(1).FindNext(rng)
     Loop While Not rng Is Nothing And rng.Address <> rngAdd
   End If
End Sub

Follow up question if you don't mind, what would be the easiest way to add additional values to be checked for? What if I wanted to check for not only "rate" but also "%" or "percentage" using the same logic.

 

[Osvaldo Palmeiro]

Please try this one which includes the new criteria.

Sub StringsCheck()
 Dim rng As Range
  For Each rng In Range("A1", Cells(1, Columns.Count).End(1))
   If InStr(rng.Value, "rate") Or InStr(rng.Value, "%") Or InStr(rng.Value, "percentage") Then
    Columns(rng.Column).Style = "Percent"
    Columns(rng.Column).NumberFormat = "0.00%"
   End If
  Next rng
End Sub

 

[miva0601]

Please try this one which includes the new criteria.

Sub StringsCheck()
Dim rng As Range
  For Each rng In Range("A1", Cells(1, Columns.Count).End(1))
   If InStr(rng.Value, "rate") Or InStr(rng.Value, "%") Or InStr(rng.Value, "percentage") Then
    Columns(rng.Column).Style = "Percent"
    Columns(rng.Column).NumberFormat = "0.00%"
   End If
  Next rng
End Sub

This works beautifully! I'd like to divide the values by 100 before formatting into percentage - could you please add this to your macro?

 

[Osvaldo Palmeiro]

Please try this:

Sub StringsCheck()
 Dim rng As Range
  For Each rng In Range("A1", Cells(1, Columns.Count).End(1))
   If InStr(rng.Value, "rate") Or InStr(rng.Value, "%") Or InStr(rng.Value, "percentage") Then
    With Range(Cells(2, rng.Column), Cells(Rows.Count, rng.Column).End(3))
     .Value = Evaluate("=" & .Address & " / 100")
     .Style = "Percent"
     .NumberFormat = "0.00%"
    End With
   End If
  Next rng
End Sub

 

[miva0601]

Please try this:

Sub StringsCheck()
Dim rng As Range
  For Each rng In Range("A1", Cells(1, Columns.Count).End(1))
   If InStr(rng.Value, "rate") Or InStr(rng.Value, "%") Or InStr(rng.Value, "percentage") Then
    With Range(Cells(2, rng.Column), Cells(Rows.Count, rng.Column).End(3))
     .Value = Evaluate("=" & .Address & " / 100")
     .Style = "Percent"
     .NumberFormat = "0.00%"
    End With
   End If
  Next rng
End Sub

Thank you so much!