VBA code to lookup a value

spearhead

New Member
Joined
Nov 30, 2015
Messages
48
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
 
OK, here is a Script5 that I think will do what you want:
Code:
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.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
OK, here is a Script5 that I think will do what you want:
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,277
Messages
6,171,156
Members
452,385
Latest member
Dottj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top