Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Write data
This procedure is not working. After the procedure has run, I can go to
editfind and it finds my value whats wrong here, please help. It displays the message for error 91. Private Sub CommandButton1_Click() Dim x As Workbook OrderToFind = ActiveSheet.Range("E17").Value TrackingWorkbook = "\\Fs1\Material\Scheduling\OrderTracking.xls" myvalue = ActiveSheet.Range("F15").Value ' Check if workbook is already opened. On Error Resume Next Set x = Workbooks("OrderTracking1") If Err = 0 Then Windows("OrderTracking1").Activate Else Workbooks.Open Filename:=TrackingWorkbook End If Sheets("To Finish").Select 'Find the order numbers comment line Cells.Find(What:=OrderToFind & " Count", LookIn:=xlValues, LookAt:=xlPart).Activate If Err = "91" Then MsgBox "Could not find Order#: " & OrderToFind Exit Sub End If 'Offset to the correct column and insert data ActiveCell.Offset(, 32).Value = myvalue End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Write data
You are best not teo generate errors whenever possible (they have a lot of
overhead associated with them that slows things down) Here is some better code that will avoid the error dim rngFound as range set rngFound = cells.find((What:=OrderToFind & " Count", LookIn:=xlValues, LookAt:=xlPart) if rngFound is nothing then msgbox "Not Found" else rngFound.offset(0,32).value = myvalue endif set rngfound = nothing You will have to paste this into your code as appropriate... HTH "James W." wrote: This procedure is not working. After the procedure has run, I can go to editfind and it finds my value whats wrong here, please help. It displays the message for error 91. Private Sub CommandButton1_Click() Dim x As Workbook OrderToFind = ActiveSheet.Range("E17").Value TrackingWorkbook = "\\Fs1\Material\Scheduling\OrderTracking.xls" myvalue = ActiveSheet.Range("F15").Value ' Check if workbook is already opened. On Error Resume Next Set x = Workbooks("OrderTracking1") If Err = 0 Then Windows("OrderTracking1").Activate Else Workbooks.Open Filename:=TrackingWorkbook End If Sheets("To Finish").Select 'Find the order numbers comment line Cells.Find(What:=OrderToFind & " Count", LookIn:=xlValues, LookAt:=xlPart).Activate If Err = "91" Then MsgBox "Could not find Order#: " & OrderToFind Exit Sub End If 'Offset to the correct column and insert data ActiveCell.Offset(, 32).Value = myvalue End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro:- Find first blank cell and write text | Excel Worksheet Functions | |||
how do I write the formula to find the mean in a column of number | Excel Worksheet Functions | |||
trying to write function to find data in last row in 1 column | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
VBA - Find every occurance of string in workbook, write to 1st she | Excel Programming |