• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Find cell value from activesheet in another workbook and update workbook using excel vba

I have two workbooks, a journal file which has the column “batch number” and a blank “Qty” columns F and G respectively.   If there is a number in the batch number column it will be unique but, there may also be blanks.  The second file is a csv file with the batch number equivalent in the “Data” column, column D, this column will not contain any blanks.   What I would like to do is step through the csv file looking up each Data value in the journal file, if no exact match was found raise error, if there is an exact match, check the journal file Qty cell on the same row, if it is blank set it to 1, if it is not blank, raise error msg,.
I have a user form that prompts the user to open the journal file and then open the csv file.  I write the path and file name of the journal file and the csv file, and the count of data records in the csv file to the form.  I have the loop to step through the CSV file I just don’t know how to search just the journal file batch number column, how to return the journal file Qty cell and update it if it is blank.
Thanks in advance,
Jim
0
phonepagerfax
Asked:
phonepagerfax
  • 3
  • 2
1 Solution
 
Robberbaron (robr)Commented:
can you post a couple of sample workbooks. say 20 rows in each ?

for raising an error, do you want a popup or something like making the cell red ?
0
 
phonepagerfaxAuthor Commented:
Robberbaron,

Thanks for your help...

A simple msgbox is fine for the error messages.  I'm going to replace it with writing to a log on the user form anyway.

Here is the sample csv file column D, this is a single column, some cells have alot of spaces between values in the cell (this is fine, these cell values are not used anyway);

Data
L100189609B
117049-5734224
117049-5734224
021030         000024
117049-5734224
021030         000024
117049-5734224
0
B
176148000000000
102456
199806         1.0000
219960         1.0000
158Z
180304         000001
180304         000001
180296         1.0000
180296         1.0000
180296         1.0000
179236         000001
179236         000001
9075907

Here is a sample of the journal file, in this sample there are several blank batch # values and all of the qty counted are blank to start;

Batch #      Qty Counted
      
      
      
      
      
111018-0333080      
      
      
      
      
      
      
117049-5734224      
120213-0376905      
120504-0409990      
090814-0027746      
100819-0162509      
120517-0414998      
      
      
      
      
      
120507-0410634      
      
      
101004-0182782      
111027-0333629      
      
      
      
120501-0408391      
176148000000000      
      
      
      
      
      
      
      
      
      
120327-0392758      
090721-0019071      
110601-0277264      
110614-0283144      
110930-0325836      
110930-0325840      
      
120604-0420832      
      
      
      
1103464      
      
      
      
      
120405-0396505      
120503-0406601      
120521-0413140      
      
120611-0420891      
      
      
      
100520-0120578      
110519-0269960      
120307-0386783      
      
      
      
      
      
      
120529-0418786      
120501-0408392      
      
      
      
120514-0413020      
      
      
      
      
      
      
120426-0406602      
120626-0430150      
      
120215-0378265      
9075907
110525-0277951      
      
120412-0395163      
      
120516-0411750      
      
      
      
100816-0161332      
110413-0257584      
120413-0404124      
      
      
      
1035122      
111109-0341866      
      
100514-0126908      
120517-0415003      
100128-0085891      
111115-0344324      
110408-0255417      
120322-0390703      
120524-0414829      
101215-0204019      
      
      
      
110502-0264810      
111228-0359478      
120517-0414999      
      
100127-0085246      
      
      
      
      
      
      
      
      
      
      
101208-0204027      
120306-0386024      
110622-0286354      
120110-0363380      
      
120308-0387241      
      
090707-0012067
0
 
Robberbaron (robr)Commented:
ok.  I have created a test workbook with your data in 2 sheets

check that the data looks about right.

then run the macro.  will generate lots of msgbox popups....
Q-27828375.xls
0
 
phonepagerfaxAuthor Commented:
robberbaron,

This is awesome, I’ve been going through the code to try and understand how it works and I have two questions;

1)Set wbJournal = ActiveWorkbook and a few line later Set wbCSV = ActiveWorkbook  'for testing
Would be modified to ActiveWorkbook(“j.xls”) and ActiveWorkbook(“c.csv”) If these were actually separate workbooks, say “j.xls” and “c.csv”, correct?

2) In the code you set the range of column F and D using the same worksheet.  Set rngJournalData = wsJournal.UsedRange, should the range for column D use Set rngJournalData = wsCSV.UsedRange ?

Thanks for your help,
Jim
0
 
phonepagerfaxAuthor Commented:
robberbaron,

I answered my own questions and the app is working great... Thanks a lot... Jim
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now