VBA to find value

[johngio]

Hi all,

I'm currently trying to write a macro that will search a range (D17:D40) and when it finds a value the same as cell C4, it will cut and paste the values from cells G15 to P15 to the G to P columns of the corresponding row.

The numbers in D17-D40 are (and always will be):

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
1
2
3
4
5

So, if the value in C4 is 3, then I want to copy the values from G15:P15 to the G38:P38

Any ideas?


I know manually I could do a match to get the row number , etc ,etc, but I'm trying to combine several smaller macros so I only have to click a single button . . .

Cheers

John

 

[acw]

John

How about

Sub ddd()
  Set findit = Range("D17:D40").Find(what:=Range("C4").Value, lookat:=xlWhole)
  findit.Offset(0, 1).Resize(1, 10).Value = Range("G15:P15").Value
End Sub

Tony

 

[johngio]

Awesome!

Works a treat. I had created a loop function but couldn't figure out the offest part :P Your way is a lot more efficient.

Thanks