Dynamic reformatting multiple cells using Offset or Resize - how can I make this code more efficient?

[davlcam]

Hello

I am developing a dynamic sheet, in which some of the data reported in each row is dependent on the result from a look up options.

The data returned in the specified cells of each row can be either an Integer, or a rational number to the first or second decimal. Data can also be percentage to the first decimal, percentage with no decimal, and currency with no cents.

I use a value in column N, which is returned with the lookup, to serve as an indicator for the format that should follow. For example, if the lookup for N results in '#.1', the subsequent cells should be reformatted to #.#.

I have multiple cells that need to be formatted, and they are not all subsequent.

Here is an example of the code I am using.

For Each y In Range("N9:N100")

    If y = "#.1" Then y.Offset(0, 4).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 5).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 8).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 10).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 12).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 13).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 14).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 15).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 16).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 17).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 18).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 19).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 20).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 22).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 24).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 26).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 27).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 28).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 29).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 30).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 31).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 32).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 33).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 34).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 35).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 36).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 37).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 38).NumberFormat = "0.0"
    If y = "#.1" Then y.Offset(0, 39).NumberFormat = "0.0"

Next

1. I have to repeat this series for each possible value of N, which (as a reminder) right now consists of 6 possible results. (Integer, Rational to 1 decimal, Rational to 2 decimals, Percentage, Percentage to 1 decimal, and currency with no cents.)

2. As you can see, there are a few groups of cells that are perfectly sequential.

Now this code works, but its just very long, and I think there is a more efficient way to write it.

I was experimenting with using the Resize function (VBA) but i couldn't quite get it. If anyone has any ideas for anything more efficient, please let me know.

Again, not urgent, my code works, its just a bit silly.

 

[cstimart]

This should be slightly better... (untested)

For Each y In Range("N9:N100")
    If y = "#.1" Then
        y.Offset(0, 4).NumberFormat = "0.0"
        y.Offset(0, 5).NumberFormat = "0.0"
        y.Offset(0, 8).NumberFormat = "0.0"
        y.Offset(0, 10).NumberFormat = "0.0"
        y.Offset(0, 12).NumberFormat = "0.0"
        y.Offset(0, 13).NumberFormat = "0.0"
        y.Offset(0, 14).NumberFormat = "0.0"
        y.Offset(0, 15).NumberFormat = "0.0"
        y.Offset(0, 16).NumberFormat = "0.0"
        y.Offset(0, 17).NumberFormat = "0.0"
        y.Offset(0, 18).NumberFormat = "0.0"
        y.Offset(0, 19).NumberFormat = "0.0"
        y.Offset(0, 20).NumberFormat = "0.0"
        y.Offset(0, 22).NumberFormat = "0.0"
        y.Offset(0, 24).NumberFormat = "0.0"
        y.Offset(0, 26).NumberFormat = "0.0"
        y.Offset(0, 27).NumberFormat = "0.0"
        y.Offset(0, 28).NumberFormat = "0.0"
        y.Offset(0, 29).NumberFormat = "0.0"
        y.Offset(0, 30).NumberFormat = "0.0"
        y.Offset(0, 31).NumberFormat = "0.0"
        y.Offset(0, 32).NumberFormat = "0.0"
        y.Offset(0, 33).NumberFormat = "0.0"
        y.Offset(0, 34).NumberFormat = "0.0"
        y.Offset(0, 35).NumberFormat = "0.0"
        y.Offset(0, 36).NumberFormat = "0.0"
        y.Offset(0, 37).NumberFormat = "0.0"
        y.Offset(0, 38).NumberFormat = "0.0"
        y.Offset(0, 39).NumberFormat = "0.0"
    End If
Next

Or, you could format the entire range "0.0" and when Y <> "#.1" then remove the formatting on the 10 specific cells?

 

[cstimart]

On second thought, couldn't you use conditional formatting on those specific columns?

 

[ChrisM]

Still not perfect, but you could go this route:

Dim i As Long
Dim aRows As Variant

aRows = Array(4, 5, 8, 10, 12, 13.........) 'keep adding all your offset numbers here

For Each y In Range("N9:N100")
    If y = "#.1" Then
         For i = 0 To UBound(aRows, 1)
                y.Offset(0, aRows(i)).NumberFormat = "0.0"
         Next i
   End If
Next y

 

[davlcam]

Hmm, wow, simple conditional formatting might work after all.

My company was stuck in 2003 for so long that I still think in terms of those limitations some times.

Tested it. Wasn't sure if it would work with dynamic changes based on lookups but it looks like the reformatting will actually work dynamically.