PDA

View Full Version : [SOLVED] Automatically enter the value of a formula in adjacent cell



Grade4.2
08-01-2018, 04:02 AM
Good morning/afternoon/night.

1. I enter 4 numbers into cells located in column A
2. I have a formula in column B that searches for those 4 numbers in a list of data located on sheet 2 to return a description on sheet 2

Can I please have a code that will monitor when I input 4 digits into column "A", search for the description found in the list on sheet 2, and return the description into the adjacent cell in column "B" after I press enter? and if it finds an error because the numbers I type can't be found, can I have that error return a customized text instead of a description?

Thank you for your help. I love this forum.

mancubus
08-01-2018, 04:20 AM
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Column <> 1 Then Exit Sub
If .Row < 8 Then Exit Sub
.Offset(, 1).Value = Application.VLookup(.Value, Range("$A$1:$B$7"), 2, False)
End With

End Sub


copy to Sheet1's code module (right click on tab name, click "View Code")

Grade4.2
08-01-2018, 05:37 AM
Hello there and thank you for your code. I've tested it and found that it doesn't refer to the information on sheet 2 but instead looks at the values on the same sheet that have been entered into sheet 1. I've added some text to another attachment if that helps with what's needed. I do apoligise for any confusion caused so I'll try to explain this again.

1. when I enter 4 digits into sheet1 Column "A"
2. I need a vba code to look up these 4 digits on sheet2 column "B"
3. Then return the Description from sheet2 column "A"
4. To the adjacent cell the digits were entered, on Sheet1 column "B"
5. If there are any errors, can I please have customised text instead of an error code?

mancubus
08-02-2018, 06:31 AM
1. when I enter 4 digits into sheet1 Column "A"


what if you enter less than 4 digits number such as 456, 66, 0?

below code will search for Sheet1 ColA number (regardless of its digits) in Sheet2 ColB and;
1) if a match is found, it will return the adjacent Sheet2 ColA value to Sheet1 ColB,
2) if a match is not found, it will return the string "Value not found in Sheet2, Column B" to Sheet1 ColB,
3) if a previously entered Sheet1 ColA value is cleared, it will also clear the Sheet1 ColB value.



Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Column <> 1 Then Exit Sub
If .Row < 8 Then Exit Sub

On Error Resume Next
.Offset(, 1).Value = Worksheets("Sheet2").Range("A" & Application.Match("*" & .Value & "*", Worksheets("Sheet2").Range("$B$1:$B$50000"), 0))
If Err Then
Err.Clear
.Offset(, 1).Value = "Value not found in Sheet2, Column B"
End If

If .Value = "" Then .Offset(, 1).Value = ""
End With

End Sub

Grade4.2
08-03-2018, 04:52 AM
You are an absolute wizard. Thank you so very much. That is perfect!:clap::clap::thumb:eek:

mancubus
08-03-2018, 06:01 AM
you are welcome.
glad it helped.