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

jmoney1440p

New Member
Joined
Dec 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
625
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this:
VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this:
VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this:
VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
625
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Please try this one which includes the new criteria.
VBA Code:
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

New Member
Joined
Mar 31, 2015
Messages
17
Please try this one which includes the new criteria.
VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
625
Office Version
  1. 365
Platform
  1. Windows
Please try this:
VBA Code:
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

New Member
Joined
Mar 31, 2015
Messages
17
Please try this:
VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,674
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top