VBA code to lookup a value

[spearhead]

Guys,

Need help on the following for creating a VBA code,

Need to lookup a value from "Sheet B" (column "B") and retrieve the value in "Column C". The lookup value should match the first three digits of a sixteen digit long number (every number has either "0" or "00" so the code is with the zero as well) in "Sheet A" (Column "I") and update the value in the "Sheet A" (column "D"). The matching should be done in row wise in Sheet A

"Sheet A"

Column I - 0440201000018489 (match the first three digits in this no. : "044")
Column D - Update the value in this column (row wise) if above matches the value in "Sheet B" (column "B")

"Sheet B"

Column B - Should lookup the value "044" in this column and update the value in "Column C" in to the "Column D" in Sheet A, if matches

 

[spearhead]

If anyone wants the sample file, here it is.

https://www.dropbox.com/s/dpn9kjjpa154x8l/Sample 1.xls?dl=0

 

[spearhead]

anyone....

 

[Joe4]

Many of us cannot download your file (Corporate Policy on Security).
However, based on your description, do you really need VBA? Why not just use VLOOKUP, i.e.

=VLOOKUP(LEFT(I2,3),Lookup_Range,2,0)

where Lookup_Range is a named range on Sheet B of columns B and C.

 

[spearhead]

Many of us cannot download your file (Corporate Policy on Security).
However, based on your description, do you really need VBA? Why not just use VLOOKUP, i.e.

=VLOOKUP(LEFT(I2,3),Lookup_Range,2,0)

where Lookup_Range is a named range on Sheet B of columns B and C.

I already have a formula. I just want to have a VBA code so that the person using the file does not require to copy paste the formula to each cell or accidental deletion.

 

[Joe4]

So, are you just looking at VBA code to copy that formula down the column?
I am guessing that it should go down as far as the last entry in column I. Is that right?
Can you provide the exact formula you have in the first cell in column D?

 

[spearhead]

So, are you just looking at VBA code to copy that formula down the column?
I am guessing that it should go down as far as the last entry in column I. Is that right?
Can you provide the exact formula you have in the first cell in column D?

I am not just looking to copy down the formula. If this can be done without the formula, that would be great. Sorry im totally new to VBA.

Yes. It should continue to go down as far as column "I" has a value.

Formula : =IFERROR(VLOOKUP(LEFT(TEXT(I28996,"0000000000000000"),3),'Branch List'!B:C,2,FALSE),"")


Refer the following URL for the screenprint of the excel file.

Imgur: The most awesome images on the Internet

 

[spearhead]

I am not just looking to copy down the formula. If this can be done without the formula, that would be great. Sorry im totally new to VBA.

Yes. It should continue to go down as far as column "I" has a value.

Formula : =IFERROR(VLOOKUP(LEFT(TEXT(I28996,"0000000000000000"),3),'Branch List'!B:C,2,FALSE),"")


Refer the following URL for the screenprint of the excel file.

Imgur: The most awesome images on the Internet

I already have this VBA codes in my file. So can you please tell me how to add another code to this, if you can make one. Thanks.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call Script1(Target)
Call Script2(Target)
Call Script3(Target)
Call Script4(Target)
End Sub

Private Sub Script1(ByVal Target As Range)
On Error GoTo ErrHnd:
If Target.Column = 9 Then

'disable events to stop changes made by this macro re-trigering it
Application.EnableEvents = False
If Not IsDate(Range("A" & Target.Row).Value) Then
Range("A" & Target.Row).Value = Format(Date, "dd/mmm/yy")
End If
're-enable events
Application.EnableEvents = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Private Sub Script2(ByVal Target As Range)
Dim c As Range
If Intersect(Range("I:L"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("I:L"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "O").Value = Environ("username")
Application.EnableEvents = True
End If
Next c
End Sub

Private Sub Script3(ByVal Target As Range)
Dim c As Range
If Intersect(Range("I:L"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("I:L"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "P").Value = Format(Now, "DD-MMM-YY HH:MM:SS")
Application.EnableEvents = True
End If
Next c
End Sub
Private Sub Script4(ByVal Target As Range)
Dim c As Range
If Intersect(Range("I:L"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("I:L"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "B").Value = "N"
Application.EnableEvents = True
End If
Next c
End Sub

 

[Joe4]

Are you looking to put this new code in one of the existing scripts, or in a new script?
What cell will the first row that this formula will be put in?
Will this just be filling on one row at a time (as a new value is added), or will you be doing a fill-down where multiple cells need to be updated simultaneously?
What column being update should trigger this to run?

 

[spearhead]

Are you looking to put this new code in one of the existing scripts, or in a new script?
What cell will the first row that this formula will be put in?
Will this just be filling on one row at a time (as a new value is added), or will you be doing a fill-down where multiple cells need to be updated simultaneously?
What column being update should trigger this to run?

The new script should run along with the existing ones

this formula will be in every cell in column "D" (sheet 1)

This formula should be triggered in the corresponding cell of Column "D" if a value is entered in column "I" in the same row.

Another thing, how can all the scripts be set to remove the automated value if the value entered in column "I" is deleted. Coz all other automated fields are linked to "Column I"