Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find and Update Macro
I have several macros written to update information in a spreadsheet based on
an entry into a input box. I need to change these macros to instead look at a column of numbers in another file and find each number in my spreadsheet and update. The following is an example of one of the macros: Dim equipNum As String Do equipNum = InputBox("Scan in the Equipment Number. Type Done to Exit") Cells.Find(What:=equipNum, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 3).Select ActiveCell.FormulaR1C1 = "Y" Loop Until equipNum = "Done" Range("A1").Select I am using Excel 2003 and only have a moderate grasp on macros. Any help would be greatly appreciated! Thanks, Holly |
#2
|
|||
|
|||
How about this--but both workbooks need to be open:
Option Explicit Sub testme() Dim myCell As Range Dim myListRng As Range Dim myLookThroughRng As Range Dim FoundCell As Range With Workbooks("book1.xls").Worksheets("sheet1") Set myListRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With Workbooks("book2.xls").Worksheets("sheet99") Set myLookThroughRng = .Range("a:a") End With For Each myCell In myListRng.Cells With myLookThroughRng Set FoundCell = .Cells.Find(what:=myCell.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, lookat:=xlWhole, _ MatchCase:=False, searchdirection:=xlNext) End With If FoundCell Is Nothing Then 'not found MsgBox myCell.Value & " wasn't found in: " _ & myLookThroughRng.Address(external:=True) Else FoundCell.Offset(0, 3).Value = "Y" End If Next myCell End Sub You'll have to adjust the workbook names and worksheet names to match your stuff. hnyb1 wrote: I have several macros written to update information in a spreadsheet based on an entry into a input box. I need to change these macros to instead look at a column of numbers in another file and find each number in my spreadsheet and update. The following is an example of one of the macros: Dim equipNum As String Do equipNum = InputBox("Scan in the Equipment Number. Type Done to Exit") Cells.Find(What:=equipNum, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 3).Select ActiveCell.FormulaR1C1 = "Y" Loop Until equipNum = "Done" Range("A1").Select I am using Excel 2003 and only have a moderate grasp on macros. Any help would be greatly appreciated! Thanks, Holly -- Dave Peterson |
#3
|
|||
|
|||
Thank you! This worked perfectly.
"Dave Peterson" wrote: How about this--but both workbooks need to be open: Option Explicit Sub testme() Dim myCell As Range Dim myListRng As Range Dim myLookThroughRng As Range Dim FoundCell As Range With Workbooks("book1.xls").Worksheets("sheet1") Set myListRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With Workbooks("book2.xls").Worksheets("sheet99") Set myLookThroughRng = .Range("a:a") End With For Each myCell In myListRng.Cells With myLookThroughRng Set FoundCell = .Cells.Find(what:=myCell.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, lookat:=xlWhole, _ MatchCase:=False, searchdirection:=xlNext) End With If FoundCell Is Nothing Then 'not found MsgBox myCell.Value & " wasn't found in: " _ & myLookThroughRng.Address(external:=True) Else FoundCell.Offset(0, 3).Value = "Y" End If Next myCell End Sub You'll have to adjust the workbook names and worksheet names to match your stuff. hnyb1 wrote: I have several macros written to update information in a spreadsheet based on an entry into a input box. I need to change these macros to instead look at a column of numbers in another file and find each number in my spreadsheet and update. The following is an example of one of the macros: Dim equipNum As String Do equipNum = InputBox("Scan in the Equipment Number. Type Done to Exit") Cells.Find(What:=equipNum, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 3).Select ActiveCell.FormulaR1C1 = "Y" Loop Until equipNum = "Done" Range("A1").Select I am using Excel 2003 and only have a moderate grasp on macros. Any help would be greatly appreciated! Thanks, Holly -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Range Mess | Charts and Charting in Excel | |||
Unable to Record Macro | Excel Discussion (Misc queries) | |||
can't update links...can't find links | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
refresh pivot tables through a macro | Excel Worksheet Functions |