Searching and copying values within a sheet (2 different operations)

[Viktor]

Hi,

I need some help with a VBA code that does these two operations:
1. Searches for "GB"; copies content of the first hit into cell B2. If there is no cell containing "GB", nothing should happen.
2. Searches for "vertical-horizontal"; ctrl + right arrow; copies first hit together with the 4 cells underneath into cells E2:I2 (so it should also transpose the cells when pasting).

I'm not sure how clear this is, so I am attaching an xls as an example.

VBAExpress.xlsx

Thank you.

[sts023]

Finding a value in a column can be used something like:-

Columns("A:A").Select
    Selection.Find(What:="What to look for", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

The Copy & Transpose can be obtained from recording a macro :-

    Range("C8:C12").Select
    Selection.Copy
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, _
                                     Operation:=xlNone, _
                                     SkipBlanks:= False, _
                                     Transpose:=True
    Range("G16").Select
    Application.CutCopyMode = False

[Viktor]

Thanks Steve, but that is not what I needed.

The first answer does not copy the value of the cell that was found and then paste it into cell B2.
The second one won't work if the values I need copied change location (i.e. not any longer C8:C12). This is why it is important to use the find function and then some code for the Ctrl+right.

[sts023]

To look in any cell, try

Sheet1.Select
Cells.Find(What:="What to look for", After:=ActiveCell, LookIn:=xlValues, _ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _     MatchCase:=False).Select

and sorry, the first offering should have had

LookIn:=xlValues

not

Lookin:=xlFormulas

(Blame old age and speed typing!)

[sts023]

Also, to execute the Ctrl+Right from a selected cell, use

    Range(Some Cell address, e.g. "A1").Select
    Selection.End(xlToRight).Select

[mancubus]

try this.


1)

Sub find_copy_1()
    On Error Resume Next
    Range("B2").Value = Columns(1).Find(What:="GB", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Value
    On Error GoTo 0
End Sub

2)

Sub find_offset_copy()
    On Error Resume Next
    Range("E2:I2").Value = Application.Transpose(Columns(1).Find(What:="vertical-horizontal", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).End(xlToRight).Resize(5).Value)
    On Error GoTo 0
End Sub

[mancubus]

or if you know the column number of range to copy...
(for ex, it's columnn 3 or Column C)

2)

Sub find_offset_copy()
    On Error Resume Next
    Range("E2:I2").Value = Application.Transpose(Columns(1).Find(What:="vertical-horizontal", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(,2).Resize(5).Value)
    On Error GoTo 0
End Sub

[Viktor]

mancubus,

EDIT: BOTH WORK! THANKS A LOT!

the first one works like a charm, thanks!

The second one doesn't though. I suspect it might not capture the CTRL+ right properly.
The cells I need to copy can be 2,3 or more columns to the right of the search text.

Any ideas how to integrate the ctrl+right in your code?

Thanks again,
Viktor

[mancubus]

you are welcome. i tested the code with your attachment before copying here. i'm glad it helped. we can say End(xlToRight) is VBA equivalent of Ctrl + Right Arrow.