Search Range of Cells, Copy Cell Value in adjacent cells if criteria is me

[SensualCarrots]

I've been searching for this for a while. I've only found hybrids of what I'm trying to do. It seems simple enough, but I just can't get it to work.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]A.1[/TD]
[/TR]
[TR]
[TD]A.1[/TD]
[TD]B.1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B.2[/TD]
[/TR]
[TR]
[TD]B.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically, a macro that will search a range of cells for a particular character or string of characters, and copy the entire cell value if it finds it. Any thoughs?

 

[SensualCarrots]

I may not have made my original question clear. Basically, the first column is the given data. I want something that will search for a string (simply a period in the example above) and copy the cell contents into another column (which will be located on a different sheet) without blank rows. What I'm doing now is:

=IF(ISNUMBER(SEARCH(".",A1)),A1,"")

I then copy that into each possible destination cell. Then I go through and delete the rows that are blank. I'd like something to do that for me without having to go through and delete everything. I don't necessarily need the destination cell to be a reference to the target cell (i.e. 'Sheet 1'!A1), just the data within the target cell will be sufficient. (Although knowing how to do both would be nice.) I was trying to use the If Instr command, but haven't gotten anywhere. Thanks in advance for any advice!

 

[Fluff]

How about

Sub GetValus()
   Dim Ary As Variant
   Dim Nary() As Variant
   Dim i As Long, j As Long
   Ary = Application.Transpose(Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)))
   For i = 1 To UBound(Ary)
      If InStr(1, Ary(i), ".") > 0 Then
         ReDim Preserve Nary(0 To j)
         Nary(j) = Ary(i)
         j = j + 1
      End If
   Next i
   Sheets("Sheet2").Range("A1").Resize(j + 1).Value = Nary
End Sub

 

[SensualCarrots]

That didn't do what I need. It found the first "." in the column, and copied it for as many times as it found "." in the column. It isn't moving on to the next target.

 

[Fluff]

Sorry, the last line should be

   Sheets("Sheet2").Range("A1").Resize(j + 1).Value = Application.Transpose(Nary)

 

[SensualCarrots]

Thank you! The only issue is that after it's done, it puts "#N/A" after the last cell on Sheet 2. Not the end of the world, but any ideas?

 

[Fluff]

Use

.Resize(j).Value

 

[SensualCarrots]

Thank you so much! I really want to start learning this stuff. Do you have anywhere that you would recommend as far as online schools? I'm pretty good at excel with formulas, but when it comes to VBA, i hardly remember anything from the 7th grade lol. Thanks again for your help. Life Saver!

 

[Fluff]

Glad to help & thanks for the feedback.

I don't know anything about on-line schools, but there are plenty of excellent sites. such as
http://www.cpearson.com/Excel/MainPage.aspx
http://www.contextures.com/
https://excelmacromastery.com/vba-articles/

 

[SensualCarrots]

Thank you. Could I make another request? This time, instead of just copying the contents of the cell, it'd like to select a specific range of cells in the row which the data is found. Example: If the period is found in A5, I'd like to copy not only A5, but also C5, E5, L5, M5, & N5. If that is too much work, could I do it so I just grab the entire row for which the data is found? Then I can parse out what I need on a different sheet. Thanks again!