val() is not work in my code

  • Thread starter Thread starter Mr. GetRight
  • Start date Start date
M

Mr. GetRight

wrote the below code and i'm haveing problems when i get to:
"
StrAStart = CInt(Val(Left(SltArea, StrM)))
StrAEnd = CInt(Val(Right(SltArea, strL - StrM)))
"
part of my code, it looks like it is when i get to Val. When the program
gets to these lines it doesn't put anything for variables StrAStart and
StrAEnd. Varaible SltArea will contain something like "$A$35:$J$43". I am
trying to get the starting, and ending row that was pasted by program. If you
know a way to fix my code to make this work or even a better way to get the
info i need, it would be greatly appreciated.

Private Sub CBAddPending_Click()
Dim PendingSheet As Variant
Set PendingSheet = Worksheets("Pending List")

Dim StartRows As Integer
StartRows = 6

Dim MaxNoRows As Integer
MaxNoRows = 400

Dim CountRow As Integer
CountRow = StartRows

Dim SltArea As String
Dim StrM As Integer
Dim strL As Integer
Dim StrAStart As Integer
Dim StrAEnd As Integer



Do While CountRow < MaxNoRows
If PendingSheet.Range("A" & CountRow) = "" Then

If PendingSheet.Range("a" & CountRow + 1) = 1 Then
CountRow = CountRow + 1
Else
Exit Do
End If

Else
CountRow = CountRow + 1
End If
Loop

PendingSheet.Range("A" & CountRow) = DaySelected
CountRow = CountRow + 1
PendingSheet.Range("A" & CountRow).PasteSpecial

SltArea = (Selection.Address)
StrM = InStr(SltArea, ":")
strL = Len(SltArea)
StrAStart = CInt(Val(Left(SltArea, StrM)))
StrAEnd = CInt(Val(Right(SltArea, strL - StrM)))

Range("A" & StrAStart, "B" & StrAEnd).Delete

Range("A" & StrAStart, "A" & StrAEnd).Cells.NumberFormat = "Number"

CountRow = StrAStart
Do While CountRow > StrAEnd
If Len(PendingSheet.Range("A" & CountRow)) = 12 Then
If Left(PendingSheet.Range("A" & CountRow), 2) = 65 Then
PendingSheet.Range("A" & CountRow) = "'0" &
PendingSheet.Range("A" & CountRow)
Else

End If
Else

End If

CountRow = CountRow + 1
Loop



End Sub
 
Try this:

Dim strRng As String
strRng = Split(SltArea,":",2)
StrAStart = strRng(0)
StrAEnd = strRng(1)

Instead of

StrM = InStr(SltArea, ":")
strL = Len(SltArea)
StrAStart = CInt(Val(Left(SltArea, StrM)))
StrAEnd = CInt(Val(Right(SltArea, strL - StrM)))
 
Back
Top