Hi everyone. I've been at this for about 4 hours, using all kinds of
suggestions I've been able to find in this excellent group, but for some
reason my situation is either unique or I'm doing something wrong.

Let me say what I'm trying to do first, in case my whole approach could be
done a lot easier than what I've tried (I often spend hours writing 3 pages
of VBA to then find out I could have done it with 4 lines).

I have a worksheet (Stock) with stock item numbers in column A and the
number of each available in column D. Example:

A B C D
TK01 10
TK98 5
TZ87 14

In another workbook (a whole different xls file in a whole different
directory, called Inventory.xls) I have a Worksheet called Count. Every
time we fill orders we may decide to ship a similar item to one that was
ordered. So, the substituted item must be subtracted from inventory, and
the item that was substituted FOR must be added back into inventory. Each
day the Count worksheet is updated with the overall additions and/or
subtractions to inventory. Example:

A B
TK98 -4
TZ87 3

In this example, we used 4 TK98 as substitutes (so we need to remove, or
subtract, 4 from inventory), and we sent a substituted item instead of TZ87
3 times (so we didn't actually ship those 3 TZ87, so they must be added BACK
into inventory).

I'm trying to use a vlookup in VBA that will ADD the value in Count to the
appropriate cell in Column D of Stock, thus corrcting numbers of each part
on hand. For instance, using the examples from above, after running the
code Stock would now look like this:

A B C D
TK01 10
TK98 1
TZ87 17

(4 was subtracted from TK98's number, and 3 was added to TZ87's number).

Count has all part numbers, with 0's if there were no changes (I did this to
avoid any values not found when doing vlookup).

What I have so far is:

Sub LoopCells()
Dim cell As Range
Dim OrigVal As Integer
Dim SubVal As Integer
Dim PartNum As String
Dim SubsRange As Range
Set SubsRange = Workbooks("C:\Stock.xls").Names("AllSubs").RefersT oRange
' The SubsRange line was an attempt to solve a "subscript out of range"
issue (a suggestion on referring to external ranges that I found on the
newsgroup)

'For Each cell In Worksheets(1).UsedRange.Columns(1).Cells
' the above was commented out while I experimented

For Each cell In Worksheets(1).Range("PartNumRng")
' This is my attempt to simplify using named ranges instead of entire
columns, since the code would continue all the way down the column, even
though used rows ended at around 200

PartNum = cell.Value
OrigVal = cell.Offset(0, 4)
SubVal = Application.WorksheetFunction.VLookup(PartNum, SubsRange, 1,
False)
'The "1" in the above line, the column offset value, might not be exactly
right as compared with my examples above, but let's just assume it's the
right column offset to use (the msgbox below was returning the right values
I wanted to see when I got this thing partially working). Same with the
offset of 4 in the OrigVal line

MsgBox PartNum & " " & OrigVal & " " & SubVal
' I'm using msgbox just to check and see that the variables are coming
through OK

Next
End Sub

When I had the Stock workbook in the same directory (ie. Stock.xls, not
C:\Stock.xls, which is a different directory), it partially worked (would
show me the values in the msgbox I wanted to see, but then would hit a 400
error, which means nothing). Currently, with the workbook in a different
directory, I get subscript out of range errors before anything happens (even
though I used the .RefersToRange suggestion for avoiding the error), and
even though I open the workbook before running the code).

My plan was that once it seemed to be working, I would, for each cell, set
the value of OrigVal to be OrigVal + SubVal. However, this is not working,
or it works if the external book is in the same directory as my current
workbook, but then the sub exits with a 400 error, etc.

I've tried lots of variations on the above, to no avail. I'll spare you
looking through my other attempts.

So can someone tell me how I can accomplish my goal, assuming the Count
sheet is in an external workbook in a different directory (I don't mind
using code to open and hide it while running the updating code, I've done
that with other books and think it's fine if it simplifies things)?

Again, I want to ADD the value in the Count sheet to the appropriate value
in the Stock sheet.

Any help, advice, etc. would be GREATLY appreciated, and thanks for reading.

The VLOOKUP functions is wrong. The 1 paramter is the column where the
results are found. For example,

set SubsRange = Range("A1:C3")
=VLOOKUP("B",SubsRange,2,False)

A B C
Part Sub Part Quanty
A X 8
B Y 6
C Z 5

I can't tell from you posting if SubsRange includes the entire table.
You can also change the Range by doing something like this
set newrange = Range(SubsRange,SubRange.offset(0,2))


VLOOKUP is not very efficient and you may want to open the workbook and then
use the VBA find instead of VLOOKUP.

"CompleteNewb" wrote:

Hi everyone. I've been at this for about 4 hours, using all kinds of
suggestions I've been able to find in this excellent group, but for some
reason my situation is either unique or I'm doing something wrong.

Let me say what I'm trying to do first, in case my whole approach could be
done a lot easier than what I've tried (I often spend hours writing 3 pages
of VBA to then find out I could have done it with 4 lines).

