Quick Question - Edit Macro

[Roger]

Hello,

Can you help me to set this macro up so it works between 2 workbooks vs.
just working on the same sheet viewing different columns?

I’m looking for Sheet1 in Workbook A to look for a Match in Sheet2 Workbook
B. I’ve filled in the ranges as they would appear in each of the Workbooks.
Range(“E2:E100â€) is the range in Workbook A looking to match Range(“A2â€A100)
in Workbook B. If match is found, Workbook A Range (“B2:B100â€) would copy
cell values to destination Workbook B Range(“â€C2:C100â€).

Appreciate any help you can provide.

Thanks - Roger

Sub TestCopyFormat()

Dim Cell As Range
Dim Cindex As Integer
On Error GoTo errorhandler
Application.DisplayAlerts = False
With Worksheets("Sheet1")
For Each Cell In Range("E2:E100")
Cindex = WorksheetFunction.Match(Cell.Text, .Range("A2:A100"), 0)
.Range("B2:B100").Cells(Cindex, 1).Copy _
Destination:=Range("C2:C100").Cells(Cindex, 1)
Next Cell
End With
errorhandler:
Application.DisplayAlerts = True
End Sub

 

[Dave Peterson]

I _think_ this does what you want.

Dim WkbkARng as range
dim WkbkBRng as range
dim myCell as range
dim res as variant 'could be a number or an error

set wkbkARng = workbooks("workbookA.xls").worksheets("sheet1").range("E2:e100")
set wkbkBRng = workbooks("workbookB.xls").worksheets("sheet2").range("a2:a100")

for each mycell in wkbkarng.cells
res = application.match(mycell.value,wkbkbrng,0)
if iserror(res) then
'no match, do nothing
else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res)

'or just to bring back the value
'wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
end if
next mycell


Untested, uncompiled--watch for typos.

 

[Roger]

Hi Dave,

Thank you so much for answering this- it's really been giving me a sharp
pain in the backside.

You noted that I could bring back the value by swapping the line of code as
noted below. The problem is once I swap, I get an error and I'm not sure
what I'm doing wrong.

Thanks again for your review - Roger

myCell.Offset(0, 1).Copy _
Destination:=WkbkRng(res) <<<< tried to replace as shown below


myCell.Offset(0, 1).Copy
Destination:=wkbkbrng(res).offset(0,1).value = mycell.offset(0,1).value
<<< received error with above>>>>

 

[Dave Peterson]

What error did you get?

And maybe you wanted this:

mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)

Did you want to bring back the column to the right of the matching cell and
place it in the cell to the right of the original cell?

 

[Roger]

That is exactly what I wanted and it is now working flawlessly.

Thank you for your help and you rock!

Roger

 

[Dave Peterson]

Glad you got it working--and sorry about the original typo.