Increment Formula With If Statement - Xtreme Visual Basic Talk

08-07-2012, 06:28 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

Increment Formula With If Statement

Hey everyone, I'd like to know, if its possible, how to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.

I tried to apply the code for all the but this makes my file huge like 20MB!

You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.

https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm

Thx in advance!

08-09-2012, 04:15 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

Anyone?

08-09-2012, 10:03 AM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

Do you mean VBA code to change a cell formula? It is not entirely clear from your description exactly what you are trying to do. Also, the dropbox link is not working. Post some example VBA code and any relevant cell formulas you have tried here.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

08-10-2012, 03:06 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

Quote:

Originally Posted by Cerian Knight

The formula i'm using is this one:

=IF(R10<>"";MOD(R10-Q10;1);"")

If you open the file below you'll understand, just go to the records sheet and you'll see. I don't want to apply the formula to the whole column, I just want to add the formula to one more cell if there is a value start time and end time.

Thx in advance

This new link is working > https://www.dropbox.com/s/qb2m9a7a6at7e68/project.xlsm

08-10-2012, 09:38 AM

Gruff

Bald Mountain Survivor

Super Moderator
* Expert *

Join Date: Aug 2003

Location: Oregon, USA

Posts: 5,883

Spaggiari,

1) Not everyone can open an xlsm file. Many have earlier versions of Excel.
This is why CK asked you to post the code you are using as text.

2) You still did not answer Cerian Knight's VBA code question.

If you want to run a program on static data then you do not need to put a formula in the cells. VBA can do the calculations and put a hard value in the cells.
Things will change only when you run the program and the resullting file should be much smaller.

__________________
Burn the land and boil the sea
You can't take the sky from me

~T

08-10-2012, 12:14 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

I did finally manage to convert the xlsm to look at it.

I think you need to enumerate by UsedRange. Something like this (not fully tested):

Code:

Dim I As Long
For I = 6 To Worksheets("records").UsedRange.Rows.count
  If Cells(I, 18).Value - Cells(I, 17).Value > 0 Then
    'by formula:
    Cells(I, 2).Formula = "=IF(R" & CStr(I) & "<>"""",MOD(R" & CStr(I) & "-Q" & CStr(I) & ",1),"""")"
    'or by value
    Cells(I,2).Value = Cells(I, 18).Value - Cells(I, 17).Value
  Else
    'by formula
    Cells(I, 2).Formula = ""
    'or by value
    Cells(I,2).Value = ""
  End If
Next

Another, possibly much better, idea is to alter the .Value of the cell based on parsing the Worksheet_Change event Range values to see if any cells in the 'Range' match the column criteria. This would automate the process so it is not tied to any button... if that is advantageous.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

Last edited by Cerian Knight; 08-10-2012 at 03:55 PM.

08-13-2012, 04:17 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

Quote:

Originally Posted by Gruff

Hey Gruff I didn't post any code because I didn't have any, I'm really stuck at this and I asked for help in that way. I know that not everyone can open a xlsm file and I know that people may have earlier versions I just uploaded the file so it would be easier to others if they saw the app don't take me wrong.

Anyways I'll work on the code CK posted and I'll keep this thread updated.

Ty

08-17-2012, 03:38 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

This is what I came up with, created a table and applied a formula to the fields and its working fine.

You can take a look here https://www.dropbox.com/s/qb2m9a7a6a...project.xlsm?m

Ty

08-17-2012, 07:29 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

Glad to hear you got it working. I was actually kind of impressed with what you had done (e.g., UserForms, etc.). One of the stumbling blocks to creating some of the code in my example, is the frustrating recognition that the IntelliSense is somewhat broken in VBA (2003, anyway)... so it is not easy to find some of the useful syntax (unless you already know it exists).

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

#10

08-20-2012, 03:30 AM

Spaggiari

Newcomer

Join Date: Aug 2012

Posts: 9

Quote:

Originally Posted by Cerian Knight

Got it working with some help from other forums too, and yes its not easy to find the syntax unless you already know it like you said.

Cheers