Numericall assigning cells in VBA

[BartNL]

Good afternoon everybody,

Excuse me for my title, but I really don't know what tag I could give my problem.

Here it is:

I'm trying with the help of VBA to find cells on a numericall way.
I'm looking for data in a cell. If this data meets mt requirements, than I will asign a 1 to range("J27"). (pure for testing).
The first data is in cell F11. The one in F12 etcetera. This has to be done for a whole lot of F's. Also, all the data is not +1 from each other, so then I will use i+3 for example. But because of the fact that this data can be extended in the future, (and a lot!), I have to work with an "i".

This is what I have so far:

Please Login or Register  to view this content.

When I run this, nothing happens.
My suspicion is that I have to Define "i", or that there is more wrong with my code.
I hope that you understand my story and also that you can help me with it!

Thank you in advance!

Kind regards,
Bart

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[oeldere]

@BartNL

Welcome to the excel forum.

You get better help if you add an excel file, without confidential information.

Please also add the desired (excpected) result.

According to the forumrules you have to add tags # around your code.

Please take your time to read the forumrules.

Forummembers are not allowed to help you, if you are not act due the forumrules.

[BartNL]

Thank you, I will try to make an Excel file with the problem!

[BartNL]

So, I've made a quick Excell sheet, and I hope that makes something clear.

And this is the code I have:

Please Login or Register  to view this content.

[AB33]

This code gives you a good start. You can built-up more conditions.

Please Login or Register  to view this content.

[BartNL]

Thank you! This works well. But I have another problem according to this.

Besides texts as wheels, trunk, etc. I also have checkboxes with an outcome with ones and zeros (checked and un-checked). But these ones and zeros don't have the same characteristics.

For example: checkbox for head lights on: checked --> 1, unchecked --> 0.
Horn on or off: checked --> 1, unchecked --> 0.

But both these 1's and 0's have different calculations. I hope you know what I mean.

So with the texts wheels trunk etc. this code works fine.
But if I say:

Please Login or Register  to view this content.

Then he does this for all the ones. But it is possible that one 1 is not the other 1.

Does this makes any sense?

Regards,
Bart

[AB33]

Bart,
No, you need to show it as an attachment.

[BartNL]

Fair enough, I didn't had a lot of spare time yesterday. But I've made an example for what I mean.

In the Excel file, you can see the options: "Lights on" and "horn". Both of them are working with a checkbox and will give a quantity of 1 or 0 (on and off). I know that it can be done with names, as described before, but in my original excell file, this is not really possible.

I've adjusted your code a little.

But now the problem: The 1 or 0 from the checkbox "lights on" has to have a different calculation behind it, than the 1 or 0 from the checkbox "horn".
With your code, as it is, the 1 and 0, no matter where it is used, will give the same "properties".

I hope you now know what I mean?
If this is really hard to do, I have to find something else, to specify that the 1 and zero for "lights on" are always referring to "lights on" and not to the "horn".

Thanks again!

And a good morning to you.

Bart

[AB33]

Bart,
You said you have adjusted my code, yet, I do not see any code in the attached. Please include the code with the attached and show me what the code is doing and what supposed to do as I am not sure I understand your question.

[BartNL]

Bart,
You said you have adjusted my code, yet, I do not see any code in the attached. Please include the code with the attached and show me what the code is doing and what supposed to do as I am not sure I understand your question.

I'm sorry for the misunderstanding, I saved the file as an Excell file, not as a Macro enabled file.

I hope it is working now!

Thanks again.

Bart

[AB33]

Bart,
If you are saving it in excel 2003, you need to include xlsm after the name of the code. If you are using excel 2007 and beyond, you save it as macro enabled.

[BartNL]

Sorry again. Pfoe..

I'm hoping this one will do what I (and you) want...

Bart

[AB33]

Bart,
Okay!
I can now see the code. If you want to test column "C", you need to loop through it. The code is returning the correct value for column D. What is the issue? What the code is not doing what is supposed to do? If I untick and tick the boxes in column C, column D values change from 1 to 0 and viceversal.

[BartNL]

Hi AB33, goodmorning (for me it's morning),

I'm going to work with the "Call" function. So in short:

Please Login or Register  to view this content.

This works finem because Tire is specific for the choice made in the drop down. Because, if Range("D"& i) is not tire, nothing will happen.

But now, I'm also working with ones and zeros. This gives a problem.

If you see my excel file, both the checkboxes for "Lights on" and "horn" can give a 1 and a 0.

For the lights on:

Please Login or Register  to view this content.

For the horn:

Please Login or Register  to view this content.

But this doesn't make a distinguish between the 1 for the light and the 1 for the horn.

If both checkboxes are checked, both will have the outcome of 7. If both checkboxes are unchecked, both have the outcome of 8.

I can solve this problem by giving each choice (1 & 0 for lights and 1 & 0 for horn, so 4 choices), a specific name.
This is possible, but this will give a lot more work, but not impossible.

Maybe that you have a different solution. (if you understand what I mean)

Thanks again!

Bart