Prev: Going through Named Ranges in only specific Sheets in my WorkBook
Next: Negative to zero code
From: Eddy Stan on 13 May 2010 16:59
Hi all,
i have 2 sheets as below
column c J
row 6 511 285
row 7 285
row 8 422 625

sheet2 col b m
row 100 510
row 105 511 285
row 120 282
row 125 285
row 800 422 625

find sheet1 511 and 422 in sheet2 (because value next to them is > 0) then
put next to them 285 and 625 in sheet2 from sheet1
thanks in advance

regards,
Eddy stan

From: PY & Associates on 14 May 2010 00:23
On May 14, 4:59 am, Eddy Stan <EddyS...(a)discussions.microsoft.com>
wrote:
> Hi all,
> i have 2 sheets as below
> column     c        J
> row 6      511    285
> row 7      285
> row 8      422    625
>
> sheet2       col b   m
> row 100      510
> row 105      511   285
> row 120      282  
> row 125      285
> row 800      422   625
>
> find sheet1 511 and 422 in sheet2 (because value next to them is > 0) then
> put next to them 285 and 625 in sheet2 from sheet1
> thanks in advance
>
> regards,
> Eddy stan

something like this

Option Explicit
Sub m()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

Dim rng1 As Range
Dim rng2 As Range
Dim frow As Integer
Dim c As Range
Set rng1 = ws1.Range("C6:C8")
Set rng2 = ws2.Range("B100:B800")
On Error Resume Next

For Each c In rng1
If c.Offset(0, 7) > 0 Then
frow = rng2.Find(c).Row
If frow > 0 Then ws2.Cells(frow, "M") = c.Offset(0, 7)
frow = 0
End If
Next c
End Sub
 | 
Pages: 1
Prev: Going through Named Ranges in only specific Sheets in my WorkBook
Next: Negative to zero code