Hi,
I'm using Excel 2003 and am working on the table below.
My VBA code runs through column H and if it finds a matching number in D1:E4 it returns the value in column C for that row.
So for example '005' in H6 is found in D2, therfore copy C2 ('NMM') into I6
This works pretty well, however for some reason '008' in H7 does not return GGM. Instead it returns MWA.
I thought it always returns the first hit but that doesn't seem to be the case.
How can I exclude rows which have 'Office' in column B from my search range.
Can I somehow define exceptions in 'Find'?
This is my whole code:
I'm using Excel 2003 and am working on the table below.
My VBA code runs through column H and if it finds a matching number in D1:E4 it returns the value in column C for that row.
So for example '005' in H6 is found in D2, therfore copy C2 ('NMM') into I6
This works pretty well, however for some reason '008' in H7 does not return GGM. Instead it returns MWA.
I thought it always returns the first hit but that doesn't seem to be the case.
How can I exclude rows which have 'Office' in column B from my search range.
Can I somehow define exceptions in 'Find'?
Code:
Set found = rngSites.Find(cell.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
Code:
Sub Site_Lookup()
Dim rngSites As Range, rngLookup As Range, cell As Range, found As Range
Dim LR As Long, i As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Const FR As Long = 6 '<-- First Row of actual data
Set rngSites = Range("D" & 1 & ":E" & 4)
Set rngLookup = Range("H" & FR & ":H" & LR)
For Each cell In rngLookup
If cell.Value <> "" Then
If IsNumeric(cell) Then
Set found = rngSites.Find(cell.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
If Not found Is Nothing Then
' Site found
cell.Offset(, 1).Value = Range("C" & found.Row).Value
Else
' No Match
cell.Offset(, 1).Value = "N\A"
End If
Else
' Text
cell.Offset(, 1).Value = cell.Value
End If
End If
Next cell
End Sub| Excel Workbook | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | January | Site | GGM | 008 | 024 | ||||||
| 2 | January | Site | NMM | 005 | 011 | ||||||
| 3 | January | Office | MWA | 008 | 024 | ||||||
| 4 | January | Office | KNC | 008 | 024 | ||||||
| 5 | RigSite | RigSite | |||||||||
| 6 | 005 | NMM | |||||||||
| 7 | 008 | MWA | |||||||||
| 8 | 011 | NMM | |||||||||
| 9 | 021 | N\A | |||||||||
| 10 | 024 | GGM | |||||||||
Sheet1 | |||||||||||
ffice
></o
