Stephen Byrom
asked on
type mismatch in loop
Hi,
I get a type mismatch error and I have changed to variable types so many times I can't remember where I started! Hopefully someone can point me in the right direction.
The code in question is attached
I have I as String and K as Long. The code stops at line 11.
Maybe it's just bad syntax in the loop. I'm completely lost.
Any help as always is greatly appreciated.
ps
I should probably explain what I'm trying to do.
I want the code to look at the value of A1 in wsht1 ("I") and find that same value in column A of wsht3 ("K"), then paste the range values of wsht1 (C1:AA1) to the corresponding "K" row but offset two columns right (Column C in effect)
I hope I have explained it clear enough.
I get a type mismatch error and I have changed to variable types so many times I can't remember where I started! Hopefully someone can point me in the right direction.
The code in question is attached
I have I as String and K as Long. The code stops at line 11.
Maybe it's just bad syntax in the loop. I'm completely lost.
Any help as always is greatly appreciated.
ps
I should probably explain what I'm trying to do.
I want the code to look at the value of A1 in wsht1 ("I") and find that same value in column A of wsht3 ("K"), then paste the range values of wsht1 (C1:AA1) to the corresponding "K" row but offset two columns right (Column C in effect)
I hope I have explained it clear enough.
Set wbk1 = ActiveWorkbook
Set wsht1 = wbk1.Sheets("Sheet1")
Set wbk3 = Workbooks.Open(wbk3path) 'Interface workbook
Set wsht3 = wbk3.Sheets(1)
wsht3.Range("A1:AA2").Formula = wsht1.Range("A1:AA2").Value
wsht1.Range("A1").EntireRow.Delete
wsht1.Range("A1").EntireRow.Delete
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
K = wsht3.Columns("A:A").Find(What:=I, lookat:=xlWhole)
wsht3.Range(K).Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRow.Delete
Loop Until I = ""
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry:
if not ( findK is Nothing ) then
if not ( findK is Nothing ) then
ASKER
Thanks for persevering.
I now get the error "Object variable or With block variable not set"
The code is now
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
findK = wsht3.Columns("A:A").Find( What:=I, lookat:=xlWhole)
If Not (findK Is Nothing) Then
K = findK.Value
Range(K).Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo w.Delete
End If
Loop Until I = ""
I now get the error "Object variable or With block variable not set"
The code is now
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
findK = wsht3.Columns("A:A").Find(
If Not (findK Is Nothing) Then
K = findK.Value
Range(K).Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo
End If
Loop Until I = ""
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the input ssaqibh, but i still get the error and I've changed the code to this;
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find( What:=I, lookat:=xlWhole)
If Not (findK Is Nothing) Then
K = findK
K.Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo w.Delete
End If
Loop Until I = ""
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find(
If Not (findK Is Nothing) Then
K = findK
K.Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo
End If
Loop Until I = ""
ASKER
Tried this also;
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find( What:=I, lookat:=xlWhole)
If Not (findK Is Nothing) Then
K = findK
wsht3.Range(K).Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo w.Delete
End If
Loop Until I = ""
Still get the "Object variable or With block variable not set" error
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find(
If Not (findK Is Nothing) Then
K = findK
wsht3.Range(K).Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo
End If
Loop Until I = ""
Still get the "Object variable or With block variable not set" error
Ok Try this
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find( What:=I, lookat:=xlWhole)
If Not (findK Is Nothing) Then
K = findK
wsht3.Range(findK.Address) .Offset(0, 2).PasteSpecial xlPasteValues
wsht1.Range("A1").EntireRo w.Delete
End If
Loop Until I = ""
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find(
If Not (findK Is Nothing) Then
K = findK
wsht3.Range(findK.Address)
wsht1.Range("A1").EntireRo
End If
Loop Until I = ""
ASKER
managed it!!
new code is this;
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find( What:=I, lookat:=xlWhole)
If Not (findK Is Nothing) Then
findK.Offset(0, 2).PasteSpecial xlPasteValues
End If
wsht1.Range("A1").EntireRo w.Delete
Loop Until I = ""
new code is this;
Do
I = wsht1.Range("A1")
wsht1.Range("C1:AA1").Copy
Dim findK As Object
Set findK = wsht3.Columns("A:A").Find(
If Not (findK Is Nothing) Then
findK.Offset(0, 2).PasteSpecial xlPasteValues
End If
wsht1.Range("A1").EntireRo
Loop Until I = ""
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for olorin57's comment #a38798967
Assisted answer: 250 points for angelIII's comment #a38798639
Assisted answer: 250 points for ssaqibh's comment #a38798844
for the following reason:
Had to split the points. Thanks to you both for your advice and pointers.
Accepted answer: 0 points for olorin57's comment #a38798967
Assisted answer: 250 points for angelIII's comment #a38798639
Assisted answer: 250 points for ssaqibh's comment #a38798844
for the following reason:
Had to split the points. Thanks to you both for your advice and pointers.
ASKER
Had to split the points
Thanks to you both
Thanks to you both
ASKER
I get an error "Invalid use of object" when I run it though, and the "Nothing" is highlighted