Solved: Upper, Proper, and Date Formats

[Sir Babydum GBE]

Hi,

I download an Excel csv report every day which I then need to work on.

The first thing I do is tidy it up.

Is there a way of getting vb to...

Convert B2:E10000, G2:I10000, K2,L10000 to Proper Case
Convert M2:M10000 to UPPER CASE
Examine J2:J10000 and, if a text entry conver to UPPER, otherwise as a number.

I currently am doing this by using formulae and pasting values over the originals then deleting the formulae.

Is there a quicker way?

Thanks

BD

[Ken Puls]

Hello BD,

To be honest, I'd just apply a formula via code to the required range, set the value of the range you want converted to the formula range's value, then clear the formula range.

So where data is in A1:A10
With ActiveSheet
.Range("B1:B10").FormulaR1C1 = "=Proper(RC[-1])"
.Range("A1:A10") = .Range("B1:B10").Value
.Range("B1:B10").ClearContents
End With

[Bob Phillips]

Good grief, our man is back!



Public Sub Test()
Dim cell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet
For Each cell In .Range("B2:E10000, G2:I10000, K2,L10000")
cell.Value = Application.Proper(cell.Value)
Next cell
For Each cell In .Range("M2:M10000,J2:J10000")
cell.Value = UCase(cell.Value)
Next cell
End With
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
End Sub

[Ken Puls]

Bob, would you really loop through over 90,000 cells over doing a copy/paste of the formulas? Whie I haven't ever timed it beyond user feel, I would have thought that would be a lot slower... even with calc set to manual.

[Sir Babydum GBE]

Thanks Both

Yep - I'm back!

Bob, I'm getting an error and the line that's being highlighted is:

ActiveSheet
(Complie Error: Invalid use of property)

Odd isn't it, then again... Nihil simul inventum est et perfectum

[Sir Babydum GBE]

...oh, there was a missing "With"...

[malik641]

If it's the same range every time, then what about using a template workbook with 2 sheets? The first sheet is for the CSV download, and the second is all the formulas that refer to the ranges on the CSV sheet. Sort of like a Data sheet and a Report sheet.

Yay? Nay?

[Bob Phillips]

Bob, would you really loop through over 90,000 cells over doing a copy/paste of the formulas? Whie I haven't ever timed it beyond user feel, I would have thought that would be a lot slower... even with calc set to manual.

I wouldn't, but BD works for a major energy company, so he's got nothing to do all day!

[Ken Puls]

Ah, okay. Just checking! :)

[malik641]

I wouldn't, but BD works for a major energy company, so he's got nothing to do all day!:rotlaugh:

[mvidas]

Could always go overboard, ran quick though:Sub BDConvert()
ConvertForBD Range("B2:E10000,G2:I10000,K2:L10000"), vbProperCase
ConvertForBD Range("J2:J10000"), vbUpperCase, True
ConvertForBD Range("M2:M10000"), vbUpperCase
End Sub
Function ConvertForBD(RG As Range, ConvType As VbStrConv, Optional CheckText As Boolean)
Dim AR As Range, TempArr() As Variant, R As Long, C As Long
For Each AR In RG.Areas
TempArr = AR.Value
For R = 1 To UBound(TempArr, 1)
For C = 1 To UBound(TempArr, 2)
If CheckText Then
If Not IsNumeric(TempArr(R, C)) Then
TempArr(R, C) = StrConv(TempArr(R, C), ConvType)
End If
Else
TempArr(R, C) = StrConv(TempArr(R, C), ConvType)
End If
Next
Next
AR.Value = TempArr
Next
End Function

[unmarkedhelicopter]

How about if I changed my signiture to :-
"Most things are both conceived and screwed up, at the same time !"

[Sir Babydum GBE]

I wouldn't, but BD works for a major energy company, so he's got nothing to do all day!How dare you XLD!

You lot, can you not encourage him please!

Thanks for the answers everyone.

[Aussiebear]

I got the mail room blues!!!!

[Ken Puls]

How dare you XLD!

You lot, can you not encourage him please!

Is this one of those "truth hurts" things, or are you saying you don't?

:rotlaugh: