My VB script runs as long as I don't switch windows by clicking “Alt + Tab”. Why?

Multi tool use
The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.
My question is :
- Is it due to improper use of loops ?
- Is it Due to number of function calls I have used ?
- Or the code itself is inefficient ?
Please let me know if I have missed any relevant info.
Private Sub CommandButton1_Click()
Call Interfac
Call DeleteRowBasedOnCriteria
Call DeleteRowBasedOnCriteria2
GenerateReport Worksheets("Report_Template"), Worksheets("Jira"), Worksheets("Script")
Call Deleterows
Call DefaultData
MsgBox "Report Generation Finished!"
End Sub
Public costcenterswitch As Long
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DeleteRowBasedOnCriteria2()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "All Assignees" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DefaultData()
For Row = 2 To ActiveSheet.UsedRange.Rows.Count
Cells(Row, 1).Formula = 1
Cells(Row, 3).Formula = "SVDO"
Cells(Row, 4).Formula = costcenterswitch
Cells(Row, 5).Formula = "PS_99999"
Cells(Row, 9).Formula = 999
Cells(Row, 10).Formula = "EWH"
Cells(Row, 12).Formula = "H"
Cells(Row, 13).Formula = 0
Cells(Row, 14).Formula = 0
Cells(Row, 2).Formula = Row - 1
Next Row
End Sub
Sub Deleterows()
On Error Resume Next
Columns("K").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Sub Interfac()
Sheets("Script").Select
If IsEmpty(Range("O3").Value) = False Then
costcenterswitch = Range("O3").Value
Else
costcenterswitch = 900214
End If
End Sub
Sub GenerateReport(ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet)
Dim report As Workbook
Set report = Workbooks.Add
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim Row As Long, col As Integer, row3 As Integer, col3 As Integer, runsthirtyonetimes As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
Row = 1
For col = 1 To ws1col
Cells(Row, col).Formula = ws1.Cells(Row, col).Formula
Cells(Row, col).Font.Bold = True
Next col
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 1 To 31
Cells(counter, 7).Formula = ws2.Cells(Row, 2).Formula
Cells(counter, 8).NumberFormat = "yyyy-mm-dd"
Cells(counter, 8).Formula = ws2.Cells(Row, counter).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, counter).Formula
If ws2.Cells(Row, 1).Formula = ws3.Cells(3, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(3, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(4, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(4, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(5, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(5, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(6, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(6, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(7, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(7, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(8, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(8, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(9, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(9, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(10, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(10, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(11, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(11, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(12, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(12, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(13, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(13, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(14, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(14, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(15, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(15, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(16, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(16, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(17, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(17, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(18, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(18, 17).Formula
Else
Cells(counter, 6).Formula = "BAD ID"
Cells(counter, col).Interior.Color = RGB(200, 0, 0)
Cells(counter, col).Font.Bold = True
End If
counter = counter + 1
Next runsthirtyonetimes
Next Row
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 4 To 34
Cells(counter, 8).Formula = ws2.Cells(1, runsthirtyonetimes).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, runsthirtyonetimes).Formula
counter = counter + 1
Next runsthirtyonetimes
Next Row
Columns("A:Z").ColumnWidth = 20
Columns("G:G").ColumnWidth = 60
Rows("1:100").RowHeight = 15
End Sub
excel vba excel-vba
|
show 1 more comment
The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.
My question is :
- Is it due to improper use of loops ?
- Is it Due to number of function calls I have used ?
- Or the code itself is inefficient ?
Please let me know if I have missed any relevant info.
Private Sub CommandButton1_Click()
Call Interfac
Call DeleteRowBasedOnCriteria
Call DeleteRowBasedOnCriteria2
GenerateReport Worksheets("Report_Template"), Worksheets("Jira"), Worksheets("Script")
Call Deleterows
Call DefaultData
MsgBox "Report Generation Finished!"
End Sub
Public costcenterswitch As Long
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DeleteRowBasedOnCriteria2()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "All Assignees" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DefaultData()
For Row = 2 To ActiveSheet.UsedRange.Rows.Count
Cells(Row, 1).Formula = 1
Cells(Row, 3).Formula = "SVDO"
Cells(Row, 4).Formula = costcenterswitch
Cells(Row, 5).Formula = "PS_99999"
Cells(Row, 9).Formula = 999
Cells(Row, 10).Formula = "EWH"
Cells(Row, 12).Formula = "H"
Cells(Row, 13).Formula = 0
Cells(Row, 14).Formula = 0
Cells(Row, 2).Formula = Row - 1
Next Row
End Sub
Sub Deleterows()
On Error Resume Next
Columns("K").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Sub Interfac()
Sheets("Script").Select
If IsEmpty(Range("O3").Value) = False Then
costcenterswitch = Range("O3").Value
Else
costcenterswitch = 900214
End If
End Sub
Sub GenerateReport(ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet)
Dim report As Workbook
Set report = Workbooks.Add
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim Row As Long, col As Integer, row3 As Integer, col3 As Integer, runsthirtyonetimes As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
Row = 1
For col = 1 To ws1col
Cells(Row, col).Formula = ws1.Cells(Row, col).Formula
Cells(Row, col).Font.Bold = True
Next col
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 1 To 31
Cells(counter, 7).Formula = ws2.Cells(Row, 2).Formula
Cells(counter, 8).NumberFormat = "yyyy-mm-dd"
Cells(counter, 8).Formula = ws2.Cells(Row, counter).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, counter).Formula
If ws2.Cells(Row, 1).Formula = ws3.Cells(3, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(3, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(4, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(4, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(5, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(5, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(6, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(6, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(7, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(7, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(8, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(8, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(9, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(9, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(10, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(10, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(11, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(11, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(12, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(12, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(13, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(13, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(14, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(14, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(15, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(15, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(16, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(16, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(17, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(17, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(18, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(18, 17).Formula
Else
Cells(counter, 6).Formula = "BAD ID"
Cells(counter, col).Interior.Color = RGB(200, 0, 0)
Cells(counter, col).Font.Bold = True
End If
counter = counter + 1
Next runsthirtyonetimes
Next Row
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 4 To 34
Cells(counter, 8).Formula = ws2.Cells(1, runsthirtyonetimes).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, runsthirtyonetimes).Formula
counter = counter + 1
Next runsthirtyonetimes
Next Row
Columns("A:Z").ColumnWidth = 20
Columns("G:G").ColumnWidth = 60
Rows("1:100").RowHeight = 15
End Sub
excel vba excel-vba
1
Qualify all your unqualifiedrange
,cells
,columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook therange/cells/columns
belong to.
– chillin
Dec 31 '18 at 12:25
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
One example of an unqualified reference would be in yourDeleteRowBasedOnCriteria
procedure,For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though youSelect
the intended parent worksheet on the line before, the call toCells(Rows...
is in and of itself unqualified. Also I think yourDeleteRowBasedOnCriteria
andDeleteRowBasedOnCriteria2
can be written usingRange.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to putThisworkbook
before all of your worksheets, but you said this is a VBScript?
– chillin
Dec 31 '18 at 12:53
|
show 1 more comment
The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.
My question is :
- Is it due to improper use of loops ?
- Is it Due to number of function calls I have used ?
- Or the code itself is inefficient ?
Please let me know if I have missed any relevant info.
Private Sub CommandButton1_Click()
Call Interfac
Call DeleteRowBasedOnCriteria
Call DeleteRowBasedOnCriteria2
GenerateReport Worksheets("Report_Template"), Worksheets("Jira"), Worksheets("Script")
Call Deleterows
Call DefaultData
MsgBox "Report Generation Finished!"
End Sub
Public costcenterswitch As Long
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DeleteRowBasedOnCriteria2()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "All Assignees" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DefaultData()
For Row = 2 To ActiveSheet.UsedRange.Rows.Count
Cells(Row, 1).Formula = 1
Cells(Row, 3).Formula = "SVDO"
Cells(Row, 4).Formula = costcenterswitch
Cells(Row, 5).Formula = "PS_99999"
Cells(Row, 9).Formula = 999
Cells(Row, 10).Formula = "EWH"
Cells(Row, 12).Formula = "H"
Cells(Row, 13).Formula = 0
Cells(Row, 14).Formula = 0
Cells(Row, 2).Formula = Row - 1
Next Row
End Sub
Sub Deleterows()
On Error Resume Next
Columns("K").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Sub Interfac()
Sheets("Script").Select
If IsEmpty(Range("O3").Value) = False Then
costcenterswitch = Range("O3").Value
Else
costcenterswitch = 900214
End If
End Sub
Sub GenerateReport(ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet)
Dim report As Workbook
Set report = Workbooks.Add
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim Row As Long, col As Integer, row3 As Integer, col3 As Integer, runsthirtyonetimes As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
Row = 1
For col = 1 To ws1col
Cells(Row, col).Formula = ws1.Cells(Row, col).Formula
Cells(Row, col).Font.Bold = True
Next col
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 1 To 31
Cells(counter, 7).Formula = ws2.Cells(Row, 2).Formula
Cells(counter, 8).NumberFormat = "yyyy-mm-dd"
Cells(counter, 8).Formula = ws2.Cells(Row, counter).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, counter).Formula
If ws2.Cells(Row, 1).Formula = ws3.Cells(3, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(3, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(4, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(4, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(5, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(5, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(6, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(6, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(7, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(7, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(8, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(8, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(9, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(9, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(10, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(10, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(11, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(11, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(12, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(12, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(13, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(13, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(14, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(14, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(15, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(15, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(16, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(16, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(17, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(17, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(18, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(18, 17).Formula
Else
Cells(counter, 6).Formula = "BAD ID"
Cells(counter, col).Interior.Color = RGB(200, 0, 0)
Cells(counter, col).Font.Bold = True
End If
counter = counter + 1
Next runsthirtyonetimes
Next Row
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 4 To 34
Cells(counter, 8).Formula = ws2.Cells(1, runsthirtyonetimes).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, runsthirtyonetimes).Formula
counter = counter + 1
Next runsthirtyonetimes
Next Row
Columns("A:Z").ColumnWidth = 20
Columns("G:G").ColumnWidth = 60
Rows("1:100").RowHeight = 15
End Sub
excel vba excel-vba
The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.
My question is :
- Is it due to improper use of loops ?
- Is it Due to number of function calls I have used ?
- Or the code itself is inefficient ?
Please let me know if I have missed any relevant info.
Private Sub CommandButton1_Click()
Call Interfac
Call DeleteRowBasedOnCriteria
Call DeleteRowBasedOnCriteria2
GenerateReport Worksheets("Report_Template"), Worksheets("Jira"), Worksheets("Script")
Call Deleterows
Call DefaultData
MsgBox "Report Generation Finished!"
End Sub
Public costcenterswitch As Long
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DeleteRowBasedOnCriteria2()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "All Assignees" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DefaultData()
For Row = 2 To ActiveSheet.UsedRange.Rows.Count
Cells(Row, 1).Formula = 1
Cells(Row, 3).Formula = "SVDO"
Cells(Row, 4).Formula = costcenterswitch
Cells(Row, 5).Formula = "PS_99999"
Cells(Row, 9).Formula = 999
Cells(Row, 10).Formula = "EWH"
Cells(Row, 12).Formula = "H"
Cells(Row, 13).Formula = 0
Cells(Row, 14).Formula = 0
Cells(Row, 2).Formula = Row - 1
Next Row
End Sub
Sub Deleterows()
On Error Resume Next
Columns("K").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Sub Interfac()
Sheets("Script").Select
If IsEmpty(Range("O3").Value) = False Then
costcenterswitch = Range("O3").Value
Else
costcenterswitch = 900214
End If
End Sub
Sub GenerateReport(ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet)
Dim report As Workbook
Set report = Workbooks.Add
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim Row As Long, col As Integer, row3 As Integer, col3 As Integer, runsthirtyonetimes As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
Row = 1
For col = 1 To ws1col
Cells(Row, col).Formula = ws1.Cells(Row, col).Formula
Cells(Row, col).Font.Bold = True
Next col
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 1 To 31
Cells(counter, 7).Formula = ws2.Cells(Row, 2).Formula
Cells(counter, 8).NumberFormat = "yyyy-mm-dd"
Cells(counter, 8).Formula = ws2.Cells(Row, counter).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, counter).Formula
If ws2.Cells(Row, 1).Formula = ws3.Cells(3, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(3, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(4, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(4, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(5, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(5, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(6, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(6, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(7, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(7, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(8, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(8, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(9, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(9, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(10, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(10, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(11, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(11, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(12, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(12, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(13, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(13, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(14, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(14, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(15, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(15, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(16, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(16, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(17, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(17, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(18, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(18, 17).Formula
Else
Cells(counter, 6).Formula = "BAD ID"
Cells(counter, col).Interior.Color = RGB(200, 0, 0)
Cells(counter, col).Font.Bold = True
End If
counter = counter + 1
Next runsthirtyonetimes
Next Row
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 4 To 34
Cells(counter, 8).Formula = ws2.Cells(1, runsthirtyonetimes).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, runsthirtyonetimes).Formula
counter = counter + 1
Next runsthirtyonetimes
Next Row
Columns("A:Z").ColumnWidth = 20
Columns("G:G").ColumnWidth = 60
Rows("1:100").RowHeight = 15
End Sub
excel vba excel-vba
excel vba excel-vba
edited Jan 7 at 8:54
Pᴇʜ
22.2k42750
22.2k42750
asked Dec 31 '18 at 12:14


Abhishek SinghAbhishek Singh
184
184
1
Qualify all your unqualifiedrange
,cells
,columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook therange/cells/columns
belong to.
– chillin
Dec 31 '18 at 12:25
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
One example of an unqualified reference would be in yourDeleteRowBasedOnCriteria
procedure,For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though youSelect
the intended parent worksheet on the line before, the call toCells(Rows...
is in and of itself unqualified. Also I think yourDeleteRowBasedOnCriteria
andDeleteRowBasedOnCriteria2
can be written usingRange.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to putThisworkbook
before all of your worksheets, but you said this is a VBScript?
– chillin
Dec 31 '18 at 12:53
|
show 1 more comment
1
Qualify all your unqualifiedrange
,cells
,columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook therange/cells/columns
belong to.
– chillin
Dec 31 '18 at 12:25
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
One example of an unqualified reference would be in yourDeleteRowBasedOnCriteria
procedure,For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though youSelect
the intended parent worksheet on the line before, the call toCells(Rows...
is in and of itself unqualified. Also I think yourDeleteRowBasedOnCriteria
andDeleteRowBasedOnCriteria2
can be written usingRange.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to putThisworkbook
before all of your worksheets, but you said this is a VBScript?
– chillin
Dec 31 '18 at 12:53
1
1
Qualify all your unqualified
range
, cells
, columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns
belong to.– chillin
Dec 31 '18 at 12:25
Qualify all your unqualified
range
, cells
, columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns
belong to.– chillin
Dec 31 '18 at 12:25
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
One example of an unqualified reference would be in your
DeleteRowBasedOnCriteria
procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though you Select
the intended parent worksheet on the line before, the call to Cells(Rows...
is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria
and DeleteRowBasedOnCriteria2
can be written using Range.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook
before all of your worksheets, but you said this is a VBScript?– chillin
Dec 31 '18 at 12:53
One example of an unqualified reference would be in your
DeleteRowBasedOnCriteria
procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though you Select
the intended parent worksheet on the line before, the call to Cells(Rows...
is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria
and DeleteRowBasedOnCriteria2
can be written using Range.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook
before all of your worksheets, but you said this is a VBScript?– chillin
Dec 31 '18 at 12:53
|
show 1 more comment
1 Answer
1
active
oldest
votes
If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987404%2fmy-vb-script-runs-as-long-as-i-dont-switch-windows-by-clicking-alt-tab-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
add a comment |
If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
add a comment |
If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
answered Dec 31 '18 at 13:00
Dy.LeeDy.Lee
3,6421510
3,6421510
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987404%2fmy-vb-script-runs-as-long-as-i-dont-switch-windows-by-clicking-alt-tab-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
jg0AeUcqo9OH m hiLqISvcSuUQbpbv DOOXMkJviZ5 5zFbyCoS kmG3RDIbLFIlgT
1
Qualify all your unqualified
range
,cells
,columns
calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook therange/cells/columns
belong to.– chillin
Dec 31 '18 at 12:25
Can you point out one of the unqualified range.cells.columns @chillin ?
– Abhishek Singh
Dec 31 '18 at 12:30
Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1
– QHarr
Dec 31 '18 at 12:39
Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.
– QHarr
Dec 31 '18 at 12:43
One example of an unqualified reference would be in your
DeleteRowBasedOnCriteria
procedure,For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row...
. Even though youSelect
the intended parent worksheet on the line before, the call toCells(Rows...
is in and of itself unqualified. Also I think yourDeleteRowBasedOnCriteria
andDeleteRowBasedOnCriteria2
can be written usingRange.AutoFilter
. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to putThisworkbook
before all of your worksheets, but you said this is a VBScript?– chillin
Dec 31 '18 at 12:53