Compare one sheet to another and calculate the difference? 1

[knifey]

Hi,
I'm trying to write a sub to compare each cell in a range to each cell in another range (last months sales data & this months sales data in different workbooks). Then put the variance into another sheet (ie. this month we sold 35 than last month). Also a percentage value of the variance would be useful.
I'm a complete newbie, can anyone point me in the right direction?
Thanks,
Roy

 

[mintjulep]

There is no apparent need for VBA to do what you ask for.

 

[knifey]

Thanks for that, but it dosen't really tell me much. I've come up with this below but it's not working properly. Can anyone help?
Thanks,
Roy
For i2 = 1 To rCount
If i2.value = wBook1.Worksheets("Sheet1").i1.value Then
i2.Offset(0, 1).value = i2.value
var = "="
ElseIf i2.value < wBook1.Worksheets("Sheet1").i1.value Then
integervar = wBook1.Worksheets("Sheet1").i1.value - i2.value
var = "<"
ElseIf i2.value > wBook1.Worksheets("Sheet1").i1.value Then
integervar = i2.value - wBook1.Worksheets("Sheet1").i1.value
var = ">"
i2.Offset(0, 1).value = var & integervar
i1 = i1 + 1
i2 = i2 + 1
Next i2

 

[mintjulep]

You asked to be pointed in the right direction. I pointed.

 

[knifey]

Could you point a bit more clearly?

 

[mintjulep]

... compare each cell in a range to each cell in another range (last months sales data & this months sales data in different workbooks). Then put the variance into another sheet (ie. this month we sold 35 than last month).

A lot of words, but it seems to boil down to subtract one number from another. This can be done with a simple formula.

=[Book2]Sheet1!$A$1-[Book1]Sheet1!$A$1

Also a percentage value of the variance would be useful.

Again, just a formula.

=([Book2]Sheet1!$A$1-[Book1]Sheet1!$A$1)/[Book2]Sheet1!$A$1

 

[nath]

Well it seems knifey wants to do it programmatically which seems to me a good move.

To give you an idea, you i2.value is not really going to work. If myRow is a row number (integer) and mycol is a column number, you can refer to the value of a cell using syntax like:

LastMonthWorkbook.Worksheet("Sheet1").cells(myrow,mycol).value

So you should loop through your rows , get your values than compare them. Something like:

for curRow = 1 to rowCount
myValLastMonth = mybookLastmonth.worksheet("sheet1").cells(curRow, 1).value
myvalThismonth = myBookThisMonth.worksheet("sheet1").cells(currow,1).value

if myvallastmonth = myvalthismonth then
myBookThisMonth.worksheet("sheet1").cells(currow,1).value=myvalthismonth
....
....
....
next i

I've written this on the fly. You will have to modify and verify it but it should point you in the right direction .

Good luck

Nate