PDA

View Full Version : Solved: How to perform code on all pages except first one



moose123
06-12-2012, 06:30 AM
Hi guys!

for the first thanks for help in previous topics!

that code which you helped me to create) needs to perform on all pages except first one
Could you help me with this. And I'll try to find it by myself too.

mikerickson
06-12-2012, 06:32 AM
PerhapsDim i As Long

With ThisWorkbook
For i = 2 to .Worksheets.Count
With .Worksheets(i)
rem some code
End With
Next i
End With

CodeNinja
06-12-2012, 06:32 AM
Try something like
for i = 2 to thisworkbook.sheets.count
sheets(i). blah blah blah
next i

CodeNinja
06-12-2012, 06:33 AM
wow mikerickson, beat me by less than a minute :)

Tinbendr
06-12-2012, 07:08 AM
Exclude by Sheet name

Dim i As Long

With ThisWorkbook
For i = 2 To .Worksheets.Count
If Worksheets(i).Name <> "ExcludeName" Then
With .Worksheets(i)
Rem some code
End With
End If
Next i
End With

moose123
06-12-2012, 08:49 AM
Thanks for replay!
Before I wrote here I tried

for i = 2 to sheets.count
with sheets(i)
...
end with
next

and it performs only on active page, others pages without changes

And I tried your code too, the same result

here are my code looks like
if I start macros on the first page there is an error "run-time error 13 type mismatch " if I start it on the other page it's performs only that page and others didn't touch. I attached the file maybe you can look at it


Sub myMacro2()
Dim li As Integer, lLastRow As Integer
Dim maxDate As Date
Dim YY As Long
Dim i As Integer
With ThisWorkbook

lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
maxDate = Application.Max(Cells(2, 4), Cells(lLastRow, 4))
For i = 2 To .Worksheets.Count
With .Worksheets(i)

For li = 2 To lLastRow
YY = (DateValue("31.12." & Year(Cells(li, 4))) - DateValue("01.01." & Year(Cells(li, 4))) + 1)
If CLng(maxDate) - CLng(Cells(li, 3)) > YY Then
Cells(li, 5) = 0
End If
Next li
End With
Next i
End With

End Sub

need your help guys!

moose123
06-12-2012, 09:00 AM
Thanks Tinbendr

I tried and your way too

here is a code


Sub myMacro2()
Dim li As Integer, lLastRow As Integer
Dim maxDate As Date
Dim YY As Long
Dim i As Integer


lLastRow = Cells(Rows.Count, 4).End(xlUp).Row
maxDate = Application.Max(Cells(2, 4), Cells(lLastRow, 4))
With ThisWorkbook
For i = 2 To .Worksheets.Count
If Worksheets(i).Name <> "Отчет" Then
With .Worksheets(i)

For li = 2 To lLastRow
YY = (DateValue("31.12." & Year(Cells(li, 4))) - DateValue("01.01." & Year(Cells(li, 4))) + 1)
If CLng(maxDate) - CLng(Cells(li, 3)) > YY Then
Cells(li, 5) = 0
End If
Next li
End With
End If
Next i
End With

End Sub


the same error and result as above, I think the code is right but I do something wrong, maybe because I had created "module"

CodeNinja
06-12-2012, 09:10 AM
Moose my friend,
Unfortunately I do not read Russian... I assume Russian is your first language, and I am quite impressed you can write code in English... Good for you!
Because of the language barrier, it is difficult to know exactly what is going on, but... I think you are telling vb exactly where you want to get your information.
When you have cells(***) you should state what sheet to get the cells from. IE sheet1.cells(***) or sheets("mySheet").cells(***).
I would start with that... see if it helps.

Good luck.

Tinbendr
06-12-2012, 09:40 AM
You need to move the MaxDate and LastRow inside the loop.

Get used to using fully qualified ranges.

Cells(1,1) only applies to the Active worksheet, where as
Worksheets(1).Cells(1,1)
Worksheets("Sheetname").Cells(1,1)
Worksheets(MyVariable).Cells(1,1)

always apply to the referenced sheet.

Sub myMacro2()
Dim li As Integer, lLastRow As Integer
Dim maxDate As Date
Dim YY As Long
Dim i As Integer

With ThisWorkbook
For i = 2 To .Worksheets.Count
If Worksheets(i).Name <> "Отчет" Then
With .Worksheets(i)
lLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
maxDate = Application.Max(.Cells(2, 4), .Cells(lLastRow, 4))
For li = 2 To lLastRow
YY = (DateValue("31.12." & Year(.Cells(li, 4))) - _
DateValue("01.01." & Year(.Cells(li, 4))) + 1)
If CLng(maxDate) - CLng(.Cells(li, 3)) > YY Then
.Cells(li, 5) = 0
End If
Next li
End With
End If
Next i
End With
End Sub

moose123
06-12-2012, 11:12 AM
Thanks CodeNinja and Tinbendr

Tinbendr your code is perfect for me!

thread is resolved