help shorten code

[xxentric]

This code im running works... but there is alot of repetition here, can someone take a look to see if there are ways to shorten this

Dim lookFor1, lookFor2, lookFor3, lookFor4, lookFor5, lookFor6 As Range
Dim lookFor7, lookFor8, lookFor9, lookFor10, lookFor11, lookFor12 As Range
Dim rng As Range
Dim col As Integer
Dim found1, found2, found3, found4, found5, found6 As Variant
Dim found7, found8, found9, found10, found11, found12 As Variant

Set lookFor1 = Sheets("Sheet1").Range("J23")
Set lookFor2 = Sheets("Sheet1").Range("J24")
Set lookFor3 = Sheets("Sheet1").Range("J25")
Set lookFor4 = Sheets("Sheet1").Range("J26")
Set lookFor5 = Sheets("Sheet1").Range("J27")
Set lookFor6 = Sheets("Sheet1").Range("J28")
Set lookFor7 = Sheets("Sheet1").Range("J29")
Set lookFor8 = Sheets("Sheet1").Range("J30")
Set lookFor9 = Sheets("Sheet1").Range("J31")
Set lookFor10 = Sheets("Sheet1").Range("J32")
Set lookFor11 = Sheets("Sheet1").Range("J33")
Set lookFor12 = Sheets("Sheet1").Range("J34")
Set rng = Sheets("Sheet5").Columns("A")
col = 1

On Error Resume Next
found1 = Application.VLookup(lookFor1.Value, rng, col, 0)
found2 = Application.VLookup(lookFor2.Value, rng, col, 0)
found3 = Application.VLookup(lookFor3.Value, rng, col, 0)
found4 = Application.VLookup(lookFor4.Value, rng, col, 0)
found5 = Application.VLookup(lookFor5.Value, rng, col, 0)
found6 = Application.VLookup(lookFor6.Value, rng, col, 0)
found7 = Application.VLookup(lookFor7.Value, rng, col, 0)
found8 = Application.VLookup(lookFor8.Value, rng, col, 0)
found9 = Application.VLookup(lookFor9.Value, rng, col, 0)
found10 = Application.VLookup(lookFor10.Value, rng, col, 0)
found11 = Application.VLookup(lookFor11.Value, rng, col, 0)
found12 = Application.VLookup(lookFor12.Value, rng, col, 0)
If IsError(found1) Then
Else: lookFor1.Interior.ColorIndex = 44
End If
If IsError(found2) Then
Else: lookFor2.Interior.ColorIndex = 44
End If

 

[Weaver]

Can I suggest you take a look at array variables

For instance, lookfor1 could be replaced by lookfor(1), which you can define in 1 go

dim lookfor(12) as variant

Then you can use for...next loops to initialise your variables.

Having said that, it might be better to describe in words what it is you're actually trying to achieve, with maybe a snapshot of your data. It looks to me like you could encapsulate the whole thing in a for next loop without having to separately store the result of each individual lookup.

HTH

 

[Norie]

What is the code actually meant to do?

And why haven't you posted it all?

Perhaps something like this.

Sub test()
Dim lookForRng As Range
Dim lookFor As Range
Dim rng As Range
Dim found
    Set lookForRng = Sheets("Sheet1").Range("J23:J34")
    Set rng = Sheets("Sheet5").Columns("A")

    For Each lookFor In lookForRng
        found = Application.VLookup(lookFor.Value, rng, 1, 0)
        If Not IsError(found) Then
            lookFor.Interior.ColorIndex = 44
        End If
    Next lookFor

End Sub

 

[Weaver]

This might work:

Sub test()
    For i = 1 To 12
        Set r = Sheets("sheet1").Range("J" & i + 22)
        l = r.Value
        If Application.CountIf(Sheets("sheet5").Columns(1), 1) = 0 Then
            r.Interior.ColorIndex = 44
        End If
    Next i
End Sub

 

[Sandeep Warrier]

A bit late... but my contribution..

Sub ShortenCode()
    Dim LookFor(1 To 12) As Variant, Found(1 To 12) As Variant, Rng As Range, i As Long
    Set Rng = Sheets("Sheet5").Range("A:A")
    For i = 1 To 12
        Set LookFor(i) = Sheets("Sheet1").Range("J" & (i + 22))
        Found(i) = Application.WorksheetFunction.CountIf(Rng, LookFor(i))
        If Found(i) > 0 Then LookFor(1).Interior.ColorIndex = 44
    Next i
End Sub

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Weaver]

I'm not 100%, but I think this might do the job also...

Sub Macro1()
    On Error Resume Next
    ActiveWorkbook.Names.Add Name:="rng", RefersToR1C1:="=Sheet5!C1"
    With Sheet1.Range("J22:J34")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(rng,J22)=0"
        .FormatConditions(1).Interior.ColorIndex = 44
    End With
End Sub

 

[xxentric]

wow thanks lots of responses ill try these... the reason i havent posted all of the code is that there is just so much other code that has nothing to do with this piece

the code is meant to see if what is in sheet1 cell J23 is also on sheet5 somewhere in column A, and if it is, then make cell J23 yellow or red or whatever color i make it

and then do the same for J24 and J25 and so on untill J34

 

[xxentric]

hmm... anyone know a reason i could be getting this error i dont see anythring wrong and i commented out all the other code so that this is the only thing running in the macro

Unable to set the ColorIndex property of the Interior class

Dim lookForRng As Range
Dim lookFor As Range
Dim rng As Range
Dim found
    Set lookForRng = Sheets("Sheet1").Range("J23:J34")
    Set rng = Sheets("Sheet5").Columns("A")
    For Each lookFor In lookForRng
        found = Application.VLookup(lookFor.Value, rng, 1, 0)
        If Not IsError(found) Then
            lookFor.Interior.ColorIndex = 44
        End If
    Next lookFor

 

[xxentric]

woops nevermind i got it... couldnt do it while sheet was protected even though cells were not... working great. thanks!