Copy, find & paste... oh my -- Please help!!!

[Father Guido]

How would I go about copy some contents, finding a cell based on another
cell, then offsetting from it and pasting the data?

Here's what I have.

A cell into which I will put a number (1-270), the adjacent two cells I
wish to copy then find the original cell in another range, offset from
it and paste the copied data.

Is there some kind of reverse lookup function I can use to goto a cell?

Example. I write the 25, 3, and 55 in three adjacent vertical cells.

25
3
55

I want to 'lookup/goto' the first number (25) the another range, then
paste the 3 and 55 into horizontally adjacent cells.

1
2
3
~
24
25 3 55
26

Prefer a macro solution to automate this procedure, as I have to do it
270 times. Thanks in advance!!!

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

 

[Bernie Deitrick]

Norm,

Try this macro, after selecting the cell with the 25 in it: this
assumes that the 'other range' is on the same sheet.

Sub CopyMatchTranspose()
Dim myCell As Range

Set myCell = ActiveCell
myCell(2).Resize(2, 1).Copy
'The next line(s) should all be on one line when copied into your
module
Range("A:A").Cells(Application.Match(myCell.Value, Range("A:A"),
False))(1, 2).PasteSpecial Transpose:=True

End Sub

HTH,
Bernie

 

[Father Guido]

Sub CopyMatchTranspose()
Dim myCell As Range

Set myCell = ActiveCell
myCell(2).Resize(2, 1).Copy
'The next line(s) should all be on one line when copied into your
module
Range("A:A").Cells(Application.Match(myCell.Value, Range("A:A"),
False))(1, 2).PasteSpecial Transpose:=True

End Sub

Hey, absolutely great stuff Bernie, thanks a million.

I moded it a bit, the Active Cell will always be H1, so after I enter my
data I figured I just add that to the Macro to save re-selecting that
cell. I ended up with this, if you can reduce it that would be cool. I'm
going to place an incisible rectangle over another spot and attach this
macro to it, so when I click it it runs the script, and then comes back
to where I start inputting data.

Sub CopyMatchTranspose()
Range("H1").Select
Dim myCell As Range
Set myCell = ActiveCell
myCell(2).Resize(2, 1).Copy
'The next line(s) should all be on one line when copied into your Module
Range("A:A").Cells(Application.Match(myCell.Value, Range("A:A"),
False))(1, 2).PasteSpecial Transpose:=True
CutCopyPaste = False
Range("H1").Select
End Sub

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good