Looping and combining macros - help needed

[Phlow]

Dear all,

I don't manage to combine 2 macros with a certain condition and I've tried various possibilities: for...next , if...Then, if...GoTo, etc.
I just don't get the trick and combine them the way I want.

Here's what the macros should do:

-read in each sheet of workbook between sheet "Template" and sheet "last" the Cell A2
-Cell A2 should always contain a number or nothing
-number rows according to content from Cell A2 , start with 0 in Cell A5 until value of Cell A2 (-1) is reached
-loop to next sheet and start over again

so far so good

Now, I have the problem that there are a few sheets which don't contain anything in Cell A2 and the macro stops.
I tried to somehow tell Excel to go to next sheet if Cell A2 is empty but couldn't find the trick.

Here's what I have so far:

Sub LoopThroughSheets()
    Dim Template As Integer, Last As Integer, I As Integer
    Template = Sheets("Template").Index
    Last = Sheets("Last").Index
    For I = Template + 1 To Last - 1
         With Sheets(I)
             Call performCreateRows
         End With
         Worksheets(ActiveSheet.Index + 1).Select
        line:
    Next I
End Sub

Sub performCreateRows()
    x = ActiveSheet.Range("A2")
    For j = 1 To x
        Range("A" & j + 4) = j - 1
    Next j
End Sub

Would someone know how I can combine the macros, so that if it doesn't find anything in Cell A2 it would go to next sheet.

Something like:

If Range("A2").Value = "" Then
Next I ?????

Many thanks in advance

[Simon Lloyd]

If you just want to skip the sheet that has nothing in A2 then replace this

For I = Template + 1 To Last - 1
    With Sheets(I)
        Call performCreateRows
    End With
    Worksheets(ActiveSheet.Index + 1).Select 
    line:
Next I 
End Sub

with this

For I = Template + 1 To Last - 1
    With Sheets(I)
        If .Range("A2")=vbnullstring then goto Nxt
            Call performCreateRows 
    End With
    Worksheets(ActiveSheet.Index + 1).Select 
    line:
    Nxt:
Next I 
End Sub

[Bob Phillips]

Sub LoopThroughSheets()
    Dim i As Long
    For i = Sheets("Template").Index + 1 To Sheets("Last").Index - 1
        With Sheets(i)
            With Range("A5")
                If .Value <> "" Then
                    With .Resize(x)
                        .Formula = "=ROW(A1)-1"
                        .Value = .Value
                    End With
                End If
            End With
        End With
        Worksheets(ActiveSheet.Index + 1).Select
    Next i
End Sub

[Teeroy]

Similar to xld's code.

Sub LoopThroughSheets2()
    Dim i As Long
    For i = Sheets("Template").Index + 1 To Sheets("Last").Index - 1
        With Sheets(i)
            If .Range("A2").Value >= 1 Then
                With .Range("A5").Resize(.Range("A2"))
                    .Formula = "=ROW()-5"
                    .Value = .Value
                End With
            End If
        End With
    Next i
End Sub

[snb]

Sub M_snb()
    Sheet1.Range("A10").Consolidate Array("Sheet1!R2C1", "Sheet2!R2C1", "Sheet3!R2C1", "Sheet4!R2C1"), xlSum
End Sub

[Phlow]

Thank you guys!
I've seen the responses just now.
In the meantime I had written the code a bit further and the suggestions of course don't work anymore.
That's the code:

Sub TestMergeMacrosSingleSheets()
    Call performCreateRows
    Call lastrow
    Call LoopThroughSheets
End Sub

Sub LoopThroughSheets()
    Dim Template As Integer, Last As Integer, I As Integer
    Template = Sheets("Template").Index
    Last = Sheets("Last").Index
    For I = Template + 1 To Last - 1
        With Sheets(I)
        If .Range("A2") = vbNullString Then GoTo Nxt
            Call performCreateRows
        End With
        Worksheets(ActiveSheet.Index + 1).Select
        Nxt:
    Next I
End Sub

Sub performCreateRows()
    x = ActiveSheet.Range("A2")
    For j = 1 To x
        Range("A" & j + 4) = j - 1
    Next j
End Sub

Sub lastrow()
    Dim lastrow As Long
    lastrow = ActiveSheet.Range("A5").End(xlDown).Row
    With ActiveSheet.Range("B5:D5")
        .AutoFill Destination:=Range("B5:D" & lastrow&)
    End With
End Sub

It says Run-time error '13'
Type mismatch

The debugger points to the Sub performCreateRows(): For j = 1 To x

This probably means that it is not able to find a value in Cell A2 but if I understood the code from Simon correctly, it shouldn't even look at that sheet if the Cell A2 doesn't contain any value:
If .Range("A2")=vbnullstring Then Goto Nxt


Any idea why it's dropping out there?

Many thanks in advance

[Bob Phillips]

The test for an empty cell A2 in LoopThroughSheets is a test on the sheet being addressed within the loop, whereas the test in performCreateRows is on the activesheet, they are different as the select is done afterwards.

See if this helps.

Sub TestMergeMacrosSingleSheets()
    Call lastrow
    Call LoopThroughSheets
End Sub
 
