VBA - Find Cell Co-ordinates from a date string

[sh1pley]

Hi

I have two sheets, one called "Diary" and one called "Calendar"

In cell L2 of diary I have a date, say 03/05/2011

I need to take that date and update a value in the "Calendar" sheet with reference to the "05" being the column number in the "Calendar" sheet and the "03" being a match in that column.

So 03 exists in column E of Calendar and I need to overwrite that with reference Sheets("Diary").Range("A1")

Possible?

 

[bertie]

Try this
Open your workbook
Press Alt + F11
Double click the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code
Press F8 to step through the code one line at a time

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Shipley()
   [COLOR=darkblue]Dim[/COLOR] dteDate [COLOR=darkblue]As[/COLOR] Date
   [COLOR=darkblue]Dim[/COLOR] [COLOR=red]iCol [/COLOR][COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] [COLOR=red]iFind[/COLOR] [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR][COLOR=red] rng[/COLOR] [COLOR=darkblue]As[/COLOR] Range
 
   dteDate = Sheets("Diary").Range("L2").Value
  [COLOR=red] iCol[/COLOR] = Month(dteDate)
  [COLOR=red] iFind[/COLOR] = Day(dteDate)
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=green]'search for the value[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Calendar")
      [COLOR=darkblue]Set[/COLOR] [COLOR=red]rng[/COLOR] = .Columns([COLOR=red]iCol[/COLOR]).Find( _
               What:=[COLOR=red]iFind[/COLOR], _
               After:=.Cells(1, [COLOR=red]iCol[/COLOR]), _
               LookIn:=xlValues, _
               LookAt:=xlPart, _
               SearchOrder:=[COLOR=red]xlByColumns[/COLOR], _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False)
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'check the value was found[/COLOR]
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] ([COLOR=red]rng[/COLOR] [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR]) [COLOR=darkblue]Then[/COLOR]
      Sheets("Calendar").Range([COLOR=red]rng.Address[/COLOR]).Value = _
         Sheets("Diary").Range("A1").Value
 
      [COLOR=green]'Formula Reference?[/COLOR]
      [COLOR=green]'Sheets("Calendar").Range(rng.Address).Value = "=Diary!$A$1"[/COLOR]
   [COLOR=darkblue]Else[/COLOR]
      MsgBox "No match Found!"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Norie]

If the column and row are based on the date perhaps something like this would work.

Dim lngCol As Long
Dim lngRow As Long
Dim TheDate As Date
 
TheDate = DateValue("03/05/11")
 
lngCol = Month(TheDate)
 
lngRow = Day(TheDate)
 
Worksheets("Diary").Range("A1").Copy Worksheets("Calendar").Cells(lngRow, lngCol