Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Problems
Hello
Im trying to make a code that will help me find information in a work sheet like a search/find tool. I have got this far but now im stuck because it never find any infomation. I also need to know is there any way to make to it seach in one column instead of the whole work sheet here's my code: Sub BatchLocate() Label1: Dim myRange As Range Dim lastRow As Integer Dim myNum As Long Dim myAnswer As String Dim myCheck As Boolean Dim n As Integer Dim k As Integer Range("D8").Select Set myRange = ActiveCell.CurrentRegion lastRow = myRange.Rows.Count myAnswer = InputBox("Enter Batch Number.") myCheck = IsNumeric(myAnswer) If myAnswer < Empty Then If myCheck = False Then MsgBox " Please enter a valid batch Number" GoTo Label1 Else myNum = CLng(myAnswer) End If Else Range("d2").Select Exit Sub End If Range("d2").Value = myNum n = 8 For k = 1 To lastRow myRange.Cells(k).Select If myRange.Cells(k).Value = myNum Then Range("B" & n).Value = myNum ActiveCell.Offset(0, -2).Copy.ActiveSheet.Cells(4, 2).Select.Paste ActiveCell.Offset(0, 2).Copy.ActiveSheet.Cells(4, 3).Select.Paste ActiveCell.Offset(0, 4).Copy.ActiveSheet.Cells(4, 4).Select.Paste ActiveCell.Offset(0, 5).Copy.ActiveSheet.Cells(4, 5).Select.Paste n = n + 1 End If Next k If n = 8 Then MsgBox "Batch " & myNum & " was not found." Else Range("d2").Select End If End Sub Any help would be great. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Problems
the best thing to do is step through the code and wattch what is happening by
looking at the worksheet. I suspect that the active region may give you problems bexasue it sops a blank rows and columns. 1) Go to VBA a click any where in the macro. 2) Then press F8. 3) go to worksheet and see if code is doing performing the right instructions 4) go back to vbba and press F8 again. You can also press F9 to set a break point and use F5 to run. Look and the differentt option in the VBA menues for more help. "Leon" wrote: Hello Im trying to make a code that will help me find information in a work sheet like a search/find tool. I have got this far but now im stuck because it never find any infomation. I also need to know is there any way to make to it seach in one column instead of the whole work sheet here's my code: Sub BatchLocate() Label1: Dim myRange As Range Dim lastRow As Integer Dim myNum As Long Dim myAnswer As String Dim myCheck As Boolean Dim n As Integer Dim k As Integer Range("D8").Select Set myRange = ActiveCell.CurrentRegion lastRow = myRange.Rows.Count myAnswer = InputBox("Enter Batch Number.") myCheck = IsNumeric(myAnswer) If myAnswer < Empty Then If myCheck = False Then MsgBox " Please enter a valid batch Number" GoTo Label1 Else myNum = CLng(myAnswer) End If Else Range("d2").Select Exit Sub End If Range("d2").Value = myNum n = 8 For k = 1 To lastRow myRange.Cells(k).Select If myRange.Cells(k).Value = myNum Then Range("B" & n).Value = myNum ActiveCell.Offset(0, -2).Copy.ActiveSheet.Cells(4, 2).Select.Paste ActiveCell.Offset(0, 2).Copy.ActiveSheet.Cells(4, 3).Select.Paste ActiveCell.Offset(0, 4).Copy.ActiveSheet.Cells(4, 4).Select.Paste ActiveCell.Offset(0, 5).Copy.ActiveSheet.Cells(4, 5).Select.Paste n = n + 1 End If Next k If n = 8 Then MsgBox "Batch " & myNum & " was not found." Else Range("d2").Select End If End Sub Any help would be great. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code problems | Excel Programming | |||
Problems with code | Excel Programming | |||
sorting code problems | Excel Programming | |||
Problems merging an excel file due to code or file problems? | Excel Programming | |||
VBA Code problems! | Excel Programming |