Sub LoopThroughSheets()
    Dim Template As Long, Last As Long, i As Long, j As Long
    Template = Sheets("Template").Index
    Last = Sheets("Last").Index
    For i = Template + 1 To Last - 1
        With Sheets(i)
            If .Range("A2") <> vbNullString Then
                For j = 1 To .Range("A2").Value
                    .Range("A" & j + 4) = j - 1
                Next j
            End If
        End With
    Next i
End Sub
 
Sub lastrow()
    Dim lastrow As Long
    lastrow = ActiveSheet.Range("A5").End(xlDown).Row
    With ActiveSheet.Range("B5:D5")
        .AutoFill Destination:=Range("B5:D" & lastrow&)
    End With
End Sub

[snb]

Sub M_snb()
  For Each sh In Sheets
    If sh.Cells(2, 1) <> "" Then sh.Cells(5, 1).Resize(sh.Cells(2, 1) + 1) = Evaluate("index(row(1:" & sh.Cells(2, 1) + 1 & ")-1,)")
  Next
End Sub

or

Sub M_snb()
  For Each sh In Sheets
    If val(sh.Cells(2, 1)) <> 0 Then sh.Cells(5, 1).Resize(sh.Cells(2,  1) + 1) = Evaluate("index(row(1:" & sh.Cells(2, 1) + 1 &  ")-1,)")
  Next
End Sub

NB. You can't 'create' rows in Excel.
A worksheet always contains the maximum amount (rows.count)

[Phlow]

Many thanks.

I think my problem is that Cell A2 is a feed from a different application (excel plugIn) and it's never empty. It just appears empty when it can't retrieve data but there's still a formula in the cell.

So, I tried the following but still doesn't work...

If (.Range("A2").Value = vbNullString Or .Range("A2").Value = """") Then GoTo Nxt
            Call performCreateRows

[Phlow]

so, I have the following Codes and they are working alright just when Cell A2 is empty (but still has a formula in it), it doesn’t do what is expected but stops instead.
Do you maybe know which expression I need to use instead of vbNullString ?

Sub TestMergeMacrosSingleSheets() 
    Call LoopThroughSheets 
    Call performCreateRows 
    Call lastrow 
End Sub 
 
Sub LoopThroughSheets() 
    Dim Template As Integer, Last As Integer, I As Integer 
    Template = Sheets("Template").Index 
    Last = Sheets("Last").Index 
    For I = Template + 1 To Last - 1 
        With Sheets(I) 
            If .Range("A2") = vbNullString Then Goto Nxt 
            Call performCreateRows 
        End With 
        Worksheets(ActiveSheet.Index + 1).Select 
        Nxt: 
    Next I 
End Sub 
 
Sub performCreateRows() 
    x = ActiveSheet.Range("A2") 
    For j = 1 To x 
        Range("A" & j + 4) = j - 1 
    Next j 
End Sub 
 
Sub lastrow() 
    Dim lastrow As Long 
    lastrow = ActiveSheet.Range("A5").End(xlDown).Row 
    With ActiveSheet.Range("B5:D5") 
        .AutoFill Destination:=Range("B5:D" & lastrow&) 
    End With 
End Sub

Many thanks and regards,

[snb]

Did you read and test any of the suggestions that have been made in this thread ?

[Simon Lloyd]

vbnullstring means completely empty change it for "" and see how you go, however you've been given some really nice answers here on how to achieve what you want, if you don't understand them that's fine, just ask for a walk through of whoevers solution you choose, they're great guys and will be more than willing to help

[Phlow]

Hey guys

[Phlow]

many thanks for help and answers.
Sorry, I'm an absolute Newbie and therefore I try to implement and test the codes I think I understand.

[Phlow]

SNB: sorry, my bad. I don't have the intention to create rows, I just want to number them. I probably just named the module wrong. And yes, I did test the codes of XLD, Teeroy and Simon.

Any of XLD's and Simon's suggestions delivered the Run-time error I mentioned in the 6th post of this thread:
It says Run-time error '13'
Type mismatch

The debugger points to the Sub performCreateRows(): For j = 1 To x ; so, again Cell A2 is empty (but still has a formula in it), and code stops here.

Terooy's suggestion created Run-time error '1004' Application-defined or object-defined error and debugger pointed to: With .Range("A5").Resize(.Range("A2"))
Didn't understand this error.

SNB's suggestions I didn't understand unfortunately.

I now read something about a function called IsNull which is supposed to give me a true or a false in return.
I tried to implement it but I wasn't that successful yet.

Any suggestions?

[Teeroy]

Does A2 ever contain 0 or a negative number? That would cause the Run-time error '1004' Application-defined or object-defined error from my suggestion.

[Phlow]

A2 always contains a formula. It's value on the other hand is shown as a number or as empty.

[Teeroy]

Shown as empty may be interpreted as a null string. In my previous post try changing the line

If .Range("A2").Value >= 1 Then

to

If Val(.Range("A2").Value) >= 1 Then

[Simon Lloyd]

You cant just stick a variable in front of it and expect it to work. What you need to do now is attach your workbook here, tell us what you would like to happen NOT what you think should happen or how, show us a before and after worksheet so we can actually see what you're expecting it to look like. Leave any code you have in the workbook so we can work out any issues ourselves.