How do I code this?

[Puppies72]

I have tried to do this myself but even though it is very straightforward in plain English it is way beyond my skill level even with using online tutorials etc. Can someone put this into code please?

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>

 

[Ruddles]

I'll start you off.

[SIZE=3][FONT=Calibri]If !Validator BS7 > 0 [B]AND[/B] If !Validator DG7 = ‘@depot’<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]then<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If !Validator BT7 < !Validator CA7 <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]then <o:p></o:p>[/FONT][/SIZE]

would be coded:-

[FONT=Fixedsys]If thisworkbook.sheets(Validator).range("BS7").value > 0 AND thisworkbook.sheets(Validator).range("DG7").value="@depot" then[/FONT]
[FONT=Fixedsys]    If thisworkbook.sheets(Validator).range("BT7").value < thisworkbook.sheets(Validator).range("CA7").value then <o:p></o:p>[/FONT]

Get the idea?

Can you proceed from there, using my example, until you get stuck again?

 

[Puppies72]

Thanks Ruddles - before I start to get stuck into this do I need to declare any values or anything as once the initial checks have been made I need the entire thing to loop and increment a row

many thanks again

 

[Ruddles]

If you're performing the loop only on rows 7 and 8, you need a variable to hold the row value, then you loop that variable from 7 to 8 - or to whatever other valus you want to end at - then instead of using 7 in your code, you use rowptr. So something like this:-

[FONT=Fixedsys]dim rowptr as long[/FONT]
 
[FONT=Fixedsys]for rowptr=7 to 8[/FONT]
[FONT=Fixedsys]    If thisworkbook.sheets(Validator).range("BS" & rowptr).value > 0 AND thisworkbook.sheets(Validator).range("DG" & rowptr).value="@depot" then
        If thisworkbook.sheets(Validator).range("BT" & rowptr).value < thisworkbook.sheets(Validator).range("CA" & rowptr).value then[/FONT]
[FONT=Fixedsys]..[/FONT]
[FONT=Fixedsys]etc.[/FONT]
[FONT=Fixedsys]next rowptr[/FONT]

 

[Puppies72]

Hi again,

I've been looking at bits and bobs of code from other macros I have cobbled together in this workbook - do you think I could use something similar to this as I have to start at row 7 and then loop every row until I get to an empty one

Dim LR As Long, i As Long
 
LR = .Range("BS" & Rows.Count).End(xlUp).Row
    For i = 6 To LR
        With .Range("C" & i)

 

[Ruddles]

That's a good start. Not sure you need the final line though, as this says "all the following code will refer to this range".

Also the dots (periods) imply that there has been a With statement before but we can't see one so perhaps:-

Dim LR As Long, i As Long
 
With Thisworkbook.Sheets(1)
  LR = .Range("BS" & .Rows.Count).End(xlUp).Row
      For i = 6 To LR

I'd suggest you start by practising with a small set of data in one column of a test worksheet, writing a loop to step through the rows and writing each value to the Immediate window (Ctrl-G) with the Debug.Print statement.

Become comfortable with the way the loop is set up and controlled, how to use the value of the loop variable (i in your example) to 'point to' the cells you're interested in and how to use the Range, Cells and Rows functions to select different parts of the worksheet.

Learning VBA can't be done overnight, but neither is it horrendously complex if you start with the simple stuff and build on what you know to get to the really tricky stuff.

And this board is always here to help when you get stuck!

 

[Puppies72]

I'd be well and truly buggered if it wasn't for the wonderful people on this site )

Thank you so much