VBA: If Defined String EXISTS in Row, Return Value

SteveOranjin

Board Regular
Joined
Dec 18, 2017
Messages
170
Hello,

I am working on a macro that I was able to configure based off of a macro I found.

What I'd like to be able to do ideally, is to have a series of cells that I can enter values into. Say, 20. It would be a table of say, 2 columns and 20 rows on a different sheet, we'll call it sheet two.

This table will be used for me to enter a value into column 1. Say, "Dog". IN column 2 of row 1, let's say I enter "Fur"

I would then like to run a macro that will look in the column titled "Product Name" in "Sheet1" and if it spots, "Dog" it would return "Fur" in a column at the end of the data sheet in question in sheet1. So lets say there were five columns of data in sheet1. In Column 6 (it can be column 1, if that makes it easier because column 1 is always in the same spot) it would enter the value I had entered into row 1 column 2.

This is what I have done so far. I'm a shoot from the HIP VBA guy so it's the best I've done so far.

Anyone think this is very hard? If not, can I get a little help?

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub AddDashes()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("RANGE TO SEARCH")

For Each cel In SrchRng
If InStr(1, cel.Value, "TOTAL") > 0 Then
cel
.Offset(1, 0).Value = "-"
End If
Next cel

End Sub</code>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
isnt that VLOOKUP?

=Vlookup(A2,sheet2!A1:B30,2)

No. Not at all.

There is always, "IF(ISNUMBER(FIND("Dog",C2)),"Dog","0") but typing that in is laborious. I'd like to see if I can reduce the time myself and My team spend punching in formulas to create more information than what is there.

Steve
 
Upvote 0
How about
Code:
Sub AddInfo()

   Dim Rng As Range
   Set Rng = Sheets("[COLOR=#ff0000]Dup[/COLOR]").Range("[COLOR=#ff0000]SrchRng[/COLOR]")
   With Range("F2", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
      .Formula = "=if(iserror(vlookup(a2," & Rng.Parent.Name & "!" & Rng.Address & ",2,0)),"""",vlookup(a2," & Rng.Parent.Name & "!" & Rng.Address & ",2,0))"
      .Value = .Value
   End With
End Sub
Change the values in red to match the sheet name & range for the lookup table
 
Upvote 0
tls taking the value in col A Looking that up in SrchRng & returning the relevant value in col F
 
Upvote 0
tls taking the value in col A Looking that up in SrchRng & returning the relevant value in col F


This macro is not working for me. Can I persuade you to send me the sheet you are working with where it works? So I can see it working? I'm going to PM you my e-mail
 
Upvote 0

Forum statistics

Threads
1,223,711
Messages
6,174,020
Members
452,542
Latest member
Bricklin

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