Active Cell Syntax

[Ascension]

Pretty basic/easy question to answer here, but is this the best way to increase the value of a cell in a looped statement? I can't name the cell or reference it through range as the loop is also moving to different locations. Thanks a lot!

Please Login or Register  to view this content.

[solnajeff]

Hi

It is more efficient to avoid selecting cells in Excel and as you have to have some method of selection within the loop it would be better to use that method to increment the cell value and avoid selecting the cell completely.

Regards

Jeff

[TMS]

How are you moving the activecell to different locations?

Can you post a sample workbook and the full code.

We can then better understand what you need to achieve and propose a solution.

Regards

[Ascension]

Following Solna's suggestion I stripped out the activecell functions, and ended up with this, but now I get type mismatch

Please Login or Register  to view this content.

I need to start with a "known" location on the spreadsheet, and then use MemOccur (and other variables like it) to "move" to a new location on the spreadsheet and perform actions.

[TMS]

Is your code secret? It's rather difficult to assess this in isolation. Given that you haven't set MemOccur or MemDollar, they will have an implicit value of zero.

So that line of code won't change anything, AFAICS.

However, I think the line of code should look like:

Sheets("Sheet4").Range("FirstRent").Offset(MemOccur, 0).Value = _
Sheets("Sheet4").Range("FirstRent").Offset(MemOccur, 0).Value + MemDollar


Is "FirstRent" a cell or a range of cells? If it's a range of cells, I think that's what will give you your Type Mismatch.

Regards

[Ascension]

I can't upload all the code no, and I understand the difficulty of piecing some of this together without the whole picture, but the variables would hold values. As for your suggested code changing Sheet4 (which AFAIK is VBA code and doesn't change) to Sheets("Sheets4") shouldn't change any functionality, but would expose the spreadsheet to the risk of the tab being renamed (which it already has been) and then the macro breaking. The named range is also a specific cell.

[Ascension]

The second variation with the Sheet2/Sheet4 ended up working, of all the things to go wrong it was the offset function. Thank you for the help!