PDA

View Full Version : [SOLVED] Searching and copying values within a sheet (2 different operations)



Viktor
11-08-2013, 04:08 AM
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.

10797

Thank you.

sts023
11-08-2013, 04:47 AM
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
11-08-2013, 05:01 AM
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
11-08-2013, 05:40 AM
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
11-08-2013, 05:46 AM
Also, to execute the Ctrl+Right from a selected cell, use

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

mancubus
11-10-2013, 03:24 PM
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
11-10-2013, 03:29 PM
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
11-11-2013, 03:32 AM
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
11-11-2013, 05:14 AM
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.