Macro Required for Lookup Function

[Akash]

Hi,

I am facing some problem in regards to formula.

I have two sheets

1. Schedule
2. Input

In Shedule Sheet I have four columns:

Ref No Project Dwg. Unit Qty
01 KEVENTER KOLKATA PC-7001-3 6
02 CIPLA LTD, BADDI 7049-1A 1
03 CIPLA LTD, BADDI 7049-2A 1
04 CIPLA LTD, BADDI 7049-3A 1
05 CIPLA LTD, BADDI 7049-5A 1
06 CIPLA LTD, BADDI 7049-9A 1

the data of Schedule Work is entered in the this sheet.

Now In put sheet we have below mentioned columns:

1. Drg No
2. Project Name
3. FH
4. FL
5. RD
6. FPI
7. QTY
8. Ref No

Now I want a macro through which when ever i enter Ref No in the Input
sheet it should pick the value of Drg No/Project Name/Qty: from
Shedule sheet and paste it autometically in the Input sheet so that
the user had not to copy it again and again.

Right now i am using the belwo mentioned Formula...
=IF(TRIM(H5)="","",LOOKUP(H5,SCHEDULE!A3:A5000,SCHEDULE!C3:C5000))

I want a macro becoz the formula gets deleted by the users by mistake.
Then i had to sit to correct the Format everytime.

Awaiting for your help.

Thanks in Advance.

Regards

Akash

 

[merjet]

Put the following in the Input sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFound As Boolean
Dim iEnd As Integer
Dim c As Range
Dim rng As Range

If Target.Column = 8 Then
If Target = "" Then
Target.Offset(0, -7) = ""
Target.Offset(0, -6) = ""
Target.Offset(0, -1) = ""
Else
iEnd = Sheets("Schedule").Range("A2").End(xlDown).Row
Set rng = Sheets("Schedule").Range("A2:A" & iEnd)
For Each c In rng
If Target = c Then
Target.Offset(0, -7) = c.Offset(0, 2)
Target.Offset(0, -6) = c.Offset(0, 1)
Target.Offset(0, -1) = c.Offset(0, 3)
bFound = True
Exit For
End If
Next c
If bFound = False Then
MsgBox ("Ref No not found.")
Target = ""
End If
End If
End If
End Sub

Hth,
Merjet

 

[Dave Peterson]

Maybe you could apply Data|filter|autofilter to the first worksheet.

Then filter only the values/rows you want copied and then copy them to the
second sheet.

If you really need a macro, you could record one when you did it manually.

This works pretty well when both sheets are laid out the same. I'm not sure
that's what you have, though.

 

[Akash]

Hi,

I am facing some problem in regards to formula.

I have two sheets

1. Schedule
2. Input

In Shedule Sheet I have four columns:

Ref No Project Dwg. Unit Qty
01 KEVENTER KOLKATA PC-7001-3 6
02 CIPLA LTD, BADDI 7049-1A 1
03 CIPLA LTD, BADDI 7049-2A 1
04 CIPLA LTD, BADDI 7049-3A 1
05 CIPLA LTD, BADDI 7049-5A 1
06 CIPLA LTD, BADDI 7049-9A 1

the data of Schedule Work is entered in the this sheet.

Now In put sheet we have below mentioned columns:

1. Drg No
2. Project Name
3. FH
4. FL
5. RD
6. FPI
7. QTY
8. Ref No

Now I want a macro through which when ever i enter Ref No in the Input
sheet it should pick the value of Drg No/Project Name/Qty: from
Shedule sheet and paste it autometically in the Input sheet so that
the user had not to copy it again and again.

Right now i am using the belwo mentioned Formula...
=IF(TRIM(H5)="","",LOOKUP(H5,SCHEDULE!A3:A5000,SCHEDULE!C3:C5000))

I want a macro becoz the formula gets deleted by the users by mistake.
Then i had to sit to correct the Format everytime.

Awaiting for your help.

Thanks in Advance.

Regards

Akash

thanks a tonn..

it has been a very helpful tool which you had provided me...

Thanks

Akash