Sheet to sheet code Type Mismatch

  • Thread starter Thread starter Howard
  • Start date Start date
H

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("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
 
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.
Click to expand...

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
 
Hi Howard,

Am Tue, 23 Apr 2013 12:00:10 +0200 schrieb Claus Busch:
.Formula = "=Vlookup(D1,Sheet1!A1:B25,2,0)"
Click to expand...

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


Regards
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.
Click to expand...

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
 
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("D1:D10")

or:

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





Regards

Claus Busch
Click to expand...
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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top