Vlookup in VBA 1

[yimitz]

I want to look up one value in sheet1 that will have a matching value in sheet2, I have to walk through each cell value and highlight matches.

Here's my code:

Application.VLookup(Range(myRcdA).Value, Range("A3535"), 3, False)

Range(myRcdA) will have data in sheet1, I need to match that to data in sheet2. The issue I think I'm having is setting the Range("A3535") to sheet2 somehow. I've tried Range.Sheets, Sheets("sheet2").Range("A3535") but no luck.

This is probably a simple fix but I'm stumped. Anyone have any ideas?

 

[SkipVought]

Hi,

Is there a reason that you are doing this in VBA and not on the worksheet?

If the range is on sheet2 and your lookup value is on sheet1...

Application.VLookup(Sheets("Sheet1").Range(myRcdA).Value, Sheets("Sheet2").Range("A3:D535"), 3, False)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

I don't want to add to the worksheet, just change the highlighting of the cells based on walking through the cells with values and doiing the comparison.

I was thinking of calling the procedure in an on-open routine(?) make the changes and save it, I haven't really thought that far ahead. Don't make any comments on that ( LOL)

I get a runtime error 13 Type mismatch. What would cause that do you think?

 

[SkipVought]

What is the value in myRcdA?

...just change the highlighting of the cells ...

You could use Conditional Formatting.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

A4 is the exact value. It would be referencing Sheet1!A4 value. my first line of code is setting focus to sheet1 and cell A4.

 

[SkipVought]

do you have both numbers and text in the same column in the table?

Specifically what value is in A4?

Here's how you should be using this code...

Dim p1 as variant

p1 = Application.VLookup(Sheets("Sheet1").Range(myRcdA).Value, Sheets("Sheet2").Range("A3:D535"), 3, False)

if not iserror(p1) then
'do this when no error
else
'do this when error
end if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

That's the ticket! I was using Set P1 = (VBCode for lookup)

No more errors. Now I just have to set the background color of the cell based on the value the lookup returned.

Thanks for the assistance!!

 

[SkipVought]

How many different values?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

4 different values so that's fairly easy. The tough part now is walking two columns over and highlighting the values in this column, etc, etc. and then also doing this same walk through for 10 worksheets. Is there a "for each worksheet" routine that would simplify this?

 

[SkipVought]

dim ws as worksheet, p1 as variant, r as range

for each ws in thisworkbook.worksheets
  if ws.name <> "Sheet2" then
    for each r in ws.[b]You have not define what this range will be PLEASE DO[/b]
      p1 = Application.VLookup(r.Value, Sheets("Sheet2").Range("A3:D535"), 3, False)

      if not iserror(p1) then
        select case p1
           case "[b]Your First value in four[/b]"
              r.interior.colorindex = [b]YourFirstBackground Color Index[/b]
' and so on for the other three cases
        end select
      end if
    next
  end if  
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

That's the thing, the range will be variable I'm afraid. I think to start I would say it would be no bigger than 13 columns and 500 rows.

 

[SkipVought]

If the range is variable, then use the range (CurrentRegion) to determine the number of rows and columns. Don't assume some other numbers.

With SomeCellRange.CurrentRegion
  StartRow = .row
  StartCol = .column
  NbrRows = .rows.count
  NbrCols = .columns.count
  EndRow = StartRow + NbrRows - 1
  EndCol = StartCol + NbrCols - 1
End With

It ALL there!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

Just wanted to give you my end result code that works. It's not perfect for every possible situation but it does what needed in a heartbeat. Thanks to Skip for all the assistance!

Sub MColorMe()

Dim myMatchVariable As Variant

'********************************************************************
Dim ws As Worksheet, p1 As Variant, r As Range

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Master" Then ' Master is the sheet I am comparing could omit if you wanted.

ws.Select

For Each r In Range("C4:Z500") 'This range is variable and would need manual adjustment if it grew, a routine could be written to find all applicable ranges needed I did this to suit my immediate need


myMatchVariable = Application.VLookup(r.Value, Sheets("Master").Range("A4:C535"), 3, False)

If Not IsError(myMatchVariable) Then

Select Case myMatchVariable

Case "Variable1toMatch"
r.Interior.ColorIndex = 3 'Red

Case "Variable2toMatch"

r.Interior.ColorIndex = 4 'Green

Case "Variable3toMatch"

r.Interior.ColorIndex = 5 'Blue

Case "Variable4toMatch"

r.Interior.ColorIndex = 6 'Yellow

End Select

End If

Next

End If

Next

'**********************************************************************

End Sub

 

[SkipVought]

For Each r In Range("C4:Z500") 'This range is variable and would need manual adjustment if it grew, a routine could be written to find all applicable ranges needed I did this to suit my immediate need

Not a good approch. You should determina a way to figure out the correct range using the objects on the sheet.

I gave you a tip on 22 Apr 10 9:27.

If your sheet is well designed, then your data area can be defined...

dim rng as range

set rng = [C4]
With rng.CurrentRegion
  StartRow = .row
  StartCol = .column
  NbrRows = .rows.count
  NbrCols = .columns.count
  EndRow = StartRow + NbrRows - 1
  EndCol = StartCol + NbrCols - 1
End With

set rng = range(rng, cells(EndRow, EndCol))
For Each r In rng
'.......

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

Thanks,

I didn't have time to work it through, it was a immediate need sitation. Now that I solved the immediate need I will work on updating the routine to clarify the required range and clean up the approach.

Thanks again!! Once I have it completed I'll let you know.