Solved: Ucase

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
[VBA]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[/VBA]
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

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

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

Quote:

---

Originally Posted by 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

---

Hi sasa,

A simple example attached, but hopefully of help,

Mark

[VBA]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[/VBA]

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

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

Quote:

---

Originally Posted by 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

---

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

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

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

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

[vba]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[/vba]

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

[VBA]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[/VBA]

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

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

sasa