PDA

View Full Version : Solved: Trying to compare two columns



AleemAM123
01-12-2009, 05:37 PM
Having a little problem (i think). I'm trying to check whether the are values in column R are similar to those in column D. So if in column D, I have 62TI_1308 and there is a 62TI_1308X in column R then I want the same row that 62TI_1308 but in column I to be set to the value of 62TI_1308X. I am not sure if I did it correctly but this is the coed I wrote:

Sub compareDCSerrorT()
'
' Macro written be Aleem Mohammed to determine if error check temperature tage is available frm the DCS
'
'

'

Dim rowcount As Long
Dim rowcount2 As Long

Dim i As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select

rowcount = Selection.Count



Range("r1").Select
Range(Selection, Selection.End(xlDown)).Select
rowcount2 = Selection.Count
result = rowcount2


Do While rowcount > 0

Do While rowcount2 > 0

If Cells.Item(rowcount, 4).Value Like Cells.Item(rowcount2, 18).Value Then Cells.Item(rowcount, 9).Value = Cells.Item(rowcount2, 18).Value

rowcount2 = rowcount2 - 1

Loop


rowcount = rowcount - 1
rowcount2 = result
result = result - 1

Loop

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True

End With


End Sub

mikerickson
01-12-2009, 06:41 PM
I think you may want to change this line
If Cells.Item(rowcount, 4).Value Like Cells.Item(rowcount2, 18).Value & "*" Then _
Cells.Item(rowcount, 9).Value = Cells.Item(rowcount2, 18).Value

Also you could use the syntax Cells(i,j) rather than Cells.Item(i,j)

AleemAM123
01-13-2009, 06:48 PM
Hi Mike, thanks for the help and the tip. I tried putting in my name as the last entry being checked in both columns and it seems that my program isn't working the way i though it should:dunno.

If Cells(104, 4).Value Like "*" & Cells(1285, 18).Value & "*" Then _
Cells(104, 9).Value = Cells(1285, 18).Value

i put Aleem at Cells(104, 4) and Aleem at Cells(1285, 18) and got nothing back in column I. It worked though when i remove the do while statements and only when Cells(1285, 18) = Aleem and not when it was AleemX.

georgiboy
01-13-2009, 11:34 PM
Try this, it does not use "Like" but it will match any given string within another string. For example if you have "GeorgeXYZ" in column R and "George" in column D it will find George within GeorgeXYZ and fill column I with GeorgeXYZ. From what i can understand this is what you require.

Sub Checker()
Dim rCell As Range, MyRange As Range

Set MyRange = Sheet1.Range("R2:R" & Range("R" & Rows.Count).End(xlUp).Row)

For Each rCell In MyRange.Cells

If rCell.Offset(, -14) <> 0 Then
If InStr(rCell.Value, rCell.Offset(, -14).Value) Then
rCell.Offset(, -9).Value = rCell.Value
End If
End If

Next

End Sub
Hope this helps

AleemAM123
01-14-2009, 03:38 PM
yeah, that's what I want but this is working either :dunno...

ok i got this to work:


Sub compareDCSerrorT()
'
' Macro written be Aleem Mohammed to determine if error check temperature tage is available frm the DCS
'
'

'

Dim rowcount As Long
Dim rowcount2 As Long
Dim i As Long
Dim searchval As String


With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
rowcount = Selection.Count

Range("r1").Select
Range(Selection, Selection.End(xlDown)).Select
rowcount2 = Selection.Count
result = rowcount2

Do While rowcount > 0

Do While rowcount2 > 0

searchval = Cells(rowcount, 4).Value & "*"

If Cells(rowcount2, 18).Value Like searchval Then _
Cells(rowcount, 9).Value = Cells(rowcount2, 18).Value


rowcount2 = rowcount2 - 1

Loop


rowcount = rowcount - 1
rowcount2 = result
result = result - 1

Loop

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True

End With


End Sub

AleemAM123
01-14-2009, 04:17 PM
thanks everyone for all your help.