Loops are not working in my code

[amitkrb]

Hi friends,
I am a beginner to VBA. I wrote a code to basically compare two sheets of data by their brand name in column A. When they match, I want to subtract the corresponding values of sheet 1 and sheet 2 for same brand. I am using this code.
Please help , it is giving a Next without For error

The code is:-

Option Explicit

Global Length1 As Integer, Length2 As Integer, i As Integer, j As Integer, k As Integer, Diff As Long








Public Sub mainQC()


i = 1
j = 1
k = 1


Length1 = Workbooks("Book1").Sheets("sheet1").Range("strtpoint1").End(xlDown).Row


Length2 = Workbooks("Book1").Sheets("sheet2").Range("strtpoint2").End(xlDown).Row
For i = 1 To Length1


For j = 1 To Length2


If Trim(StrConv(Workbooks("Book1").Sheets("sheet1").Range("A" & i).Value, vbProperCase)) = _
Trim(StrConv(Workbooks("Book1").Sheets("sheet2").Range("A" & j).Value, vbProperCase)) _
Then




Workbooks("Book1").Sheets("sheet3").Range("A" & k).Value _
 = Workbooks("Book1").Sheets("sheet1").Range("A" & i).Offset(0, k).Value - _
Workbooks("Book1").Sheets("sheet2").Range("A" & j).Offset(0, (k + 1)).Value


Exit For


Next i




End If
















End Sub

Any help would be appreciated .. Thanks!

 

[Gary's Student]

Tuck any necessary End If's inside the Nexts

 

[VoG]

Maybe

Global Length1 As Integer, Length2 As Integer, i As Integer, j As Integer, k As Integer, Diff As Long
Public Sub mainQC()
i = 1
j = 1
k = 1
Length1 = Workbooks("Book1").Sheets("sheet1").Range("strtpoint1").End(xlDown).Row
Length2 = Workbooks("Book1").Sheets("sheet2").Range("strtpoint2").End(xlDown).Row
For i = 1 To Length1
    For j = 1 To Length2
        If Trim(StrConv(Workbooks("Book1").Sheets("sheet1").Range("A" & i).Value, vbProperCase)) = _
            Trim(StrConv(Workbooks("Book1").Sheets("sheet2").Range("A" & j).Value, vbProperCase)) _
            Then
            Workbooks("Book1").Sheets("sheet3").Range("A" & k).Value _
             = Workbooks("Book1").Sheets("sheet1").Range("A" & i).Offset(0, k).Value - _
            Workbooks("Book1").Sheets("sheet2").Range("A" & j).Offset(0, (k + 1)).Value
            Exit For
        End If
    Next j
Next i
End Sub

 

[amitkrb]

Hi,
What I was trying to do is this:
Sheet 1:-

ABC	12	15	11
DEF	23	21	42
JKL	13	45	33

<tbody>
</tbody>

Sheet 2:

DEF	143	31	11
ABC	12	33	12
JKL	11	34	12

<tbody>
</tbody>

I was basically trying to take sheet 1 as reference and subtract corresponding values of ABC, DEF, JKL in sheet 1 and 2 and paste the difference in sheet 3. For example for ABC, it would be
ABC 0 12 -30

The nth column of Sheet 1 is substracted from the (n+1)th column of sheet 2.

Please help!!

 

[amitkrb]

Thanks a lot buddy