finding and replacing a cell

  • Thread starter Thread starter Rod Taylor
  • Start date Start date
R

Rod Taylor

I need some help. I am working on a schedule.I have 14 different sheets and
each sheet is named in two digit format according to the date. I also have a
hidden sheet named INFO

On the Info sheet I have a column with different initials, and the third row
contains the date also in two digit format.
I can get the initials from the Marco being worked on at the time and I
think I can get the date from the sheet name being worked on
So here is the question. How do I change the cell that corresponds to the
row containing the initials and the column with the date on sheet info. the
date will change and from time to time so will the initials so the area does
not have named ranges but I could do this if that is required I just thought
that might be in the wrong direction.
Any help would be appreciated
Thanks Rod
 
Rod,

I assume you have a unique combination of Initials and Date.

Simply set up a macro to loop through the initials column, and if it finds
the right initials check the 3rd column right to see if that's the date.

Assumptions:
On sheet info
Row 1 contains headings
Column A contains initials
Column C contains dates

this code is untested, and therefore may need modification
Sub update_info(Initials, date)
' best practice says you should declare the variable type for the passed
parameters
Sheets("Info").activate
range("a2"). select
Do until activecell="" 'initialise
loop and limit looping until blank cell reached
If activecell.value=initials then 'check current
cell to see if it mathces initials
If activecell.offset(0,2).value=date then 'if so, check two
columns right to see if it matches date
...... do what you need to ...... 'execute your
code
exit do '
suspend loop
end if
end if
activecell.offset(1,0).select 'move one cell
down
loop '
because exit do wasn't executed, loop to the top and start again
End sub

Steve
 
Assumptions:
On sheet info
Row 1 contains headings
Column A contains initials
Column C contains dates
Click to expand...

Thanks Steve I am working on namming many different ranges to accomplish
this and I like what you sent better but the Problems with the Assumptions
is that
ROW 1 is the dates
column1 contains the initales (actually the initiales start one colmn to the
left of the first date and one row down)

there are headers or headings MONDAY through SATURDAY twice two rows up but
I dont think they can be helpful (S,M,T,W,T,F,S,S,M,T,W,T,F,S)

If you have A suggestion with this I would love it.
I was thinking of using the intersect function but I dont know how to find
out which Range I am in once I find the CELL and I am not sure if I can use
a Varable for a Range NAME.
 
Back
Top