macro not giving right sum
 

I am trying to compare a value and then add that value to a cell. but
my sum is not coming right





Sub compare_price()
Dim i, j, k As Integer
Dim X, LR, LR1 As Long

LR = Worksheets("Expenses").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
LR1 = Worksheets("Statement").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
Set starta = Worksheets("Expenses").Range("B7")
Set startb = Worksheets("Statement").Range("A1")

'****set date format*****
For i = 1 To LR
starta.Offset(i, 0).Value = starta.Offset(i, 0).Value
starta.Offset(i, 0).NumberFormat = "mm/dd/yy;@"
Next i


starta.Offset(-2, 4) = 0
For i = 1 To 18
For j = 0 To LR1
If (starta.Offset(i, 4).Value = startb.Offset(j, 2).Value * (-1) And
starta.Offset(i, 0) = startb.Offset(j, 0)) Then
If starta.Offset(i, 4) < Empty Then
starta.Offset(i, 4).Interior.ColorIndex = 6
End If
'*******THIS LINE BELOW NEEDS CORRECTION*******
starta.Offset(-2, 4) = starta.Offset(-2, 4).Value + starta.Offset(i,
4).Value

End If
Next j
Next i

End Sub

macro not giving right sum
 

If I am reading your code right, then this line:

starta.Offset(-2, 4) = starta.Offset(-2, 4).Value + starta.Offset(i,
4).Value

would be executing on row 6 at the first iteration and would move down one
there after for 18 iterations.

I don't know if that is what you want, or not.

"sharmashanu" wrote:

I am trying to compare a value and then add that value to a cell. but
my sum is not coming right





Sub compare_price()
Dim i, j, k As Integer
Dim X, LR, LR1 As Long

LR = Worksheets("Expenses").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
LR1 = Worksheets("Statement").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
Set starta = Worksheets("Expenses").Range("B7")
Set startb = Worksheets("Statement").Range("A1")

'****set date format*****
For i = 1 To LR
starta.Offset(i, 0).Value = starta.Offset(i, 0).Value
starta.Offset(i, 0).NumberFormat = "mm/dd/yy;@"
Next i


starta.Offset(-2, 4) = 0
For i = 1 To 18
For j = 0 To LR1
If (starta.Offset(i, 4).Value = startb.Offset(j, 2).Value * (-1) And
starta.Offset(i, 0) = startb.Offset(j, 0)) Then
If starta.Offset(i, 4) < Empty Then
starta.Offset(i, 4).Interior.ColorIndex = 6
End If
'*******THIS LINE BELOW NEEDS CORRECTION*******
starta.Offset(-2, 4) = starta.Offset(-2, 4).Value + starta.Offset(i,
4).Value

End If
Next j
Next i

End Sub

macro not giving right sum
 

I didn't read it right, it would be executing on row 5 and adding row 8 value
on the first iteration and would then increment one row only for the cell
value that is added by the starta.Offset(i,4).Value statement. In
otherwords, each iteration adds a different value to the same cells in row 5
on each iteration.


"sharmashanu" wrote:

I am trying to compare a value and then add that value to a cell. but
my sum is not coming right





Sub compare_price()
Dim i, j, k As Integer
Dim X, LR, LR1 As Long

LR = Worksheets("Expenses").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
LR1 = Worksheets("Statement").Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row
Set starta = Worksheets("Expenses").Range("B7")
Set startb = Worksheets("Statement").Range("A1")

'****set date format*****
For i = 1 To LR
starta.Offset(i, 0).Value = starta.Offset(i, 0).Value
starta.Offset(i, 0).NumberFormat = "mm/dd/yy;@"
Next i


starta.Offset(-2, 4) = 0
For i = 1 To 18
For j = 0 To LR1
If (starta.Offset(i, 4).Value = startb.Offset(j, 2).Value * (-1) And
starta.Offset(i, 0) = startb.Offset(j, 0)) Then
If starta.Offset(i, 4) < Empty Then
starta.Offset(i, 4).Interior.ColorIndex = 6
End If
'*******THIS LINE BELOW NEEDS CORRECTION*******
starta.Offset(-2, 4) = starta.Offset(-2, 4).Value + starta.Offset(i,
4).Value

End If
Next j
Next i

End Sub

macro not giving right sum
 

thanks ....it worked