Excel Macro formatting cells if a % is in column a

nmoore1

New Member
Joined
Sep 8, 2014
Messages
5
I am trying to write code that would look in column A in the excel workbook (it is a text field with descriptions) if there a is a % in the description then format the 12 cells to the right as percent. and continue to identify all the data that has a % in column A.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Code:
Sub percentAllTheThings()

    Dim rng As Range
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)


    For Each cell In rng
        If InStr(1, cell.Value, "%") Then
            Range(cell.Address, cell.Offset(0, 12)).NumberFormat = "0%"
        End If
    Next cell
End Sub

Assuming that column A is text and will contain % somewhere in it.
 
Upvote 0
Like this
Code:
Sub sortapercent()
    Dim I As Integer
    For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, Range("A" & I).Value, "%") > 0 Then
            Range("A" & I).Offset(0, 12).NumberFormat = "0%"
        End If
    Next I
End Sub
 
Upvote 0
I am trying to make this part of a bigger macro and it works great on it's own, however, when I insert it into the main macro, I get a message saying Compile error: Statement invalid outside Type block. any sugestions?
 
Upvote 0
Code:
Sub percentAllTheThings()

    Dim rng As Range
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)


    For Each cell In rng
        If InStr(1, cell.Value, "%") Then
            Range(cell.Address, cell.Offset(0, 12)).NumberFormat = "0%"
        End If
    Next cell
End Sub

Assuming that column A is text and will contain % somewhere in it.

This is the codce that worked as a stand alone.
 
Upvote 0
Like this
Code:
Sub sortapercent()
    Dim I As Integer
    For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, Range("A" & I).Value, "%") > 0 Then
            Range("A" & I).Offset(0, 12).NumberFormat = "0%"
        End If
    Next I
End Sub


This code worked in my bigger macro, however, it only changed the format on the 12th column not all the columns between.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top