Advance 1 row every... Now with weird problem ARGH!!!!

[yoko]

Hi,

I want a macro that every time its run it will advance 1 row

Something like

' Out side the macro
Dim row1 As Integer
row1 = 36

' The macro its self
Range ("Arow1:Irow1).select
' Format something
row1 = row1 + 1

But this does not work... any ideas?

Cheers!

 

[erik.van.geit]

thanks for the compiment

next post is rather long and will evoluate to something completely different then the code we have now
take your time to experiment and to understand

there was still an inconsistency (not so good for a legend )

    Do While row1 < 150 

        With Range("A" & row1 & ":I" & row1).FormatConditions 
        .Delete 
        .Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y""" 
        .Item(1).Interior.ColorIndex = 4 
        End With 

    row1 = row1 + 1 
    If row1 = 150 Then Exit Do 
    Loop

the line

    If row1 = 150 Then Exit Do

was not needed
I didn't pay attention since I tried to explain how your code could be edited

BUT, back to work!
this code is not the way I would do it
instead of Do ... Loop, which is used mostly when you don't know exactly where it will end, you would better use For ... Next
the variable "i" will loop from "row1" to 149, just what you need

Option Explicit

Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long
Dim i As Long

On Error Resume Next
row1 = Range("P1")
On Error GoTo 0

row1 = Range("P1")
    If row1 < 1 Or row1 > Rows.Count Then
    MsgBox "error message", 48, "ERROR title"
    Exit Sub
    End If

    For i = row1 To 149

        With Range("A" & row1 & ":I" & row1).FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
        .Item(1).Interior.ColorIndex = 4
        End With

    Next i

Range("P1") = row1
Range("A1").Select

    
End Sub

FINALLY all this was just meant to help you understand looping code
but as stated earlier: "do you really need a loop???"

when setting the range at once, you don't need the loop

    With Range("A" & row1 & ":I149").FormatConditions

I didn't test next code, but you see the picture

Option Explicit

Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long

On Error Resume Next
row1 = Range("P1")
On Error GoTo 0

row1 = Range("P1")
    If row1 < 1 Or row1 > 149 Then
    MsgBox "error message", 48, "ERROR title"
    Exit Sub
    End If

    With Range("A" & row1 & ":I149").FormatConditions
    .Delete
    .Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
    .Item(1).Interior.ColorIndex = 4
    End With

Range("P1") = 149
Range("A1").Select
    
End Sub

if this is working for you...
will "149" be changed sometime or never ?

 

[yoko]

The only problem with that is that A1:I149 goes green when “Y” is entered in to I149

What I needed was for each row to work on its own so “Y” in I1 will make A1:I1 green “Y” in I2 will make A2:I2 go green etc etc.

When I did it by manually I found that I couldn’t select a large section like A1:I149 and set a conditional format as it would only react to what happens in I149 rather than each I call if you know what I mean.

I don’t understand how the For and Next statement works but when I copied the code into vba it would only set the conditional formatting to the row number that corresponded with P1 rather than do a row and move on to the next. So 6 in P1 would only conditionally formal row 6.

This is pretty interesting

 

[erik.van.geit]

The only problem with that is that A1:I149 goes green when “Y” is entered in to I149

What I needed was for each row to work on its own so “Y” in I1 will make A1:I1 green “Y” in I2 will make A2:I2 go green etc etc.

When I did it by manually I found that I couldn’t select a large section like A1:I149 and set a conditional format as it would only react to what happens in I149 rather than each I call if you know what I mean.

let's concentrate on this part or your message

you need to delete the "$" again
I didn't pay attention to this detail - as said didn't test

    .Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""

now you would get again the reference problem you had earlier
therefore you need to select the range

        .Select

result

Option Explicit

Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long

On Error Resume Next
row1 = Range("P1")
On Error GoTo 0

row1 = Range("P1")
    If row1 < 1 Or row1 > 149 Then
    MsgBox "error message", 48, "ERROR title"
    Exit Sub
    End If

    With Range("A" & row1 & ":I149")
    .Select
        With .FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""
        .Item(1).Interior.ColorIndex = 4
        End With
    End With
        
Range("P1") = 149
Range("A1").Select
    
End Sub

we are getting closer , I think

EDIT: tested and working for me

 

[yoko]

Ah lovely! What causes row1 to increment with each row then? I understood the loop with it doing row1 = row1 + 1 each time but this is different.

Cheers

 

[erik.van.geit]

can you understand this ?

Sub test()
Dim i As Long

'loop
    For i = 1 To 10
    Range("A" & i) = "loop " & i
    Next i

'no loop
    i = 10
    Range("B1:B" & i) = "no loop"
End Sub

feel free to ask more till you get it

 

[yoko]

I'm assuming I gets 1 added to it each loop until it gets to 10, ok so i get that bit, when is it better to use that rather than the do while statement?

This this would work but I may have written it wrong, I'm sure you get the idea though

dim i as long
do while 1 < 10
range("A" & I) = " loop" & I
i = i + 1
exit do
loop

    For i = 1 To 10 
    Range("A" & i) = "loop " & i 
    Next i

 

[erik.van.geit]

I already wrote:
Do ... Loop is used when you don't know exactly how many times you will need to loop

For next has a clear restriction: "from here to there"

use the helpfiles:
DO ... LOOP, FOR ... NEXT
and also FIND which contains the most used LOOP-code

 

[yoko]

Ah right cool cheers

 

[erik.van.geit]

Ah right cool cheers

you know: you learn me a lot about "how to explain clearly (in english) from first time"

 

[yoko]

haha I thought your english was very good (probably better than mine!)