View Full Version : [SLEEPER:] Looping and combining macros - help needed
Phlow
06-27-2014, 05:28 AM
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
06-27-2014, 12:58 PM
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
06-27-2014, 04:18 PM
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
06-29-2014, 01:16 AM
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
Sub M_snb()
Sheet1.Range("A10").Consolidate Array("Sheet1!R2C1", "Sheet2!R2C1", "Sheet3!R2C1", "Sheet4!R2C1"), xlSum
End Sub
Phlow
06-30-2014, 04:24 AM
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
06-30-2014, 06:05 AM
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
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
06-30-2014, 06:40 AM
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
06-30-2014, 07:07 AM
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,
Did you read and test any of the suggestions that have been made in this thread ?
Simon Lloyd
07-01-2014, 03:31 AM
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
07-07-2014, 03:39 AM
Hey guys
Phlow
07-07-2014, 03:39 AM
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
07-07-2014, 03:41 AM
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
07-07-2014, 04:55 AM
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
07-07-2014, 05:05 AM
A2 always contains a formula. It's value on the other hand is shown as a number or as empty.
Teeroy
07-07-2014, 06:12 AM
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
07-07-2014, 10:48 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.