[VBA] Finding the cell based on row and column values

[Trikson]

Hi Guys,

I am currently working on tool for my team that would enable them to add events to their projects, based on date.

Currently I have a following s/s:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project Calendar[/TD]
[TD]2014-12-15[/TD]
[TD]2014-12-16[/TD]
[TD]2014-12-17[/TD]
[TD]2014-12-18[/TD]
[TD]2014-12-19[/TD]
[TD]2014-12-20[/TD]
[TD]2014-12-21[/TD]
[TD]2014-12-22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]project1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]project2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]project3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]project4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]project5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]project6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I also have the userform where user can select the Project Name (combo box ProjectName_Box) from the list of projects, and select the date they want to create the event on (EventDate field auto-populated by clicking on desired date from MonthView calendar).

What I am struggling with is creating code that would pick these two values and find the corresponding cell. I.E. User wants to add new event for Project3 on 19th Dec 2014 so on the userform he selects Project3 from the dropdown list, and clicks on 19th Dec on MonthView calendar which populates the EventDate field with value: 2014-12-19. Now the code should take these two values then locate and select the corresponding cell - F4 in this current scenario.

Thanks in advance for any help.

 

[Trikson]

Sorry for bump, but wanted to give you latest update - which got me totally baffled:

The calendar starts on 2014-10-27 and the macro works great during first week (until 2014-10-31), but if any date after that (i.e. 2014-11-03 which is next monday) is selected - macro returns error 91. It certainly looks like some problem with the cells values but they all look the same. Moreover I tried copying the 2014-10-27 cell into 2014-11-03 one and changing values - still error. Any ideas?

PS. Again, it shouldn't be relevant but I am not 100% sure so: the calendar contains working days only (is Mon-Fri,Mon-Fri,Mon-Fri and so on)

 

[Andrew Poulsom]

I should have suggested:

    With ActiveSheet
        r = Application.Match(ProjectName_Box.Value, .Range("C5:C50"), False)
        c = Application.Match(CLng(DateValue(EventDate.Value)), .Range("D3:LA3"), False)
        .Range("D5:LA50").Cells(r, c).Value = "Chosen"
    End With

 

[Peter_SSs]

All dates, in row 3 are formated as "date", value is YYYY-MM-DD but only the DD-MM is being shown in the cell (i.e. 2014-10-27 looks like 27-10 to the end user).

That doesn't make much sense to me. If they are dates, surely they are formatted as "YYYY-MM-DD" OR "DD-MM", can't be both.

If placed in blank cells on that worksheet, what do these formulas return?

=ISNUMBER(G3)

=TEXT(G3,"0")

 

[Trikson]

=ISNUMBER(G3) returns TRUE

=TEXT(G3;"0") returns 41942

 

[ElBombay]

Trikson,

I noticed this thread and tried out the responses in an effort to improve my own coding skills. I encountered the same errors you’ve been reporting. Since more than one line of my code owes its existence to advice from Peter and Andrew over the years, I’d be willing to bet the problem lies with us.

Being unable to apply the current advice of the MVPs, below is the generic code on which I rely in situations similar to yours. Two issues relevant to applying this to your particular problem:

1) The code assumes the Column headers are in Row 1 and Row labels are in Col A
2) The Error trap probably isn’t needed since your userform controls for input; that's where I set the return values

HTH

