function location in table using vlookup help

[kmillanr]

Hey guys,

I'm having difficulties with this code:

Sub Prueba2()


Dim art_name As String
Dim stockref As Integer
Dim sale As Integer
Dim stock As Integer

Set salerange = Range("C:D")

art_name = Range("S4").Value
stockref = Range("T4").Value
sale = Range("U4").Value
stock = Application.WorksheetFunction.VLookup(art_name, salerange, 2, False)

stock = stockref - sale
Range("stock").Value = stock


End Sub

My problem is that I am trying to place the value of "stock" in the corresponding range that is in refrence to art_name. I know that my issue lies here: "Range("stock").Value = stock". I'm guessing that I have to locate the function in my table range.... or something.

 

[mole999]

you appear to be using stock as a range and also an integer, some conflict is occuring

Range("stock").Value this bit needs to be like A1

 

[kmillanr]

the thing is, I want to locate the correct range of "stock" within my dynamic range in table 1 in between columns C (articles) and D (stock column) to locate the corresponding cell for the result to substitute it. I want my macro to substract the values of the amount of stock sold from the specific cell (depending on the article that was sold) in my stock column. some sort of inverse VLOOKUP I think.... its about finding the correct and dynamic range here----> "Range("stock").Value = stock" for the value of stock to substitue it.

 

[mole999]

there is no harm in having stocka and stockb to avoid conflicts

 

[kmillanr]

ok, so if I add stockb, how can I locate "stock" and select the corresponding cell?

I tried this, but it didn't work:
Dim art_name As String
Dim stockref As Integer
Dim sale As Integer
Dim stock As Integer
Dim stockb As Range
Set salerange = Range("C:D")

art_name = Range("S4").Value
stockref = Range("T4").Value
sale = Range("U4").Value
stock = Application.WorksheetFunction.VLookup(art_name, salerange, 2, False)
stockb = stock.range.select
'stock = stockref - sale
'Range("C:D").Value = stock

I noticed that "stock" is a function, not a range, but isn't that function based on a range? shouldn't vba locate as a range, once I specify it to?.