Sheet to sheet code Type Mismatch

[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.

Thanks,
Howard

Option Explicit

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

For Each c In Sheets(Sheet2).Range("D110")
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

 

[Claus Busch]

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

 

[Claus Busch]

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

 

[Claus Busch]

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("D110")
or:
For Each c In Sheet2.Range("D110")


Regards
Claus Busch

 

[Howard]

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






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("D110")

or:

For Each c In Sheet2.Range("D110")





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

 

[Howard]

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

 

[Claus Busch]

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