Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells.Find: Why can't I pass a variable? | Excel Discussion (Misc queries) | |||
Pass The Formulas Please | Excel Discussion (Misc queries) | |||
How do I my find pass word in excel | Setting up and Configuration of Excel | |||
pass fail | New Users to Excel | |||
How to by-pass password ? | Excel Programming |