Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 20th 08, 11:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 80
Default Compare col and match then copy and paste

Hello,

The macro below works fine, but it has a draw back. When it compares col. A
from sheet 1 and 2, there shouldn't be any blank or empty cells in the range
otherwise it won't work.
Is there any way around this or mabe another macro that works with empty
cells?

thx.

here is the macro:

Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

For Each cell In rng
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
Next
End Sub


'This is what it does without the blank cell in between the range.

Sheet 1

A B C D E F G
12
13
14

Sheet 2

D E F G
11 1 2 3
13 4 5 6
16 7 8 9
14 3 2 1

Result after Function;

Sheet 1

A B C D E F G
12
13 4 5 6
14 3 2 1

--
Message posted via http://www.officekb.com


  #2   Report Post  
Old February 21st 08, 12:41 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Compare col and match then copy and paste

First, if there can be empty cells, I'd use xlup, not xldown:

Option Explicit
Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range("A1", .cells(.rows.count,"A").end(xlup))
End With

With Worksheets("Sheet2")
Set rng1 = .Range("A1", .cells(.rows.count,"A").end(xlup))
End With

For Each cell In rng.cells
if trim(cell.value) = "" then
'skip it
else
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
end if
Next cell
End Sub

(Untested, but it did compile.)


"saman110 via OfficeKB.com" wrote:

Hello,

The macro below works fine, but it has a draw back. When it compares col. A
from sheet 1 and 2, there shouldn't be any blank or empty cells in the range
otherwise it won't work.
Is there any way around this or mabe another macro that works with empty
cells?

thx.

here is the macro:

Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

For Each cell In rng
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
Next
End Sub

'This is what it does without the blank cell in between the range.

Sheet 1

A B C D E F G
12
13
14

Sheet 2

D E F G
11 1 2 3
13 4 5 6
16 7 8 9
14 3 2 1

Result after Function;

Sheet 1

A B C D E F G
12
13 4 5 6
14 3 2 1

--
Message posted via http://www.officekb.com


--

Dave Peterson
  #3   Report Post  
Old February 21st 08, 01:28 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 80
Default Compare col and match then copy and paste

Thank you. It worked well.

Dave Peterson wrote:
First, if there can be empty cells, I'd use xlup, not xldown:

Option Explicit
Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant

With Worksheets("sheet1")
Set rng = .Range("A1", .cells(.rows.count,"A").end(xlup))
End With

With Worksheets("Sheet2")
Set rng1 = .Range("A1", .cells(.rows.count,"A").end(xlup))
End With

For Each cell In rng.cells
if trim(cell.value) = "" then
'skip it
else
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
end if
Next cell
End Sub

(Untested, but it did compile.)

Hello,

[quoted text clipped - 57 lines]
--
Message posted via http://www.officekb.com



--
Message posted via http://www.officekb.com



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare two wk sheets with common data using copy paste macro conejo Excel Worksheet Functions 0 October 8th 07 09:21 AM
How to match and compare two spreadsheets Brad Excel Worksheet Functions 1 June 28th 07 09:32 AM
Countif and Index Match copy and paste thesaxonuk Excel Discussion (Misc queries) 0 October 23rd 06 03:15 PM
Compare and Match Functions DP63 Excel Discussion (Misc queries) 1 June 20th 06 04:00 PM
Excel cut/Paste Problem: Year changes after data is copy and paste Asif Excel Discussion (Misc queries) 2 December 9th 05 06:16 PM


All times are GMT +1. The time now is 08:18 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017