matching similar values

[Guest]

hello,
i am quite new to vba programming and i need help with a makro. i want to
compare two columns and write "true" or "false" or something similar in
another column, if the values are similar. the similarity is limited to
lower/upper case letters.

here is my code:

Option Compare Text

Public Sub StringVergleich()
'----- Declaration -------------------
Dim i, j, ez, lz, kez, klz, es, kz, ws As Integer
Dim arr As Long
Dim CpyStrArray(1, 3 To 5233)
Dim CpyStrInt As Integer
Dim CpyStr As Variant
Dim ObjStr As Variant

'----- Variables ----------------------
ez = 3
lz = 2200
kez = 3
klz = 5233
es = 14
kz = 15
ws = 20


For j = ez To lz
ObjStr = Cells(j, 11).Value
For i = kez To klz
CpyStr = Cells(CpyStrArray(1, i), 20)
'CpyStr = Cells(CpyStrArray(i), kz).Value
If Trim(LCase(CpyStr)) Like Trim(LCase(ObjStr)) Then
Cells(j, ws).Value = "True"
Exit For
Else
Cells(j, ws).Value = "False"
End If
Next i
Next j
End Sub

i tried a lot of algorythms and here is my actual. one column (from 3 to
5233) should be compared with the smaller one (3 to 2200). if the smaller
clumn contains one of the values listed in the bigger one, the value of a
cell in the next column to the right schould be overwritten by "true"/"false".

thanks for your help,
regards

 

[Bob Phillips]

ez = 3
lz = 2200
kez = 3
klz = 5233
es = 14
kz = 15
ws = 20

For j = ez To lz
Cells(j, "L").Value = Not IsError(Application.Match( _
Cells(j, "K").Value, Columns("T:T"), 0))
Next j


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

thank you.
it works great and fast, but is it possible to change the letters of the
values in the "columns"-syntax with a lcase-operator or something similar to
ignore upper case and lower case letters in the cells?

regards

 

[Bob Phillips]

Doesn't it already do that?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

yeah, right, it does. my apologies, great code it saves a lot of time and
avoids spaghetti code.

thanks again, have a nice day.
regards

 

[Guest]

hello,
i am sorry to bother you again. i need something similar to this: instead of
writing true or false in a cell, the color(background and/or -it doesnt
matter) of a cell/string should be changed if the comparison matches. here is
the code:

For i = 1 To 7
lzi = lzi & i
For j = ez To lzi
MyString = Cells(j, i).Value
Select Case Len(MyString)
Case Is = 0
Exit For
Case Else
MyString = Left(MyString, InStr(MyString, "."))
MyString = Replace(MyString, ".", "")
End Select
'-- Should be altered to something like MyString.Font.Color:
MyString = Not IsError(Application.Match( _
Cells(j, i).Value), Columns("S:S"), 0)
Next j
Next i

i have tried a lot of , but i always recieve errors.
thank you in advance.

regards

p.s. sorry, for my bad english.