Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hi Guys,
Working on a piece of code and not getting the desired results. My code is looking in column C for a specific cell with text and giving me a row number, I am using the row number to dynamically set how many pages to make the worksheet print too (i.e small amount of data print to 1 sheet, large amount print to more than 1 sheet)
Currently my Foundcell.Row line returns: 205 and my case statement should have the variable: 3 appear in my messagebox but it keeps showing: 2.
I feel like there may be something wrong with my case greater than or less than statements, any help getting this sorted is appreciated!
Here is my code:
Working on a piece of code and not getting the desired results. My code is looking in column C for a specific cell with text and giving me a row number, I am using the row number to dynamically set how many pages to make the worksheet print too (i.e small amount of data print to 1 sheet, large amount print to more than 1 sheet)
Currently my Foundcell.Row line returns: 205 and my case statement should have the variable: 3 appear in my messagebox but it keeps showing: 2.
I feel like there may be something wrong with my case greater than or less than statements, any help getting this sorted is appreciated!
Here is my code:
Code:
Sub PrintAreaByACT()
Dim lr As Long
Dim FoundCell As Range
Dim ws As Worksheet
Dim cL As Variant, PageCount As Variant 'Finds Column Letter
Dim PageV As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Const Searchtext As String = "Total Expense" 'Finds text "Total Expense" and updates Print Area below that row
Set FoundCell = ws.Range("C:C").Find(What:=Searchtext)
Select Case FoundCell.Row
Case Is <= 60
PageCount = 1
Case Is >= 61 <= 130
PageCount = 2
Case Is >= 131 <= 300
PageCount = 3
Case Is >= 301 <= 450
PageCount = 4
End Select
MsgBox PageCount
end sub
