- Joined
- Jun 10, 2011
- Messages
- 3
- Reaction score
- 0
Here is my code:
Option Explicit
Dim i As Long
Dim j As Long
Dim x As Long
Dim PrevCalc
Sub Report4()
'Gather information from comparison tab and locate values where value is >100%. If data set contains
'values, we will record that in the report. Because we know i & j, we know where the value is located in
'the comparison tab. Using this information, we can locate the other information we want for the report.
With Application
.ScreenUpdating = False
.EnableEvents = False
PrevCalc = .Calculation
.Calculation = xlCalculationManual
End With
x = 0
For i = 0 To 202
For j = 0 To 58
If IsError(Worksheets("Comparison").Range("C19").Offset(i, j)) Then
Else
If (Worksheets("Comparison").Range("C19").Offset(i, j)) > 1 Then
x = x + 1
Range("percentagechange3").Offset(x - 1, 0) = Worksheets("Comparison").Range("C19:BH220").Offset(i, j).Value 'Value of the % change is reported here
Range("hfmnumber3start").Offset(x - 1, 0) = Worksheets("Comparison").Range("A19").Offset(i, 0) 'since we know the row where the error is, we can look up the HFM # in that row
Range("hfmaccount3start").Offset(x - 1, 0) = Worksheets("comparison").Range("B19").Offset(i, 0) 'since we know the row where the error is, we can look up the HFM account in that row
Range("location3start").Offset(x - 1, 0) = Worksheets("comparison").Range("c15").Offset(0, j) 'since we know the column where the error is, we can look up the entity in that row
End If
End If
Next j
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = PrevCalc
End With
End Sub
How can I change the code to look in worksheets("comparison").Range("A15") and run the code above to only cells that begin with the letters E and I. I am pretty new to VBA and need some help. I'm guessing this will go just after the Else in the code above. Thanks for any help!
Option Explicit
Dim i As Long
Dim j As Long
Dim x As Long
Dim PrevCalc
Sub Report4()
'Gather information from comparison tab and locate values where value is >100%. If data set contains
'values, we will record that in the report. Because we know i & j, we know where the value is located in
'the comparison tab. Using this information, we can locate the other information we want for the report.
With Application
.ScreenUpdating = False
.EnableEvents = False
PrevCalc = .Calculation
.Calculation = xlCalculationManual
End With
x = 0
For i = 0 To 202
For j = 0 To 58
If IsError(Worksheets("Comparison").Range("C19").Offset(i, j)) Then
Else
If (Worksheets("Comparison").Range("C19").Offset(i, j)) > 1 Then
x = x + 1
Range("percentagechange3").Offset(x - 1, 0) = Worksheets("Comparison").Range("C19:BH220").Offset(i, j).Value 'Value of the % change is reported here
Range("hfmnumber3start").Offset(x - 1, 0) = Worksheets("Comparison").Range("A19").Offset(i, 0) 'since we know the row where the error is, we can look up the HFM # in that row
Range("hfmaccount3start").Offset(x - 1, 0) = Worksheets("comparison").Range("B19").Offset(i, 0) 'since we know the row where the error is, we can look up the HFM account in that row
Range("location3start").Offset(x - 1, 0) = Worksheets("comparison").Range("c15").Offset(0, j) 'since we know the column where the error is, we can look up the entity in that row
End If
End If
Next j
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = PrevCalc
End With
End Sub
How can I change the code to look in worksheets("comparison").Range("A15") and run the code above to only cells that begin with the letters E and I. I am pretty new to VBA and need some help. I'm guessing this will go just after the Else in the code above. Thanks for any help!
