When Two Vales are the Same

[jimbledon]

Hi,

I have a spreadsheet that varies often. The first tab as the changing list ("Lun Masking") whilst the second is the definitive list ("Un Assigned Volumes").

The numbers i'm trying to compare have been converted to strings and range from 0000 to 0439 in hex.

I'm trying to create some code to run through a column in ("Lun Masking") and when it matches a number in ("Un Assigned Volumes") it inputs a string value in the next free column which is the L row in the loop position.

For Each c In Worksheets("Lun Masking").Range("D5:D310").Cells
Debug.Print ("LunMasking " + c)

        For Each v In Worksheets("Un Assigned Volumes").Range("A10:A1010").Cells
                If c.Value = v.Value Then
                    'ActiveCell.Font.Bold = True
                    Worksheets("Un Assigned Volumes").Cells
                    Exit For

                End If
        Next
Next

My question is: how can i get it 'IF' statement to find the L column in the second loop. And is there an easier way of achieving this?

Thanks in advance

James

 

[PHV]

Something like v.Offset(0, 11) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chance1234]

Sub codem()
Dim intR, intR2 As Integer

intR = 2

Do Until intR = 310
     
    intR2 = 2
    Do Until intR2 = 1010
       If Sheets("Lun Masking").Cells(intR, 1) = Sheets("Un assigned Volumes").Cells(intR2, 1) Then
            Sheets ("Lun Masking"), Cells(intR, 2) = Sheets("Un assigned Volumes").Cells(intR2, 12)
           Exit Do
        End If
        intR2 = intR2 + 2
    Loop
    
    intR = intR + 1
Loop


End Sub

Is this what you mean ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[Chance1234]

Apologies that was a bit messy here is a cleaner working version

Sub codem()
Dim intR, intR2 As Integer

intR = 2

Do Until intR = 310
     
    intR2 = 2
    Do Until intR2 = 1010
       If Sheet1.Cells(intR, 1) = Sheet2.Cells(intR2, 1) Then
            Sheet1.Cells(intR, 2) = Sheet2.Cells(intR2, 12)
           Exit Do
        End If
        intR2 = intR2 + 1
    Loop
    
    intR = intR + 1
Loop


End Sub

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[SkipVought]

Hi,

This looks like a simple shreadsheet lookup.
In the free column
[tt]
=if(isna(Match(A10,'Lun Masking'!$D$5:$D$310,0)),"","My String Value")
[/tt]
and copy down.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[combo]

As c and v are Ranges, it is possible to get additional info:
v.Row
v.Column
v.Address
etc.
You can search whole range using Find method, which should be faster.

combo