find cell based on text in column and text in row 2

[chilly442]

I have a user form that has two selections and a text box. One selection is Year(row) and the other is Month(col). When the user selects Year and Month and hits submit, I need the text from the box to go to the empty cell specified by the Year and Month. So far all I can do is find an empty cell(not the right one though) only if I tell it where to start.
My layout looks like the following:

jan feb mar april may june july
2005
2006
2007
2008
Can someone get me onto the right path? Maybe even tell me where to go to figure out my problem? Everything I have tryed has not given me the results that I want.

Thanks in advance!!!

 

[AnotherHiggins]

Something along the lines of:

MyYear = 2006
MyMonth = "Apr"
MyRow = Range("A:A").find(what:=MyYear).row
MyCol = Range("1:1").Find(what:=MyMonth).column
Cells(MyRow, MyCol) = UserEntryFromTextBox

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[chilly442]

So instead of MyYear = 2006, and MyMonth = "Apr", would I pass the selected value from the form?

Thank you for getting back to me so quickly! I appreciate the time and thought.

Chilly442

 

[chilly442]

I can't get the code you posted to run. I know I have missed something. Here is the code that I have for the first part. I see that the Range for MyRow are defined as letters, and the MyCol as numbers. Switching them still gives the error.

Private Sub cmdAdd_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data")
Range("C5").Select
MyYear = 2006
MyMonth = "Apr"
MyRow = Range("C:C").Find(what:=MyYear).Row
MyCol = Range("5:5").Find(what:=MyMonth).Column
Cells(MyRow, MyCol) = Me.D5NHR.Value

End Sub

 

[SkipVought]

Try this, assuming that your years are in column C and your months are in row 5...

Private Sub cmdAdd_Click()
  Dim ws As Worksheet, MyYear as integer, MyMonth as string
  Dim MyRow as long, MyCol as integer

  Set ws = Worksheets("Data")

  MyYear = 2006
  MyMonth = "Apr"
  MyRow = ws.Range("C:C").Find(what:=MyYear).Row
  MyCol = ws.Range("5:5").Find(what:=MyMonth).Column
  ws.Cells(MyRow, MyCol) = Me.D5NHR.Value
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chilly442]

Thank you guys for taking the time to help me out with this.
When I run the code as posted above, I get:
Run-time error - "91":
Object variable or With Block variable not set

Then hit the debug button, and it points to this line of code:
MyRow = ws.Range("C:C").Find(what:=MyYear).Row

If I comment this one out, then the debugger points to the next line.

I messed around with it a bit, and am still getting the error.
I thought that With was used to change fonts and colors in cells, can this be used in a situation like this?
If so how?

Thanks
Chilly442

 

[SkipVought]

I just tested my code, and it works exactly as written.

HOWEVER, if your column of years is not in column C, you'll get a Run Time error 91!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

or if you do not have a cell containing the VALUE for year in MyYear.

You might need to do this...

Private Sub cmdAdd_Click()
  Dim ws As Worksheet, MyYear as String, MyMonth as String
  Dim MyRow as [b]range[/b], MyCol as [b]range[/b]

  Set ws = Worksheets("Data")

  MyYear = 2006
  MyMonth = "Apr"[b]
  Set MyRow = ws.Range("C:C").Find(what:=MyYear)
  if not MyRow is nothing then
    Set MyCol = ws.Range("5:5").Find(what:=MyMonth)
    if not MyCol is nothing then[/b]
      ws.Cells(MyRow[b].row[/b], MyCol[b].column[/b]) = Me.D5NHR.Value
    end if
  end if
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chilly442]

Got it!!!
Thank you for all of your help.
I appreciate all the time you have taken to help me.

Problem solved!

Chilly442