matching similar values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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:
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
 
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)
 
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
 
Doesn't it already do that?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
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:

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.
 
Back
Top