Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not giving right sum
thanks ....it worked
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Giving Macro control to another workbook | Excel Programming | |||
Macro giving runtime error on one PC and not another | Excel Programming | |||
Macro giving runtime error on one PC and not another | Excel Programming | |||
Why isn't this macro giving me proper results? | Excel Programming | |||
Macro is giving me problems with sorting | Excel Programming |