Help with For loop and value enter check

[Maradona_10]

Hi All,

I'm new in VBA and learning a lot (reading VBA excel 2007 for dummies lol) but for work I need a very simple routine to create so please help on this.

So I have a calculation to do (see cell Q4 on image attached) where the variables to enter are s1,g1, s2,g2 etc.... In some cases I will only have s1 and g1 and the rest (s2,g2 etc...) will be zeros. As you can guess I cannot put 0's because I will get errors, however I can not either put a empty cell.

So this is why I want to create my first subroutine, where the variables will be from F5 to P5 and be able to drag down the formula to compute the same calculation for the lower rows.

Could someone give a hand on how to start this? As I said, I'm reading a book but the examples they give are so basic and takes a lot of time.

Thank you,

[Bernie Deitrick]

You should post the formula that you are using instead of a picture. For VBA, you would want to use a UDF (User-Defined-Function) where your code is along the lines of

Please Login or Register  to view this content.

and you would use it like

=MyNewFunction(F5:P5)

and then when you copy it, the references will update like any other Excel function.

[Maradona_10]

Hi,

Thank you , but I need a loop that checks the values of si and gi and makes the sum of it as: Sum [Si2 / 4*gi*dh].
I tried something but I still need help to make it work

Please Login or Register  to view this content.

Can someone please help me?

Thx

[Bernie Deitrick]

Maybe

Please Login or Register  to view this content.

[Maradona_10]

Hi all,

Sorry but the code above do not have the workbook. So here I'm attaching it.

So the code i've worked on is (not working):

Please Login or Register  to view this content.

I'm new in this so i tried to code the easiest part first (the for loop) however, I want to include in this code a smart 'value check' that is if there are empty cells or 0's, then the code would skip the si and gi for the next si+1 and gi+1.
What I mean is that lets say s2 and g2 are 0 and 0 but s3 and g3 are 50 and 50. Then I would like my code to skip the calculation of s2 and g2 (because mathematical we cannot divide by 0) but continue with s3 and g3 etc...

Thank you in advance.

[Bernie Deitrick]

.... include in this code a smart 'value check' that is if there are empty cells or 0's, then the code would skip

Try this one:

Bolt fixed.xlsm

which uses this code

Please Login or Register  to view this content.

[Maradona_10]

Thank you Bernie, it works awesome!!!

Just one last thing to close this thread: How can I count the columns selected in the "rngData As Range" input and make it a variable(say VariableColumn)? So that in the For Loop I don't have to put 1 to 9 but rather 1 to VariableColumn.

Thank you!!!

[Bernie Deitrick]

Please Login or Register  to view this content.