Now, how can I get it write to another workbook? This one may be a bit
tricker, because the workbook I want to write to doesn't always have the
same name (it is always open though).

Change this line (to the equal sign):

worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")

to something like:

workbooks(ws.Name).activesheet.ActiveCell.FormulaR 1C1 =

You may need to tweak this a little but basically you need to
reference the workbook, then the worksheet and then the cell.


On Fri, 17 Aug 2012 20:15:09 +0000, KeriM
wrote:


John Keith;1604696 Wrote:
As usual there are many ways to address the next requirements for
testing the cell contents as you describe below. But the simplest
solution I would use would be to test the cell to see if it contains
"a" or "1" by using the instr function like this:

For i = first_row to last_row
if instr(cells(i, "B")',"a")0 and instr(cells(i, "C"),"1")0 then _
worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")
Next i

The second line in the loop will store your result in the same row on
the target sheet as the source sheet.

For only 400 lines of data the for loop will be very fast.



John,

Perfect! It does the calculation and writes it on the source worksheet.


Now, how can I get it write to another workbook? This one may be a bit
tricker, because the workbook I want to write to doesn't always have the
same name (it is always open though). This is how it is written (and
working) for my other sheets:


Code:
--------------------


Dim ws As Workbook
If ws.Name Like "*Count*" Then
Windows(ws.Name).Select
Range("C3").Select
ActiveCell.FormulaR1C1 = 'write answer here'

--------------------


When I tried to implement this code into yours, it was posting a value
of 0, probably because it lost the values when I switched sheets. Can I
store the answer in a variable and then call the variable to write the
answer in the cell?

John Keith