PDA

View Full Version : [SOLVED] Changing formatting by writing all cells to an array



cplindem
03-12-2014, 04:34 PM
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
03-13-2014, 03:04 AM
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
03-14-2014, 05:30 PM
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
03-14-2014, 05:50 PM
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...
TempRange(iRow, iColumn).NumberFormat 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...
PercentFormat = CStr(TempRange(iRow, iColumn))
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
03-17-2014, 08:33 AM
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
03-18-2014, 01:26 AM
You want - right-aligned, how do you want to display other values?

cplindem
03-18-2014, 08:22 AM
Any other value can stay as is. Currently text is left-aligned. Numbers are right-aligned. I just need the hyphens to change.

snb
03-19-2014, 04:02 AM
see the attachment

cplindem
03-19-2014, 08:34 AM
I'm not seeing any VBA code in the attachment - just some numbers in a grey field.
What am I missing?

snb
03-19-2014, 09:57 AM
if you enter numbers >-1 they wiil align right, if <0 they will align left.

cplindem
03-19-2014, 10:58 AM
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
03-19-2014, 01:02 PM
see the attachment

cplindem
03-19-2014, 01:49 PM
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
03-19-2014, 02:14 PM
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
03-19-2014, 02:48 PM
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
03-19-2014, 03:32 PM
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
03-19-2014, 06:14 PM
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
03-20-2014, 01:57 AM
Before running the code replace any hyphen to a (any) number: could be zero, a date, a time, .9999, etc.

cplindem
03-20-2014, 08:25 AM
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
03-20-2014, 09:27 AM
You still don't get it.
Please read my posts again.

cplindem
03-20-2014, 10:30 AM
Maybe tell me what I'm missing. I tried reading your posts multiple times.

This is what I think you were suggesting:
1. Find and replace all hyphens to "x", where "x" is a number.
2. Format all numbers to be right aligned.
3. Find and replace all "x" to hyphens.

You're saying my understanding is still incorrect?

snb
03-20-2014, 02:14 PM
Not anymore.
but step 3 could also be: replace all 'X' by nothing/empty,nullstring, ""

You were looking for a simple, fast way to format certain cells. After running the code it doesn't matter anymore what the value of the cell will be: it will be aligned to the right.

cplindem
03-20-2014, 03:52 PM
Yeah, I understand that this will successfully right-align.

But I do need them to be hyphens in the end. And the problem with this method is that if I set "x" to a number, such as 99999, that number might actually appear in one of my files as a data point in the future. In step three, it will then change this potentially important data point 99999 to a hyphen along with all the other fake 99999 numbers, thus destroying the data point.

Zack Barresse
03-20-2014, 04:09 PM
So use an intermediary string. ? Or keep it to finding "-" and replacing with "-" like you had before. Doesn't that work for you?

snb
03-20-2014, 04:37 PM
I give up.

cplindem
03-20-2014, 04:47 PM
So use an intermediary string. ? Or keep it to finding "-" and replacing with "-" like you had before. Doesn't that work for you?
The code I posted on the last page works for me perfectly. That's why I marked the thread as SOLVED.

I was merely following through on snb's suggestion. I'm still learning VBA so I take every opportunity I can to understand more.