I have a worksheet (Stock) with stock item numbers in column A and the
number of each available in column D. Example:

A B C D
TK01 10
TK98 5
TZ87 14

In another workbook (a whole different xls file in a whole different
directory, called Inventory.xls) I have a Worksheet called Count. Every
time we fill orders we may decide to ship a similar item to one that was
ordered. So, the substituted item must be subtracted from inventory, and
the item that was substituted FOR must be added back into inventory. Each
day the Count worksheet is updated with the overall additions and/or
subtractions to inventory. Example:

A B
TK98 -4
TZ87 3

In this example, we used 4 TK98 as substitutes (so we need to remove, or
subtract, 4 from inventory), and we sent a substituted item instead of TZ87
3 times (so we didn't actually ship those 3 TZ87, so they must be added BACK
into inventory).

I'm trying to use a vlookup in VBA that will ADD the value in Count to the
appropriate cell in Column D of Stock, thus corrcting numbers of each part
on hand. For instance, using the examples from above, after running the
code Stock would now look like this:

A B C D
TK01 10
TK98 1
TZ87 17

(4 was subtracted from TK98's number, and 3 was added to TZ87's number).

Count has all part numbers, with 0's if there were no changes (I did this to
avoid any values not found when doing vlookup).

What I have so far is:

Sub LoopCells()
Dim cell As Range
Dim OrigVal As Integer
Dim SubVal As Integer
Dim PartNum As String
Dim SubsRange As Range
Set SubsRange = Workbooks("C:\Stock.xls").Names("AllSubs").RefersT oRange
' The SubsRange line was an attempt to solve a "subscript out of range"
issue (a suggestion on referring to external ranges that I found on the
newsgroup)

'For Each cell In Worksheets(1).UsedRange.Columns(1).Cells
' the above was commented out while I experimented

For Each cell In Worksheets(1).Range("PartNumRng")
' This is my attempt to simplify using named ranges instead of entire
columns, since the code would continue all the way down the column, even
though used rows ended at around 200

PartNum = cell.Value
OrigVal = cell.Offset(0, 4)
SubVal = Application.WorksheetFunction.VLookup(PartNum, SubsRange, 1,
False)
'The "1" in the above line, the column offset value, might not be exactly
right as compared with my examples above, but let's just assume it's the
right column offset to use (the msgbox below was returning the right values
I wanted to see when I got this thing partially working). Same with the
offset of 4 in the OrigVal line

MsgBox PartNum & " " & OrigVal & " " & SubVal
' I'm using msgbox just to check and see that the variables are coming
through OK

Next
End Sub

When I had the Stock workbook in the same directory (ie. Stock.xls, not
C:\Stock.xls, which is a different directory), it partially worked (would
show me the values in the msgbox I wanted to see, but then would hit a 400
error, which means nothing). Currently, with the workbook in a different
directory, I get subscript out of range errors before anything happens (even
though I used the .RefersToRange suggestion for avoiding the error), and
even though I open the workbook before running the code).

My plan was that once it seemed to be working, I would, for each cell, set
the value of OrigVal to be OrigVal + SubVal. However, this is not working,
or it works if the external book is in the same directory as my current
workbook, but then the sub exits with a 400 error, etc.

I've tried lots of variations on the above, to no avail. I'll spare you
looking through my other attempts.

