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

 

[Joe4]

OK, here is a Script5 that I think will do what you want:

Private Sub Script5(ByVal Target As Range)

'   Check to see if column I has been updated
    If Target.Column = 9 Then
        Application.EnableEvents = False
'       If column I has a value, then enter the formula in column D
        If Target <> "" Then
            Target.Offset(0, -5).FormulaR1C1 = _
                "=IFERROR(VLOOKUP(LEFT(TEXT(RC[5],""0000000000000000""),3),'Branch List'!C[-2]:C[-1],2,FALSE),"""")"
'       If column I is blank, remove everything from column D
        Else
            Target.Offset(0, -5).ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub

So, you would just add this and a call to Script5 from your Worksheet_Change event procedure at the top.

 

[spearhead]

OK, here is a Script5 that I think will do what you want:

Private Sub Script5(ByVal Target As Range)

'   Check to see if column I has been updated
    If Target.Column = 9 Then
        Application.EnableEvents = False
'       If column I has a value, then enter the formula in column D
        If Target <> "" Then
            Target.Offset(0, -5).FormulaR1C1 = _
                "=IFERROR(VLOOKUP(LEFT(TEXT(RC[5],""0000000000000000""),3),'Branch List'!C[-2]:C[-1],2,FALSE),"""")"
'       If column I is blank, remove everything from column D
        Else
            Target.Offset(0, -5).ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub

So, you would just add this and a call to Script5 from your Worksheet_Change event procedure at the top.

Thank you so much friend. It worked.

Another small thing, what is the script lines I need to add to all other 4 scripts to have the clear contents if the value entered in column I is deleted.

And just want to know how to protect the sheets with a password and allow the scripts to work. I just only want to allow certain columns to be updated manually for the user, but others to be frozen. The frozen columns will have scripts running on them. How do I allow them to run with the protection on them?

I have more to ask, can I ask in this thread or do I need to raise another one. Sorry for all the trouble.

 

[spearhead]

How can I add this formula "=IF(A28996,TEXT(A28996,"mmm"),"")" to column Q and this formula "=IF(A28996,TEXT(A28996,"yyyy"),"")" once the date is picked from the script to column A. What is the VBA script to add these two formulas?

Can you tell me how to do it so I can do it next time.

 

[Joe4]

You should be able to apply the exact same logic in the section of code I just gave you. Basically, it is the same thing, just with different columns/formulas. Give it a try.

Here is a little trick for converting your Excel formula to the VBA Relative Reference (RC) notation. Turn on your Macro Recorder, and then enter the formula in a cell on your spreadsheet, and then stop the recorder. Then take a look at the VBA code you just created.

To understand how the RC notation works, "R" stands for "Row" and "C" stands for "Column".
So RC[5] means stay in the same row, but move 5 columns to the right.
Similarly, C[-2] means move two columns to the left.

The other useful piece of knowledge in using the code I created is the OFFSET function. The format is: OFFSET(row,column)
So Target.Offset(0, -5).ClearContents means starting in the cell that triggered the macro (the cell we just changed the value of), move 5 columns to the left and clear the contents of that cell.

 

[spearhead]

You should be able to apply the exact same logic in the section of code I just gave you. Basically, it is the same thing, just with different columns/formulas. Give it a try.

Here is a little trick for converting your Excel formula to the VBA Relative Reference (RC) notation. Turn on your Macro Recorder, and then enter the formula in a cell on your spreadsheet, and then stop the recorder. Then take a look at the VBA code you just created.

To understand how the RC notation works, "R" stands for "Row" and "C" stands for "Column".
So RC[5] means stay in the same row, but move 5 columns to the right.
Similarly, C[-2] means move two columns to the left.

The other useful piece of knowledge in using the code I created is the OFFSET function. The format is: OFFSET(row,column)
So Target.Offset(0, -5).ClearContents means starting in the cell that triggered the macro (the cell we just changed the value of), move 5 columns to the left and clear the contents of that cell.

Thank you so much for the explanation. But I get a runtime error 13 type mismatch on the following. It points to If Target <> "" Then

Private Sub Script6(ByVal Target As Range)

