VBA: If Defined String EXISTS in Row, Return Value

[SteveOranjin]

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>

 

[ranman256]

isnt that VLOOKUP?

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

 

[SteveOranjin]

Anyone?

 

[SteveOranjin]

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

 

[Fluff]

How about

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

 

[SteveOranjin]

Is that looking in row E of sheet 1 or is it looking in the column entitled, "product name"

Steve

 

[Fluff]

tls taking the value in col A Looking that up in SrchRng & returning the relevant value in col F

 

[SteveOranjin]

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

 

[Fluff]

File can be found here https://1drv.ms/x/s!AtYrlcV2NWPFgRJCfNg_bifel4pb

 

[SteveOranjin]

vgb