• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If statement where rows are a variable and number of columns over may change.

N

nbeharry

New Member
If Val(sh1.Cells(i, 22)) > 0 Then


The above statement in my loop works great but if I insert/delete columns, I cannot use the fixed number of 22.


The number will change depends on how many columns inserted/deleted.


How can I use fix this. Please help! Thanks.
 
Instead of 22

Do something like

Range("MyRng").count


Then setup a named formula MyRng as

=offset($e$1,,,,columns($e$1:$aa$1))


Obviously adjust to suit
 
Hi Hui..Thanks for responding to my post. I'm not sure I understand..i'm not good at this at all.:(.

Please take a look at my file and as you will see, if I insert/delete column and everything shift, I will be picking up the incorrectt column. Can you please let me know how to fix this. Thanks.


http://www16.zippyshare.com/v/68614848/file.html
 
Hi, nbeharry!


First of all and sorry to say, the table at your worksheet wksht is slightly unconventional. Try doing this:


a) Place an asterisk in X6 cell (next column of end table at row vendors).


b) Define a dynamic named ranged DataTable as:

=DESREF(wksht!$A$11;;;CONTARA(wksht!$A:$A)-1;COINCIDIR("*";wksht!$6:$6;0)-1) -----> in english: =OFFSET(wksht!$A$11,,,COUNTA(wksht!$A:$A)-1,MATCH("*",wksht!$6:$6,0)-1)


c) Replace your code:

-----

If Val(sh1.Cells(i, 22)) > 0 Then

-----

by this:

-----

If Val(sh1.Cells(i, Range("DataTable").Columns.Count - 1)) > 0 Then

-----

qualifying Range with Worksheets("wksht"). if VBA code in other worksheet or in a module.


Regards!


PS: Check this link for the fixed file:

https://dl.dropboxusercontent.com/u/60558749/If%20statement%20where%20rows%20are%20a%20variable%20and%20number%20of%20columns%20over%20may%20change.%20-%20NalMGmt%20%28for%20nbeharry%20at%20chandoo.org%29.xlsm
 
Thanks for answering SirJB7. I downloaded the link for the fixed file and it is the same as the one I sent. Sorry for the bother but can you please take a look and let me know. Thank you soo much for all your help.
 
Oh, and yes, I also sorry for the unconventional worksheet..it's what I was given to work with and couldn't really change the setup.
 
Hi, nbeharry!

It isn't exactly the same file that you uploaded, it has a) and b) points (asterisk in X6 and dynamic named range definition), the only thing that I left you as homework was c) (replacing the instruction that originated your question).

Regards!
 
Oh I see, thank you so much SirJB7..u did all the hard work and leave with a piece of cake...u r the best..BIG THANKS..:)
 
Hi, nbeharry!

Just advise if any issue.

Regards!

PS: In fact I avoided the hard work, defining the range was the easiest part, going thru "that" code... well, let the author do the job :)
 
You must log in or register to reply here.
Back
Top