' Check to see if column A has been updated
If Target.Column = 1 Then
Application.EnableEvents = False
' If column A has a value, then enter the formula in column Q
If Target <> "" Then
Target.Offset(0, 16).FormulaR1C1 = _
"=IF(RC[-16],TEXT(RC[-16],""mmm""),"""")"
' If column A is blank, remove everything from column Q
Else
Target.Offset(0, 16).ClearContents
End If
Application.EnableEvents = True
End If



and how do i have the clear contents for the following type of codes. since i tried the things u said, but didnt work. i need to enter a specific value.

Private Sub Script4(ByVal Target As Range)
Dim c As Range
If Intersect(Range("E:H"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("E:H"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "B").Value = "N"
Application.EnableEvents = True
End If
Next c
End Sub

 

[spearhead]

and how do i have the clear contents for the following type of codes. since i tried the things u said, but didnt work. i need to enter a specific value.

Private Sub Script4(ByVal Target As Range)
Dim c As Range
If Intersect(Range("E:H"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("E:H"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "B").Value = "N"
Application.EnableEvents = True
End If
Next c
End Sub

this i changed. and now its working as i want. can u tell me regarding the other one.

 

[Joe4]

this i changed. and now its working as i want. can u tell me regarding the other one.

I do not see anything inherently wrong with that.
What cell is being updated when that is row (somewhere in column A?), and what is the value in the cell being updated?
Are you updating more than one cell at a time when it is being called (maybe doing a copy and paste into multiple cells at the same time)?

 

[spearhead]

I do not see anything inherently wrong with that.
What cell is being updated when that is row (somewhere in column A?), and what is the value in the cell being updated?
Are you updating more than one cell at a time when it is being called (maybe doing a copy and paste into multiple cells at the same time)?

Cell updated is "A"

following is the full code list I use for this excel.

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

Private Sub Script1(ByVal Target As Range)

' Check to see if column E has been updated
If Target.Column = 5 Then
Application.EnableEvents = False
' If column E has a value, then enter the value in column A
If Target <> "" Then
Target.Offset(0, -4).Value = Format(Date, "dd/mmm/yy")
' If column E is blank, remove everything from column O
Else
Target.Offset(0, -4).ClearContents
End If
Application.EnableEvents = True
End If

End Sub


Private Sub Script2(ByVal Target As Range)

' Check to see if column E has been updated
If Target.Column = 5 Then
Application.EnableEvents = False
' If column E has a value, then enter the value in column O
If Target <> "" Then
Target.Offset(0, 10).Value = Environ("username")
' If column E is blank, remove everything from column O
Else
Target.Offset(0, 10).ClearContents
End If
Application.EnableEvents = True
End If

End Sub

Private Sub Script3(ByVal Target As Range)

' Check to see if column E has been updated
If Target.Column = 5 Then
Application.EnableEvents = False
' If column E has a value, then enter the value in column P
If Target <> "" Then
Target.Offset(0, 11).Value = Format(Now, "DD-MMM-YY HH:MM:SS")
' If column E is blank, remove everything from column P
Else
Target.Offset(0, 11).ClearContents
End If
Application.EnableEvents = True
End If

End Sub

Private Sub Script4(ByVal Target As Range)

' Check to see if column E has been updated
If Target.Column = 5 Then
Application.EnableEvents = False
' If column E has a value, then enter the value in column B
If Target <> "" Then
Target.Offset(0, -3).Value = "N"
' If column E is blank, remove everything from column B
Else
Target.Offset(0, -3).ClearContents
End If
Application.EnableEvents = True
End If

End Sub
Private Sub Script5(ByVal Target As Range)

' Check to see if column I has been updated
If Target.Column = 9 Then
Application.EnableEvents = False
' If column I has a value, then enter the formula in column D
If Target <> "" Then
Target.Offset(0, -5).FormulaR1C1 = _
"=IFERROR(VLOOKUP(LEFT(TEXT(RC[5],""0000000000000000""),3),'Branch List'!C[-2]:C[-1],2,FALSE),"""")"
' If column I is blank, remove everything from column D
Else
Target.Offset(0, -5).ClearContents
End If
Application.EnableEvents = True
End If

End Sub
Private Sub Script6(ByVal Target As Range)

' Check to see if column A has been updated
If Target.Column = 1 Then
Application.EnableEvents = False
' If column A has a value, then enter the formula in column Q
If Target <> "" Then
Target.Offset(0, 16).FormulaR1C1 = _
"=IF(RC[-16],TEXT(RC[-16],""mmm""),"""")"
' If column A is blank, remove everything from column Q
Else
Target.Offset(0, 16).ClearContents
End If
Application.EnableEvents = True
End If

End Sub
Private Sub Script7(ByVal Target As Range)

' Check to see if column A has been updated
If Target.Column = 1 Then
Application.EnableEvents = False
' If column A has a value, then enter the formula in column R
If Target <> "" Then
Target.Offset(0, 17).FormulaR1C1 = _
"=IF(RC[-17],TEXT(RC[-17],""yyyy""),"""")"
' If column A is blank, remove everything from column R
Else
Target.Offset(0, 17).ClearContents
End If
Application.EnableEvents = True
End If

End Sub

 

[Joe4]

Since I am unable replicate that behavior, I am afraid there is not much else I can suggest.
The only thing I can think of would be if you were updating multiple values in column A simultaneously, like with a Copy/Paste. That could be problematic.