Find a Value Pass Row 99
 

Hello I'm new to VBA and I am having trouble searching for a value Past row 99
Below is the code it works great until I input data that would go into row
100 could someone please assist me.

Thanks.

Sub CheckEXT()
wrkbook = "MEL.xls"
q = RDAT.Cells(7, 1)
sPath = ThisWorkbook.Path
DEVID = RDAT.Cells(13, 1)
FileExists = (Len(Dir(sPath & "\EXTS\" & wrkbook)) 0)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If FileExists = True Then
WbToOpen = (sPath & "\EXTS\" & wrkbook)
Workbooks.Open (WbToOpen)
Set wrksht = ActiveWorkbook.Worksheets("MEL")
With wrksht.Range("B1:B1000")
Set b = .Find(DEVID, Lookat:=xlRows)
If b Is Nothing Then
RDAT.Cells(37, 1) = 0
Else

' This is the Problem Child

RECN = Right(b.Address, 2)

' This is the Problem Child
I need to chang the 2 to 3 when it reaches cells over 99 but I can't figure
out how to make it work


For i = 1 To 11
x = i + 1
RDAT.Cells(x, 5) = wrksht.Cells(RECN, i)
Next i
End If
End With
Workbooks(wrkbook).Close savechanges:=True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Find a Value Pass Row 99
 

you could use an IF statement or Select Case statement to check b.row to see
if it is over 99 and assign a 2 or 3 to a variable to use in your subsequent
code.

Or you could use the CHOOSE function.

Sub test()
MsgBox Right(ActiveCell.Address, _
Application.Choose(Len(CStr(ActiveCell.Row)), 1, 2, 3))
End Sub


If the row is <10 you don't want to return 2 digits do you? Or is the row
never going to be less than 2 digits?

"skid" wrote:

Hello I'm new to VBA and I am having trouble searching for a value Past row 99
Below is the code it works great until I input data that would go into row
100 could someone please assist me.

Thanks.

Sub CheckEXT()
wrkbook = "MEL.xls"
q = RDAT.Cells(7, 1)
sPath = ThisWorkbook.Path
DEVID = RDAT.Cells(13, 1)
FileExists = (Len(Dir(sPath & "\EXTS\" & wrkbook)) 0)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If FileExists = True Then
WbToOpen = (sPath & "\EXTS\" & wrkbook)
Workbooks.Open (WbToOpen)
Set wrksht = ActiveWorkbook.Worksheets("MEL")
With wrksht.Range("B1:B1000")
Set b = .Find(DEVID, Lookat:=xlRows)
If b Is Nothing Then
RDAT.Cells(37, 1) = 0
Else

' This is the Problem Child

RECN = Right(b.Address, 2)

' This is the Problem Child
I need to chang the 2 to 3 when it reaches cells over 99 but I can't figure
out how to make it work


For i = 1 To 11
x = i + 1
RDAT.Cells(x, 5) = wrksht.Cells(RECN, i)
Next i
End If
End With
Workbooks(wrkbook).Close savechanges:=True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Find a Value Pass Row 99
 

Sorry - just not with it. got too caught up with the < 100 (and overlooked
the possibility the rows could go all the way up to 5 digits.

Just use b.row to determine the row number.

"skid" wrote:

Hello I'm new to VBA and I am having trouble searching for a value Past row 99
Below is the code it works great until I input data that would go into row
100 could someone please assist me.

Thanks.

Sub CheckEXT()
wrkbook = "MEL.xls"
q = RDAT.Cells(7, 1)
sPath = ThisWorkbook.Path
DEVID = RDAT.Cells(13, 1)
FileExists = (Len(Dir(sPath & "\EXTS\" & wrkbook)) 0)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If FileExists = True Then
WbToOpen = (sPath & "\EXTS\" & wrkbook)
Workbooks.Open (WbToOpen)
Set wrksht = ActiveWorkbook.Worksheets("MEL")
With wrksht.Range("B1:B1000")
Set b = .Find(DEVID, Lookat:=xlRows)
If b Is Nothing Then
RDAT.Cells(37, 1) = 0
Else

' This is the Problem Child

RECN = Right(b.Address, 2)

' This is the Problem Child
I need to chang the 2 to 3 when it reaches cells over 99 but I can't figure
out how to make it work


For i = 1 To 11
x = i + 1
RDAT.Cells(x, 5) = wrksht.Cells(RECN, i)
Next i
End If
End With
Workbooks(wrkbook).Close savechanges:=True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Find a Value Pass Row 99
 

Thanks JMB Works like charm.

"JMB" wrote:

Sorry - just not with it. got too caught up with the < 100 (and overlooked
the possibility the rows could go all the way up to 5 digits.

Just use b.row to determine the row number.

"skid" wrote:

Hello I'm new to VBA and I am having trouble searching for a value Past row 99
Below is the code it works great until I input data that would go into row
100 could someone please assist me.

Thanks.

Sub CheckEXT()
wrkbook = "MEL.xls"
q = RDAT.Cells(7, 1)
sPath = ThisWorkbook.Path
DEVID = RDAT.Cells(13, 1)
FileExists = (Len(Dir(sPath & "\EXTS\" & wrkbook)) 0)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If FileExists = True Then
WbToOpen = (sPath & "\EXTS\" & wrkbook)
Workbooks.Open (WbToOpen)
Set wrksht = ActiveWorkbook.Worksheets("MEL")
With wrksht.Range("B1:B1000")
Set b = .Find(DEVID, Lookat:=xlRows)
If b Is Nothing Then
RDAT.Cells(37, 1) = 0
Else

' This is the Problem Child

RECN = Right(b.Address, 2)

' This is the Problem Child
I need to chang the 2 to 3 when it reaches cells over 99 but I can't figure
out how to make it work


For i = 1 To 11
x = i + 1
RDAT.Cells(x, 5) = wrksht.Cells(RECN, i)
Next i
End If
End With
Workbooks(wrkbook).Close savechanges:=True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Find a Value Pass Row 99
 

you're welcome.

"skid" wrote:

Thanks JMB Works like charm.

"JMB" wrote:

Sorry - just not with it. got too caught up with the < 100 (and overlooked
the possibility the rows could go all the way up to 5 digits.

Just use b.row to determine the row number.

"skid" wrote:

Hello I'm new to VBA and I am having trouble searching for a value Past row 99
Below is the code it works great until I input data that would go into row
100 could someone please assist me.

Thanks.

Sub CheckEXT()
wrkbook = "MEL.xls"
q = RDAT.Cells(7, 1)
sPath = ThisWorkbook.Path
DEVID = RDAT.Cells(13, 1)
FileExists = (Len(Dir(sPath & "\EXTS\" & wrkbook)) 0)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If FileExists = True Then
WbToOpen = (sPath & "\EXTS\" & wrkbook)
Workbooks.Open (WbToOpen)
Set wrksht = ActiveWorkbook.Worksheets("MEL")
With wrksht.Range("B1:B1000")
Set b = .Find(DEVID, Lookat:=xlRows)
If b Is Nothing Then
RDAT.Cells(37, 1) = 0
Else

' This is the Problem Child

RECN = Right(b.Address, 2)

' This is the Problem Child
I need to chang the 2 to 3 when it reaches cells over 99 but I can't figure
out how to make it work


For i = 1 To 11
x = i + 1
RDAT.Cells(x, 5) = wrksht.Cells(RECN, i)
Next i
End If
End With
Workbooks(wrkbook).Close savechanges:=True
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub