Help with VBA Code

[Puppies72]

Hi all,

I know exactly what I want to do but don't have a notion how to put the code together - if anyone can take a look at this I'd be extremely grateful - it's basically meant for a wargame for ships etc to restock with ammo/fuel etc.

In plain English what I need the macro to do:

If !Validator BS7 > 0 AND If !Validator DG7 = ‘@depot’<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
then<o></o>
If !Validator BT7 < !Validator CA7 <o></o>
then <o></o>
if !Ships $Q$6 > 0 add 1 to !Validator BT7 and subtract 1 from !Ships $Q$6 until either !Validator BT7 = !Validator CA7 OR !Ships $Q$6 = 0<o></o>
then <o></o>
If !Validator BU7 < !Validator CB7 <o></o>
then <o></o>
if !Ships $R$6 > 0 add 1 to !Validator BU7 and subtract 1 from !Ships $R$6 until either !Validator BU7 = !Validator CB7 OR !Ships $R$6 = 0<o></o>
then<o></o>
If !Validator BV7 < !Validator CC7 <o></o>
then <o></o>
if !Ships $S$6 > 0 add 1 to !Validator BV7 and subtract 1 from !Ships $S$6 until either !Validator BV7 = !Validator CC7 OR !Ships $S$6 = 0<o></o>
then <o></o>
If !Validator BW7 < !Validator CD7 <o></o>
then <o></o>
if !Ships $T$6 > 0 add 1 to !Validator BW7 and subtract 1 from !Ships $T$6 until either !Validator BWT7 = !Validator CD7 OR !Ships $T$6 = 0<o></o>
then<o></o>
If !Validator BX7 < !Validator CE7 <o></o>
then <o></o>
if !Ships $U$6 > 0 add 1 to !Validator BX7 and subtract 1 from !Ships $U$6 until either !Validator BX7 = !Validator CE7 OR !Ships $U$6 = 0<o></o>
then<o></o>
If !Validator BZ7 < !Validator CG7 <o></o>
then <o></o>
if !Ships $W$6 > 0 add 1 to !Validator BZ7 and subtract 1 from !Ships $W$6 until either !Validator BZ7 = !Validator CG7 OR !Ships $W$6 = 0<o></o>
then <o></o>
start again with !Validator BS8<o></o>
<o> </o>
notes<o></o>
<o> </o>
If !Validator BS7 is 0 or blank OR DG7 does not equal ‘@depot’ then do nothing<o></o>
At any other point where the value in !Ships = 0 OR the value in the two !Validator cells are equal do next<o></o>
The values mention for !Ship are fixed and won't change through the macro loop - i.e. even though the row in validator increments the row in ships never will<o></o>


As always any help greatly appreciated!

 

[kpark91]

Since this is quite repetitive, I will just tell you what code you need and you can implement it.

Worksheets("Ships").Range("B1").Value = 1

This sets B1 of "Ships" worksheet as 1.

Dim a As Integer

This declares "a" as an integer which you can use as a value later on... like

If a = 1 Then
    a = 2
ElseIf a = 2 Then
    a = 3
Else
    a = 1
End If

As you've seen, that is the way you could use a variable.
Variable is invisible during the runtime unless the code 'prints' the variable.
And above is the code in using IF Statements.

If you want to loop: there are 2 types of simple loops (obviously there are other ways of looping like recursion and such but they're not considered 'looping' really but does the same functionality if coded right)
1) For loop
2) Do While ... Loop

I suggest you google VBA help/tutorials and learn the syntax.

 

[Puppies72]

Thanks for the starter - I have looked at some of the online tutorials as well as VBA for Dummies but still can't much further on with this...the reason I want it macro'd is because it is so repetitive...

As with previous posts I'm happy to try to work through this myself but really struggling for some guidance and trying to get this fixed as quickly as possible as I am currently running this check manually

Questions...

Declaring variables as integers - which ones do I need to declare? As you can see for each row there are 6 checks which are made so a total of 18 values are checked/compared - do I need to declare them all?

The very first line is the only IF AND argument but how do I declare the value in !Validator DG7 as it will be text...

 

[kpark91]

Hi, Puppies.

If you want to compare strings between a range and a string you can use

If Range("A1").Value = "Hello" Then
   'Your code
End If

or

If Range("A1").Text = "Hello" Then
    'Your code
End If

This is how you use AND, OR and other operators.

If Range("A1").Value = "Hello" And Range("B1").Value = "My" Then
     'Your code
End If

 

[Puppies72]

Ok thanks for that - will have a crack at it and see how I get on - will be tomorrow am now before I get to look at it again but thank you so far!!

I daresay I will probably need some more direction but want to see how much I can put together

 

[kpark91]

That is no problem.
As long as you try and get something started.
Writing a code from scratch can be hard especially when you're not used to the syntax!

 

[Puppies72]

Hi all,

I posted this last night and kpark91 has given me some tips on what to do but I've been at this since 7 this morning and still can't figure out the basics like what calues I should be declaring if I want to do 6 matches per row and then increment the row when that loop is done.

Can anyone give me at least a starting point where I can try to muddle on from

thanks