Solved: Ucase

[sasa]

I have this macro. It works by the way I can find four by four all names from cells 3 -> 9 and when equal to the unique name in cell 10 identify these by ucase replacement.
is able to
Sub Ucase()
Dim R
Ro = 12
Ru = 15
For R = 12 To 135 Step 4
For E = Ro To Ru
Testo = UCase(Cells(E, 10))
Range(Cells(R, 3), Cells(R + 3, 9)).Replace What:=Cells(E, 10), Replacement:=Testo, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next
Ro = Ro + 4
Ru = Ru + 4
Next
End Sub
Now I need a macro that works the same way but that is able to recognize not only a single name in cell 10 but what is in a range. I tried this way:Range(Cells(R, 3), Cells(R + 3, 9)).Replace What:=Range (Cells(E, 10), Cells(E+10,10)), but does not work.

Pls help
sasa

[GTO]

Greetings,

Sorry, but eh?

Please use the little green VBA button to wrap the code. It's but a wee bit of code, could you attach the wb? Sorry, but my poor dense noggin didn't follow...

Thanks,

Mark

[sasa]

Well, iI try to explain. I need a macro that compare two ranges of names,
When some name in the second range is equal to a name in the first range it is changed to uppercase.
thanks

[GTO]

Well, iI try to explain. I need a macro that compare two ranges of names,
When some name in the second range is equal to a name in the first range it is changed to uppercase.
thanks

Hi sasa,

A simple example attached, but hopefully of help,

Mark

Sub UcaseIfMatch()
Dim _
rngRangeOne As Range, _
rngRangeTwo As Range
For Each rngRangeOne In Sheet1.Range("B3:I3")
For Each rngRangeTwo In Sheet1.Range("B5:I5")
If rngRangeTwo.Value = rngRangeOne.Value Then
rngRangeTwo.Value = UCase(rngRangeTwo.Value)
End If
Next rngRangeTwo
Next rngRangeOne
End Sub

[GTO]

Should have mentioned, you can of course flip stuff around if I misunderstood which range should change to uppercase.

Have a great morning, this boy is about 5 hours past "dang, I should be asleep!"

Mark

[sasa]

Yes,thanks.. it works fine..
But only if I have a only name in a cell.
Unfortunately in my sheet I also have more names in an only cell. Have you a solution ?

Thanks a lot

[GTO]

Yes,thanks.. it works fine..
But only if I have a only name in a cell.
Unfortunately in my sheet I also have more names in an only cell. Have you a solution ?

Thanks a lot

Gosh, and for my being 'thicker' than usual, I was fairly estatic that the laptop didn't light itself on fire when F5 was pressed...

Okay though, what does, "But only if I only name in a cell" mean? And... "...I have more names in an only cell..."

Are you trying to say that more than one name could/would be in an individual cell?

Thanks,

Mark

[sasa]

Mark forgive me... In every cell of the two ranges yes I have more than one name
for example b3 = Mark Katia Ronald
b4 Jack Ralph
b5 Sharon Peter Mark
and so on

is it too difficult to do ?

Thanks

sasa

[mdmackillop]

Pleae post an example showing EXACTLY what you are after with typical data. This avoids confusion and wasted resources.

[GTO]

Hi sasa,

MD is correct of course, but since the range in your wb was rather large, I was already working on a solution (using much smaller ranges) to the 'multiple names in ea (or at least some) of the cells in either/both ranges.

By the way, "Jumpin' Jiminies!" - this was quite the stretch for me, but I think I got it! (he says deliriously...)

Mark

Sub UCaseIfMatch_2()
Dim _
rngRangeOne As Range, aNamesOne() As String, _
rngRangeTwo As Range, aNamesTwo() As String, _
intCount_Outer As Integer, strVal_rngOne As String, _
intCount_Inner As Integer, strVal_rngTwo As String
For Each rngRangeOne In Sheet1.Range("B7:I7")
aNamesOne = Split(Trim(rngRangeOne.Value))

For intCount_Outer = LBound(aNamesOne) To UBound(aNamesOne)
strVal_rngOne = Trim(aNamesOne(intCount_Outer))

For Each rngRangeTwo In Sheet1.Range("B9:I9")
aNamesTwo = Split(Trim(rngRangeTwo.Value))

For intCount_Inner = LBound(aNamesTwo) To UBound(aNamesTwo)
strVal_rngTwo = Trim(aNamesTwo(intCount_Inner))

If strVal_rngOne = strVal_rngTwo Then
aNamesTwo(intCount_Inner) = UCase(strVal_rngTwo)
End If
Next intCount_Inner

strVal_rngTwo = Empty
For intCount_Inner = LBound(aNamesTwo) To UBound(aNamesTwo)
If strVal_rngTwo = Empty Then
strVal_rngTwo = aNamesTwo(intCount_Inner) & " "
Else
strVal_rngTwo = strVal_rngTwo & aNamesTwo(intCount_Inner) & " "
End If
Next

rngRangeTwo.Value = Trim(strVal_rngTwo)


Next rngRangeTwo

Next intCount_Outer
Next rngRangeOne
End Sub

[GTO]

Or... if I was considerably brighter, I might have tried this...

Sub UcaseIfMatch_3()
Dim _
rngRangeOne As Range, _
rngRangeTwo As Range, _
aNamesOne() As String, _
intCount_Outer As Integer, _
strVal_rngOne As String

'// Adjust the second range as needed (as well as the first range further down). //
Set rngRangeTwo = Sheet1.Range("B9:I9")

'// Look in each cell from the first range //
For Each rngRangeOne In Sheet1.Range("B7:I7")

'// Create an array of value(s) from however many names in in the cell//
'// being looked at (while removing any leading/trailing spaces) //
aNamesOne = Split(Trim(rngRangeOne.Value))

'// Use the lower and upper bounds to see how many elements exist in the array//
For intCount_Outer = LBound(aNamesOne) To UBound(aNamesOne)

'// look at each element after trimming it //
strVal_rngOne = Trim(aNamesOne(intCount_Outer))

'// Then use the 'Replace' function to replace both the second loop and IF //
'// test in my previous example; as 'Replace' will look all through the //
'// second range and replace any values that match the current //
'// strVal_rngOne value (that is, in essence, the current element from the //
'// first range. //
rngRangeTwo.Replace aNamesOne(intCount_Outer), _
UCase(aNamesOne(intCount_Outer)), xlPart
Next intCount_Outer
Next rngRangeOne
End Sub

Unless I goobered something I am unaware of, this should be pretty efficient. BTW, this one is compliments of Demosthine, a buddy, who are a sight smarter than yours truly...

Hope this helps, and have a great day,

Mark

[sasa]

Thanks a lot Mark. That's just what I need

sasa