Link to home
Start Free TrialLog in
Avatar of Stephen Byrom
Stephen ByromFlag for Ireland

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.

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 = ""

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stephen Byrom

ASKER

thanks for the input.
I get an error "Invalid use of object" when I run it though, and the "Nothing" is highlighted
sorry:

if not ( findK is Nothing ) then
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").EntireRow.Delete
        End If
Loop Until I = ""
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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").EntireRow.Delete
        End If
Loop Until I = ""
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").EntireRow.Delete
        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").EntireRow.Delete
        End If
Loop Until I = ""
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").EntireRow.Delete
Loop Until I = ""
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.
Had to split the points
Thanks to you both