Help in Excel VBA for simple task

[Jani]

I'm a VB programmer and now I was asked to do a simple task in Excel
using VBA. And I only have 2 days to do it. Can someone tell me how
to:

Search a given range in SheetA, look for ItemA (from column A) and
insert the data from SheetA's column B into SheetB's column B?
Additonal criteria would be, SheetA's column C should match the
filtering criteria obtain from SheetB's cell C1 and SheetA's ItemA
should match SheetB's column B, for example.

SheetA
ColA ColB ColC
A001 1000 2
A002 1200 2
A003 900 1
A004 1300 2

SheetB should shows (provided cell C1 = 2):
ColA ColB
A001 1000
A002 1200
A004 1300

I'm really in a rush for this task because this task is the 1st stage
in order to proceed to my later stages in my VB project development.

 

[Bob Phillips]

Look up Find in Excel VBA help, that shows how to find a value in the
column.

Then just offset the found cell by 1 column, cell.Offset(0,1).Value to get
B's value.

Don't understand the C bit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

wasn't sure exactly whatt you wanted from your description. this shoudl get
you started. Post response if you need more help

Sub MyLookup()

'search column a sheet a
Sheets("SheetA").Activate
'rows.count is a constant indicating last row in worksheet
'end(xlup) search from end of worksheet to 1st non-empty cell
ShALastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set ShAColARange = Sheets("SheetA").Range(Cells(1, 1), Cells(ShALastRow, 1))

Sheets("SheetB").Activate
'rows.count is a constant indicating last row in worksheet
'end(xlup) search from end of worksheet to 1st non-empty cell
ShBLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set ShBColARange = Sheets("SheetB").Range(Cells(1, 1), Cells(ShBLastRow, 1))

For Each MyCell In ShAColARange
If Not IsEmpty(MyCell) Then
Sheets("SheetB").Activate
Set FoundCell = _
ShBColARange.Find(MyCell, LookIn:=xlValues)

If Not FoundCell Is Nothing Then
'insert the data from SheetA's column B into SheetB's column B
FoundCell.Offset(rowoffset:=0, columnoffset:=1) = _
MyCell.Offset(rowoffset:=0, columnoffset:=1)

'SheetA 's column C should match the filtering criteria
'obtain from SheetB's cell C1
MyCell.Offset(rowoffset:=0, columnoffset:=2) = _
Sheets("SheetB").Range("C1")

End If
End If

Next MyCell

End Sub