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. :banghead:

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 :thumb:thumb

[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.