So can someone tell me how I can accomplish my goal, assuming the Count
sheet is in an external workbook in a different directory (I don't mind
using code to open and hide it while running the updating code, I've done
that with other books and think it's fine if it simplifies things)?

Again, I want to ADD the value in the Count sheet to the appropriate value
in the Stock sheet.

Any help, advice, etc. would be GREATLY appreciated, and thanks for reading.

This line won't work.
Set SubsRange = Workbooks("C:\Stock.xls").Names("AllSubs").RefersT oRange
(You don't include the path in the Workbooks() code)

If Stock.xls is open, you'd use:
Set SubsRange = Workbooks("Stock.xls").Names("AllSubs").RefersToRa nge
(But stock.xls has to be open)

If you can't open the stock.xls workbook, then maybe you could find an empty
cell (a hidden worksheet?), create the formula in code and put that formula in a
cell. Then inspect the value of that cell.

And I wouldn't use application.worksheetfunction.vlookup.

I'd use...

Dim res as variant 'could be an error
res = application.vlookup(PartNum, SubsRange, 2, False)
if iserror(res) then
res = "No match"
end if
msgbox res




CompleteNewb wrote:

Hi everyone. I've been at this for about 4 hours, using all kinds of
suggestions I've been able to find in this excellent group, but for some
reason my situation is either unique or I'm doing something wrong.

Let me say what I'm trying to do first, in case my whole approach could be
done a lot easier than what I've tried (I often spend hours writing 3 pages
of VBA to then find out I could have done it with 4 lines).

I have a worksheet (Stock) with stock item numbers in column A and the
number of each available in column D. Example:

A B C D
TK01 10
TK98 5
TZ87 14

In another workbook (a whole different xls file in a whole different
directory, called Inventory.xls) I have a Worksheet called Count. Every
time we fill orders we may decide to ship a similar item to one that was
ordered. So, the substituted item must be subtracted from inventory, and
the item that was substituted FOR must be added back into inventory. Each
day the Count worksheet is updated with the overall additions and/or
subtractions to inventory. Example:

A B
TK98 -4
TZ87 3

In this example, we used 4 TK98 as substitutes (so we need to remove, or
subtract, 4 from inventory), and we sent a substituted item instead of TZ87
3 times (so we didn't actually ship those 3 TZ87, so they must be added BACK
into inventory).

I'm trying to use a vlookup in VBA that will ADD the value in Count to the
appropriate cell in Column D of Stock, thus corrcting numbers of each part
on hand. For instance, using the examples from above, after running the
code Stock would now look like this:

A B C D
TK01 10
TK98 1
TZ87 17

(4 was subtracted from TK98's number, and 3 was added to TZ87's number).

Count has all part numbers, with 0's if there were no changes (I did this to
avoid any values not found when doing vlookup).

What I have so far is:

Sub LoopCells()
Dim cell As Range
Dim OrigVal As Integer
Dim SubVal As Integer
Dim PartNum As String
Dim SubsRange As Range
Set SubsRange = Workbooks("C:\Stock.xls").Names("AllSubs").RefersT oRange
' The SubsRange line was an attempt to solve a "subscript out of range"
issue (a suggestion on referring to external ranges that I found on the
newsgroup)

'For Each cell In Worksheets(1).UsedRange.Columns(1).Cells
' the above was commented out while I experimented

For Each cell In Worksheets(1).Range("PartNumRng")
' This is my attempt to simplify using named ranges instead of entire
columns, since the code would continue all the way down the column, even
though used rows ended at around 200

PartNum = cell.Value
OrigVal = cell.Offset(0, 4)
SubVal = Application.WorksheetFunction.VLookup(PartNum, SubsRange, 1,
False)
'The "1" in the above line, the column offset value, might not be exactly
right as compared with my examples above, but let's just assume it's the
right column offset to use (the msgbox below was returning the right values
I wanted to see when I got this thing partially working). Same with the
offset of 4 in the OrigVal line

MsgBox PartNum & " " & OrigVal & " " & SubVal
' I'm using msgbox just to check and see that the variables are coming
through OK

Next
End Sub

When I had the Stock workbook in the same directory (ie. Stock.xls, not
C:\Stock.xls, which is a different directory), it partially worked (would
show me the values in the msgbox I wanted to see, but then would hit a 400
error, which means nothing). Currently, with the workbook in a different
directory, I get subscript out of range errors before anything happens (even
though I used the .RefersToRange suggestion for avoiding the error), and
even though I open the workbook before running the code).

My plan was that once it seemed to be working, I would, for each cell, set
the value of OrigVal to be OrigVal + SubVal. However, this is not working,
or it works if the external book is in the same directory as my current
workbook, but then the sub exits with a 400 error, etc.

I've tried lots of variations on the above, to no avail. I'll spare you
looking through my other attempts.

So can someone tell me how I can accomplish my goal, assuming the Count
sheet is in an external workbook in a different directory (I don't mind
using code to open and hide it while running the updating code, I've done
that with other books and think it's fine if it simplifies things)?

Again, I want to ADD the value in the Count sheet to the appropriate value
in the Stock sheet.

Any help, advice, etc. would be GREATLY appreciated, and thanks for reading.

--

Dave Peterson