Sheet to sheet code Type Mismatch
 

I want to go down the list on sheet2 D column and compare to sheet1 A column and if a match copy column A .Offset(0, 1) back to sheet2 column D .Offset(0, 1).

I can't sniff out the type mismatch error I'm getting.

Then I need to do the same thing as this code but do it between two workbooks.

Thanks,
Howard

Option Explicit

Sub S_1ToS_2()
Dim c As Range
Dim c1 As Variant

For Each c In Sheets(Sheet2).Range("D1:D10")
For Each c1 In Sheets(Sheet1).Range("A1:A25")
If c1.Value = c.Value Then
c1.Offset(0, 1).Copy c.Offset(0, 1)
End If
Next
Next

End Sub

Sheet to sheet code Type Mismatch
 

Hi Howard,

Am Tue, 23 Apr 2013 02:47:32 -0700 (PDT) schrieb Howard:

I want to go down the list on sheet2 D column and compare to sheet1 A column and if a match copy column A .Offset(0, 1) back to sheet2 column D .Offset(0, 1).

I can't sniff out the type mismatch error I'm getting.

Then I need to do the same thing as this code but do it between two workbooks.

without looping through all cells in both sheets:

Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("E1:E10")
.Formula = "=Vlookup(D1,Sheet1!A1:B25,2,0)"
.Value = .Value
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Sheet to sheet code Type Mismatch
 

Hi Howard,

Am Tue, 23 Apr 2013 12:00:10 +0200 schrieb Claus Busch:

.Formula = "=Vlookup(D1,Sheet1!A1:B25,2,0)"

there is a typo in the formula above.
Change to:
..Formula = "=Vlookup(D1,Sheet1!$A$1:$B$25,2,0)"


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Sheet to sheet code Type Mismatch
 

Am Tue, 23 Apr 2013 02:47:32 -0700 (PDT) schrieb Howard:

I can't sniff out the type mismatch error I'm getting.

you can refere to your sheet with sheet name or with code name

Either you have to write:
For Each c In Sheets("Sheet2").Range("D1:D10")
or:
For Each c In Sheet2.Range("D1:D10")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Sheet to sheet code Type Mismatch
 

On Tuesday, April 23, 2013 3:17:10 AM UTC-7, Claus Busch wrote:
Am Tue, 23 Apr 2013 02:47:32 -0700 (PDT) schrieb Howard:



I can't sniff out the type mismatch error I'm getting.



you can refere to your sheet with sheet name or with code name



Either you have to write:

For Each c In Sheets("Sheet2").Range("D1:D10")

or:

For Each c In Sheet2.Range("D1:D10")





Regards

Claus Busch

Thanks Claus, works perfectly.

I may be back to get the proper syntax to do the same between two workbooks.
Seems I just need to get the workbook names in front of the sheet names and use the same bacic code.

Regards,
Howard

Sheet to sheet code Type Mismatch
 

Thanks Claus, works perfectly.



I may be back to get the proper syntax to do the same between two workbooks.

Seems I just need to get the workbook names in front of the sheet names and use the same bacic code.



Regards,

Howard

Easier than I thought.

This seems to work just fine from workbook to workbook.

Sub Bk4_To_Bk3()
With Sheets("Sheet2").Range("I1:I10")
.Formula = "=VLOOKUP(H1,[Book4]Sheet1!$A$1:$B$25,2,0)"
.Value = .Value
End With
End Sub

Thanks much, Claus. Always appreciate your fine advice.

Regards,
Howard

Sheet to sheet code Type Mismatch
 

Hi Howard,

Am Tue, 23 Apr 2013 04:38:45 -0700 (PDT) schrieb Howard:

Easier than I thought.

This seems to work just fine from workbook to workbook.

Sub Bk4_To_Bk3()
With Sheets("Sheet2").Range("I1:I10")
.Formula = "=VLOOKUP(H1,[Book4]Sheet1!$A$1:$B$25,2,0)"
.Value = .Value
End With
End Sub

always glad to help. Thank you for the feedback.
If you have spaces in the workbook name don't forget the apostrophes.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2