Changing formatting by writing all cells to an array

[cplindem]

I am trying to look through a large amount of data in a single worksheet and change the formatting for all cells containing percentages. I can do it using a simply For Each loop on each cell, but it's slow. I understand I can probably speed it up by writing to an array first, but I'm clumsy.

Sub Test10()
   
    Dim TempRange As Variant
    Dim iRow As Long
    Dim iColumn As Long
    Dim PercentFormat As String
                
    TempRange = Sheets(1).UsedRange.Value
    
    For iRow = LBound(TempRange) To UBound(TempRange)
        For iColumn = LBound(TempRange) To UBound(TempRange)
            PercentFormat = TempRange(iRow, iColumn).NumberFormat    '==== throws error, "object required"
                If PercentFormat = "0.00%" Then
                    PercentFormat = "0%"
                End If
        Next iColumn
    Next iRow
        
    Sheets(1).UsedRange.Value = TempRange
                
End Sub

Clearly I'm not using NumberFormat correctly on an object, but I can't figure out how to do that.
I also suspect I'm not describing LBound and UBound correctly.

Thanks

[Bob Phillips]

    Application.FindFormat.NumberFormat = "0.00%"
    Application.ReplaceFormat.NumberFormat = "0%"
    ActiveSheet.UsedRange.Replace What:="", _
                                  Replacement:="", _
                                  LookAt:=xlPart, _
                                  SearchOrder:=xlByRows, _
                                  MatchCase:=False, _
                                  SearchFormat:=True, _
                                  ReplaceFormat:=True

[cplindem]

Aha, that's way better. I kept looking for something like this by searching for conditional formatting in vba.
The only thing I was finding was FormatConditions, and that obviously does not contain NumberFormat.

I'm guessing writing to an array is helpful in some situations though?

Much thanks.

[Zack Barresse]

FormatConditions is conditional formatting, which is different than cell formatting, although it does contain it's own formatting.

For looping through multi-dimensional arrays, I'd recommend looking at the second (and optional) parameter of both Ubound/Lbound calls.

The reason you're getting the error is how you're using the array...
[vba]TempRange(iRow, iColumn).NumberFormat[/vba] It's not a Range object, so it won't have the NumberFormat property like a normal Range object would. It's an array, of which the dimensions you're specifying. Instead, that line should be...
[vba]PercentFormat = CStr(TempRange(iRow, iColumn))[/vba]
In the end, as Bob posted, a loop is severely inefficient with the ability to replace all formats in one fell swoop. But the methodology should serve well for you in the future.

HTH

[cplindem]

Thanks for the reply. Your explanation of the reason for error makes sense.
But I'm not seeing how your new line of code would return the number formatting.


Also, a followup question to xld's solution:
I would like to do something similar with alignment (Right align all cells that have a value of "-").

I know that the way I am trying to do it at the moment will not work, but I am hoping there is a similar solution.

Sub AlignHyphens()


    Dim Rng As Range
    Set Rng = Sheets(1).UsedRange
    Rng.FormatConditions.Delete


    With Rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="-")
        .HorizontalAlignment = xlRight
    End With


End Sub

I understand HorizontalAlignment will not work with FormatConditions, but I'm wondering if there's another similar Format type that will allow me to align.
I can get this to work by using a simple For Each loop, but there's so much data that the code is slow. xld's FindFormat solution was so much faster.

[Bob Phillips]

You want - right-aligned, how do you want to display other values?

[cplindem]

Any other value can stay as is. Currently text is left-aligned. Numbers are right-aligned. I just need the hyphens to change.

[snb]

see the attachment

[cplindem]

I'm not seeing any VBA code in the attachment - just some numbers in a grey field.
What am I missing?

[snb]

if you enter numbers >-1 they wiil align right, if <0 they will align left.

[cplindem]

Ah, I see. That's not quite what I'm going for.

All numbers currently align right in my file. Whether they are positive or negative is irrelevant. This is what I want, so they need no adjustment.
The problem is that hyphens are not aligning right. Not negative numbers, just cells with only "-" in them. This is happening because Excel is treating them as general text. I want them to align right as if they were numbers.

Does that make sense?

[snb]

see the attachment

[cplindem]

That is how I would like the alignments to work.
So how do I achieve that in VBA?

If it helps, the current code I'm using is:

    Dim Cell As Range
    Dim Rng As Range
    Set Rng = Sheets(1).UsedRange


    For Each Cell In Rng
        If Cell.Value = "-" Then
            Cell.HorizontalAlignment = xlRight
        End If
    Next Cell

This does exactly what I need it to do. It's just slow, because there are so many cells to look through. I was hoping there was a faster method, similar to FormatConditions or FindFormat.

[snb]

Assuming all cells in which - has to be aligned right, contain any kind of number.
After running the code every number that will be replaced by -, wil keep it's alignment at he right.

Sub M_snb()
    Cells.HorizontalAlignment = xlGeneral
    Cells.SpecialCells(2, 1).HorizontalAlignment = xlRight
End Sub

[cplindem]

SpecialCells seems promising. If my understanding of the method is correct, you are looking for constants that are numbers.

2 problems:

1) I don't think Excel is recognizing hyphens as a number. I think it is treating them as "general" cells. I think this is why they are currently left-aligned.
2) When I try to run this sub, Excel sits there running and running. There is a spinning wheel and everything becomes left-aligned, but nothing after that. I left it running for a few minutes, and it still hadn't finished. Furthermore, the VBA icon on my taskbar appears to keep closing and opening....very odd.

[snb]

In that case: forget the first code line.

You have to define beforehand which cells may contain '-'.
Do not fill them with '-' but with a number (e.g. zero 0)
Then run the macro.
After you have run the macro Excel 'knows' which cells (those containing numbers) to align right no matter if it contains a number or a hyphen afterwards.
So starting without any hyphen before running the code is crucial. !

then use :

Sub M_snb() 
    usedrange.HorizontalAlignment = xlGeneral 
    Cells.SpecialCells(2, 1).HorizontalAlignment = xlRight 
End Sub

If the first line takes too much time simply ignore it.

[cplindem]

Do you mean I have to change all the hyphens to zeros and then change them back to hyphens afterwards?
That won't work because there are some zeroes in the file. Zeros need to stay as zeros. And hyphens need to stay as hyphens.

I could change all the hyphens to some arbitrary number, then change them back to hyphens afterwards, but there may be a case where this arbitrary number occurs somewhere in a file. That would be a problem.

Am I understanding correctly?

[snb]

Before running the code replace any hyphen to a (any) number: could be zero, a date, a time, .9999, etc.

[cplindem]

Hmmm, yeah as I mentioned, that's a problem. I can't change it to a number because whatever number I choose may occur elsewhere in the file (or in a future file). I can't have that overlap because then an actual data point will be switched to a hyphen after the alignment finishes.

Anyway, I was actually able to find another chunk of code elsewhere that works for my purposes:

Sub AlignHyphens()
Application.ReplaceFormat.HorizontalAlignment = xlRight
Sheets(1).UsedRange.Replace What:="-", Replacement:="-", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
End Sub

[snb]

You still don't get it.
Please read my posts again.