Macro help needed

[Corbana]

Hi all

No douubt this will be the first of many posts that i make, moving from excel from 123

What i am trying to do, Ranges reduced to a minimum to keep it simple.

I have 2 sheets:

Sheet 1 has a range called "monday" (a1:b3) which has the following date in it

A2 - jim
A3 - sam
B1 - 7/11/2005

Sheet 2 has the range "data" (A1:B2)

A1 - jim
A2 - sam
B1 - 7
B2 - 0

what i need the macro to do is:

1. ask for date via an input box
2. find that date in sheet 1
3. get name from A1 on sheet 2
4. get number from the right of the name in A1
5. goto the date on sheet 1 and cross ref the name and then add the number as a value
6. loop until the cell is blank (in this case C1 would be the blank cell)

Hope this is explained well enough.

ty in advance

Corbana

 

[GlennUK]

So, basically, you want to put values from sheet2 into sheet1, but you don't want to put formulae in sheet1 to do this.

Sheet1 cell B2 formula would be

=INDEX(Sheet2!$B$1:$B$2,MATCH(A2,Sheet2!$A$1:$A$2,0))

Is it that you only want this data copying to happen on certain dates as specified in row 1 of sheet1?

 

[Corbana]

i am basically trying to have an input sheet for say jim, sam and peter. (there will be a lot more ppl on it when done)

sheet 2 will have new figures entered in it by someone daily ie

jim 3
sam 12
peter 30
etc

I would then like them to press a button and then these numbers are transfered to sheet 1 as numbers so that i can look back over previous dates.

 

[GlennUK]

You'd be better of having the current date in a cell in Sheet2, and refer to that in the macro ( you can format the cell to have whatever format you like ). You could also have a cell to the right of your date cell, to check that it contains a date ( and gives a message otherwise ), and calculate which column in Sheet1 it should be copied in to. This would be a simple MATCH function. It would then just need a simple macro to do the processing.

 

[Corbana]

I've done it this way:

    Dayfind = InputBox("Enter date")
    Sheets("195D").Select
    Range("A1").Select
    Cells.Find(What:=Dayfind, After:=ActiveCell).Activate
    ActiveCell.Offset(1, 0).Select
    Sheets("195 Import").Select
    Range("B2:B19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("195D").Select
    ActiveSheet.Paste

however it keeps giving me an error of

Run-time error '91':

Object variable or With block variable not set

when i debug it highlights the find line of the script.

any ideas where I am going wrong?

**edit** after a bit of playing i can see that it is doing something that I dont want it to, the date that i am entering is converting to mmddyyyy format and the macro will allow me to search future dates but not past dates.

 

[Corbana]

Edited prev post with an update any further assistance is greately appriciated

 

[GlennUK]

You haven't taken my recommendations on board. Which means you've included prompting for the date in your macro, BUT, you aren't checking to see if the Cancel button has been pressed. And you aren't checking to see if the Find fails.

You're assuming the names in sheet "195 Import" are the same names and in the same order as in the sheet "195D". Is that right? You didn't mention it before, in fact you said to cross-ref the name before pasting.

Are you going to keep going with the macro you've got, and try to debug the problems with it?

 

[Corbana]

So, basically, you want to put values from sheet2 into sheet1, but you don't want to put formulae in sheet1 to do this.

Sheet1 cell B2 formula would be

=INDEX(Sheet2!$B$1:$B$2,MATCH(A2,Sheet2!$A$1:$A$2,0))

Is it that you only want this data copying to happen on certain dates as specified in row 1 of sheet1?

sorry if it appears that i havent tried your responces, the macro you gave me in the post is not going to work as I intend to have a range of dates i.e. 1st-31st Jan and 1 form to enter the data on and hopefully a button to get the data across to the correct date.

what i need to do is identify the cell with the date supplied in it and then offset by 1 row and then add the data to it.

 

[GlennUK]

I think I get the picture

It doesn't bother me if you don't take my recommendations ... although I don't think you've thought things through. When I recommended that you put the date for posting into a cell, I was trying to save you some macro code ... but you went for a macro prompt anyway, but didn't do any error checking. I wouldn't program a macro without double checking user reponses, honestly ... you know they'll press Cancel some day, and even mistype the date.

Back to my queries ....

You're assuming the names in sheet "195 Import" are the same names and in the same order as in the sheet "195D". Is that right?

... what's the answer to that one?

 

[Corbana]

i am assuming so yes.

the file i am working on is here if you want to look
http://www.ozgrid.com/forum/attachment.php?attachmentid=9896