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:-
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ABC[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]23[/TD]
[TD]21[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD]13[/TD]
[TD]45[/TD]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]DEF[/TD]
[TD]143[/TD]
[TD]31[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]12[/TD]
[TD]33[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD]11[/TD]
[TD]34[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]



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