Ok.

In nearly there.

I have now stored the row number in variable "x".

How do I select the cell B"x" ?

Range("Bx") doesn't work, so how?

Thanks


--
thegooner
------------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...o&userid=36618
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Instead of the range object use cells.

so for instance:

datevariable = sheets("Sheet2").cells(rowvariable,1)
valuevariable = sheets("Sheet2").cells(rowvariable,2)


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Thanks for your help

Could you please tell me why this doesn't work (sorry for being such a
noob)

Dim was
Dim site
Dim locality
Dim y
Dim now
Dim testdate As Long
Dim x

' these 2 lines get what column to edit in the last query
' site should return the number 3, locality should return the number 2

site = Application.VLookup(F6, Worksheets("List").Range("B3:C5"), 2,
False)
locality = Application.VLookup(H6, Worksheets("List").Range("E3:F4"),
2, False)

'add the results of the 2 varaibles above to get a number of column
(should be 5)
y = site + locality

' this line returns the value already in that cell
was = Application.VLookup(A6, Worksheets("Data").Range("A:V"), y,
False)

' this line adds the input amount in L6 to the amount already in the
cell
now = was + Range("L6")

testdate = Range("A6")

x = Application.Match(testdate, Worksheets("Data").Range("A:A"), 0)

Worksheets("Data").Cells(x, thevalue) = now


--
thegooner
------------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...o&userid=36618
View this thread: http://www.excelforum.com/showthread...hreadid=563633

is it the vlookups that arent working?

probably cos you need to quote the search criteria.

vlookup("H6",Range,returncol,false)


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Thanks again

It appears to stop at the following line:

y = site + locality

(This is highlighted on debug)

I tried quoting the cell references, but i still get the same error.


--
thegooner
------------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...o&userid=36618
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Type mismatch will usually indicate you are trying to add a text to a
number.

so for example your site vlookup may return "example"
and your locality vlookup could return 7.

7 + example wont work.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

when your code goes into debug.
Show the immediate window with ctrl+g.

Type....
?site<enter

what result do you get?

do the same for locality...

?locality


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Damn this is getting frustrating

I enetered the vlookup directly into the worksheet, took out the Rang
etc and I get numeric values for both:

site = 3
locality = 2

I now have a box of matches to hand, ready to burn my pc. :mad

--
thegoone
-----------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...fo&userid=3661
View this thread: http://www.excelforum.com/showthread.php?threadid=56363

post your grid...


is it something like (top left cell B3)...

F5 2
F6 3
F7 4


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

MattShoreson Wrote:
when your code goes into debug.
Show the immediate window with ctrl+g.

Type....
?site<enter

what result do you get?

do the same for locality...

?locality

These show up as error 2042.
I googled this and it states that it's the equivelnet of #N/A

But if i run it on the sheet with:

=VLookup(F6,List!B3:C5,2,False)

It returns ok

--
thegoone
-----------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...fo&userid=3661
View this thread: http://www.excelforum.com/showthread.php?threadid=56363

For both site and locality, how about this...

Application.VLookup(range("F6"), Worksheets("List").Range("B3:C5"), 2,
False)


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=563633

Excellent.

That worked. Last question, how do I insert the new data into a cell?

The worksheet is called "data" and the cell reference is held under
varaibales ("x" and "the value") and the data to input is held i
varaible "now".

This didn't work:

Worksheets("Data").Cells(x, thevalue) = now

Thanks agai

--
thegoone
-----------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...fo&userid=3661
View this thread: http://www.excelforum.com/showthread.php?threadid=56363