Finding the cell based on row and column values
'----------------------------------------------------------------------------
Function zFind_inGrid(cColHdr As String, cRowLabel As String, cRetType As String) As String
'
'Find co-ordinates of a Header/Label (x,y) location.
'Parm 3 will be 'A' to return address, 'V' to return value
'9/8/10 for Hudson2 project
'
Dim bFindLabel As Boolean
Dim iColNum As Integer, iError As Integer, iRowNum As Long
Dim cString As String
On Error GoTo ErrFind_inGrid

    'Search for Column Header
    Cells(1, 1).Activate
    iColNum = Cells.Find(What:=cColHdr, After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Column
    If ActiveCell.Row > 1 Then GoTo ErrFind_inGrid
    
    'Search for Row Label
    bFindLabel = 1          'Cosmetic for error nessage
    Cells(1, 1).Activate
    iRowNum = Cells.Find(What:=cRowLabel, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Row
    Cells(iRowNum, iColNum).Activate
    
ErrFind_inGrid:
    'Prepare error-message clarification
    cString = "Header"
    If bFindLabel Then cString = "Label"
    
    'Store E.Num to mVar for readability
    iError = Err.Number
    If iError = 91 Then
        MsgBox (cString & " NOT FOUND.  Check spelling or Add to DataBase")
        
    ElseIf ActiveCell.Column <> iColNum Then                 'Value not in column
        MsgBox (cString & " NOT found under " & cColHdr)
    
    ElseIf iError = 0 Then              'Value has bee found
        'MsgBox (cValue & " FOUND.")    'Development check-point
        zFind_inGrid = ActiveCell.Address
        If cRetType = "V" Then zFind_inGrid = ActiveCell.Value
    
    Else
        MsgBox ("Unexpected error #" & Err.Number)
        End						‘AbEnd
        
    End If
    On Error GoTo 0
End Function

 

[Andrew Poulsom]

Did you try what I suggested in Post #12?

 

[Rick Rothstein]

I would think this should work...

Private Sub CommandButton1_Click()
  Intersect(Range("C5:C50").Find(ProjectName.Value_Box, , xlValues, xlWhole, , , False, , False).EntireRow, _
    Range("D3:LA3").Find(DateValue(EventDate.Value), , xlValues, xlWhole, , , , , False).EntireColumn) = "Chosen"
End Sub

 

[Peter_SSs]

I would think this should work...

Private Sub CommandButton1_Click()
  Intersect(Range("C5:C50").Find(ProjectName.Value[COLOR="#0000CD"][B]_Box[/B][/COLOR], , xlValues, xlWhole, , , False, , False).EntireRow, _
    Range("D3:LA3").Find(DateValue(EventDate.Value), , xlValues, xlWhole, , , , , False).EntireColumn) = "Chosen"
End Sub

Rick, I think you have the blue bit in the wrong place.
However, even allowing for that the code doesn't work for me - same error as reported in post #8


@Trikson: Further question I should have asked is "Are the dates in row 3 the result of formulas or are they constants?"

 

[ElBombay]

A layman's takeaway from this evolving battle of the Titans:

Andrew's post #12 did work on a table of mine The table used was not burdened with Date-values (notorious for not playing nice) but my adaptation of Rick's offering is generating a 91 error usimg that same table.

Sub test1()
'
'Andrew Poulsom on Mr. Excel. begun12/15/14
'[VBA] Finding the cell based on row and column values
'12/17/14
'
Dim r As Integer
Dim c As Integer
Dim iError As Integer
On Error GoTo ErrTest1

    With ActiveSheet
        r = Application.Match(17, .Range("a2:a257"), False)
        c = Application.Match("CharFunc", .Range("A1:D1"), False)
        .Range("a2:d257").Cells(r, c).Value = "Chosen"
    
    End With
ErrTest1:
    iError = Err.Number
    If iError <> 0 Then
        MsgBox ("Unexpected error #" & Err.Number)
        End
        
    End If
End Sub

Sub test2()
'
'Rick Rothstein

  Intersect(Range("A2:A257").Find(17, , _
    xlValues, xlWhole, , , False, , False).EntireRow, _
    Range("A1:D257").Find("ChrFunc", , xlValues, _
    xlWhole, , , , , False).EntireColumn) = "Chosen"

End Sub

 

[Rick Rothstein]

but my adaptation of Rick's offering is generating a 91 error usimg that same table.

Sub test2()
'
'Rick Rothstein

  Intersect(Range("A2:A257").Find(17, , _
    xlValues, xlWhole, , , False, , False).EntireRow, _
    Range("A1:D257").Find("ChrFunc", , xlValues, _
    xlWhole, , , , , False).EntireColumn) = "Chosen"

End Sub

Where could the text "ChrFunc" be located... anywhere in the table??? If it can only be found in Row 1 (where the headers are), then change the green range to A1:D1 and retest my code (although for a normally constructed table, I would assume the range would be B1:D1 as cell A1 is located over the headers in Column A).