More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Bob Umlas
Expertise
I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...)
Experience
Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."
Publications
Excellence, The Expert, Microsoft
Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Macro
Expert: Bob Umlas - 11/1/2009
Question
Hi
I have been trying to record and execute a macro involving two separate workbooks but am not being successful. I don’t know enough about VB to try to write one on my own. Your assistance is very much appreciated.
I have two workbooks (WB1 & WB2) with only one worksheet in each. Here is what I am trying to accomplish.
Column D of WB1 contains ID numbers.
Find that ID number in column F of WB2.
If the ID number matches, change/update/insert the characters “VAC” in adjacent cells of same row in columns H, I, J, K, and L of WB2.
Return to WB1, move to next cell down in column D of WB1 and repeat steps for finding, matching, and updating until all ID numbers in WB1 are processed.
thanks
Answer
Sub InsertVac()
Dim n as Long
Set WB2=Workbooks("WB2.xls").Sheets(1)
Set WB1=Workbooks("WB1.xls").Sheets(1)
On Error Resume Next
For i=1 to WB2.range("F65536").end(xlup).row
n=0
n=Application.Match(wb2.cells(i,6).value,wb1.range("D:D"),0)
if n<>0 then
for j=8 to 12
wb2.cells(i,j).value = "VAC" & wb2.cells(i,j).value
Next
End If
Next
End Sub
Add to this Answer Ask a Question
|
|
