| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
matching similar values
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
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching similar values
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) "Tyrone" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching similar values
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" wrote: 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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching similar values
Doesn't it already do that?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyrone" wrote in message ... 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" wrote: 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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching similar values
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 "Bob Phillips" wrote: Doesn't it already do that? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyrone" wrote in message ... 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" wrote: 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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching similar values
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
thank you in advance. regards p.s. sorry, for my bad english. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup found two similar values | Excel Discussion (Misc queries) | |||
| Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
| Vlookup on a worksheet with similar values | Excel Worksheet Functions | |||
| Counting cells, similar values | Excel Worksheet Functions | |||
| Fill values into a listbox matching selected values from a combobox | Excel Programming | |||
Hybrid Mode
