Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Error handling in this code
Hello Excel users and Experts,
Excel 2002 SP3 I'm stumped on error handling in this code. If there is no number entered in the Input box I get a type mismatch error message. Have tried "If j is nothing exit sub", and "on error resume next" but it keeps slapping me around! If you enter a number that is not in the list in column A then I don't get the error but want to do a message box saying the number did not exist in Col A. It's kicking my butt on that too! Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for") For Each c In Rng If c.Value = j Then c.Resize(1, 7).Copy Sheets("Sheet2"). _ Range("G100").End(xlUp).Offset(1, 0) End If Next End Sub Thanks for your help. Regards, Howard |
#2
|
|||
|
|||
One way to get a numeric value back is to use application.inputbox. Then you
don't have to do as much validation to check what the user typed. And you could use application.match to see if that number entered matched your range. And if it did match, then you could just copy that cell (after resizing it): Option Explicit Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long Dim res As Variant i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = CLng(Application.InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for", Type:=1)) If j = 0 Then 'user hit cancel Exit Sub '?? End If res = Application.Match(j, Rng, 0) If IsError(res) Then MsgBox "Not found!" Else Rng(res).Resize(1, 7).Copy _ Destination:=Worksheets("Sheet2").Range("G100").En d(xlUp).Offset(1, 0) End If End Sub ========= One difference. Your code loops through all the cells in that Rng variable. If you have duplicates job numbers, then your code will copy both duplicate ranges. The code I suggested just does the first match--and then quits. I'm not sure if you have duplicates -- if you don't, then that difference won't be important. "L. Howard Kittle" wrote: Hello Excel users and Experts, Excel 2002 SP3 I'm stumped on error handling in this code. If there is no number entered in the Input box I get a type mismatch error message. Have tried "If j is nothing exit sub", and "on error resume next" but it keeps slapping me around! If you enter a number that is not in the list in column A then I don't get the error but want to do a message box saying the number did not exist in Col A. It's kicking my butt on that too! Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for") For Each c In Rng If c.Value = j Then c.Resize(1, 7).Copy Sheets("Sheet2"). _ Range("G100").End(xlUp).Offset(1, 0) End If Next End Sub Thanks for your help. Regards, Howard -- Dave Peterson |
#3
|
|||
|
|||
Hi Dave,
Actually there could be more than one number in column A and I want to copy all of those same number's row to the other sheet. So if there is three 1234's then all three would be copied to the other sheet, one below the other. The error stuff seems to be okay. Thanks for the good info and guidance. Thanks, Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel users and Experts, Excel 2002 SP3 I'm stumped on error handling in this code. If there is no number entered in the Input box I get a type mismatch error message. Have tried "If j is nothing exit sub", and "on error resume next" but it keeps slapping me around! If you enter a number that is not in the list in column A then I don't get the error but want to do a message box saying the number did not exist in Col A. It's kicking my butt on that too! Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for") For Each c In Rng If c.Value = j Then c.Resize(1, 7).Copy Sheets("Sheet2"). _ Range("G100").End(xlUp).Offset(1, 0) End If Next End Sub Thanks for your help. Regards, Howard |
#4
|
|||
|
|||
Hi again Dave,
I don't think I fully understood your 'splanation after first and second review. I will muck about and try to incorporate your advice into this code. I'm vauge but have a mild sense about what you are talking about. May need more help in the neart future. This is in response to an "Anthony" post dated 10/7/2005 8:54 AM titled "Macro VB code help" in Excel.misc. He wants all version of the lookup number to be copied to new sheet. Thanks, Howard "L. Howard Kittle" wrote in message ... Hello Excel users and Experts, Excel 2002 SP3 I'm stumped on error handling in this code. If there is no number entered in the Input box I get a type mismatch error message. Have tried "If j is nothing exit sub", and "on error resume next" but it keeps slapping me around! If you enter a number that is not in the list in column A then I don't get the error but want to do a message box saying the number did not exist in Col A. It's kicking my butt on that too! Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for") For Each c In Rng If c.Value = j Then c.Resize(1, 7).Copy Sheets("Sheet2"). _ Range("G100").End(xlUp).Offset(1, 0) End If Next End Sub Thanks for your help. Regards, Howard |
#5
|
|||
|
|||
There's an example in VBA's help how to use Find to search through a range. It
keeps track of the first address that was found and then repeats the find until the found cell addess matches that first address. I would think that that technique would be quicker than looping through the cells in the range. "L. Howard Kittle" wrote: Hi Dave, Actually there could be more than one number in column A and I want to copy all of those same number's row to the other sheet. So if there is three 1234's then all three would be copied to the other sheet, one below the other. The error stuff seems to be okay. Thanks for the good info and guidance. Thanks, Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel users and Experts, Excel 2002 SP3 I'm stumped on error handling in this code. If there is no number entered in the Input box I get a type mismatch error message. Have tried "If j is nothing exit sub", and "on error resume next" but it keeps slapping me around! If you enter a number that is not in the list in column A then I don't get the error but want to do a message box saying the number did not exist in Col A. It's kicking my butt on that too! Sub TransferIt() Dim i As Long Dim Rng As Range Dim c As Range Dim j As Long i = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & i) j = InputBox("Please enter the job" & vbCr & _ "number you wish to" & vbCr & "print a job card for") For Each c In Rng If c.Value = j Then c.Resize(1, 7).Copy Sheets("Sheet2"). _ Range("G100").End(xlUp).Offset(1, 0) End If Next End Sub Thanks for your help. Regards, Howard -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |