macro help please

[thegooner]

Hi

I need a macro that can do the following. Is it possible?

I have a worksheet with dates running down column A. So, A1 i
12/07/1999, B1 is 13/07/1999 etc.

In a different worksheet (same workbook) I want to type a date int
cell A1, and then a value in B1. (Eg. A1 = 14/07/1999 ; B1 = 15.25 )

I need a macro that will check to see what date I typed in A1 of th
second worksheet, and then input the data I typed in B1 (15.25) int
Column B of the first worksheet, next to the corresponding date.

So:

Worksheet 1 :
A------------------------------B
12/07/1999
13/07/1999
14/07/1999--------------------(macro puts 15.25 here)

Worksheet 2:
A------------------------------B
14/07/1999--------------------15.25 (Button to run macro here)

Can anyone help me with this?

Thanks a lo

 

[MattShoreson]

Why not use a worksheet function on the first sheet?

i.e. vlooku

 

[thegooner]

Once i hit the button to enter the value, the macro will then delete th
date I entered and the value, to allow me to repeat the process.

Therefore, I can't use vlookup !!

Thank

 

[MattShoreson]

seems like overkill to me just type the number in sheet1.

Still if you want to persist....

Use worksheet_change event, type in the number on sheet 2 (store in
variable).
from this you can find the row of the cell (target.cell)

Use cells(target.row, 1) to find date.

Use this application.worksheetfunction.match on sheet 1 referencing
date from above.
Use the row number from match function to insert variable into column
2.

clear contents from sheet2.

 

[thegooner]

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

 

[MattShoreson]

Instead of the range object use cells.

so for instance:

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

 

[thegooner]

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

 

[MattShoreson]

is it the vlookups that arent working?

probably cos you need to quote the search criteria.

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

 

[thegooner]

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.

 

[MattShoreson]

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]

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

 

[thegooner]

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

 

[MattShoreson]

post your grid...


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

F5 2
F6 3
F7 4

 

[thegooner]

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

 

[MattShoreson]

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

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

 

[thegooner]

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