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

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
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:

ABCDEFGHI
1Project Calendar2014-12-152014-12-162014-12-172014-12-182014-12-192014-12-202014-12-212014-12-22
2project1
3project2
4project3
5project4
6project5
7project6

<tbody>
</tbody>

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.
 
Last edited:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does this work for you?

Code:
Private Sub CommandButton1_Click()
    Dim r As Long
    Dim c As Long
    With ActiveSheet
        r = Application.Match(ProjectName_Box.Value, .Range("A1:A7"), False)
        c = Application.Match(CLng(DateValue(EventDate.Value)), .Range("A1:I1"), False)
        .Cells(r, c).Value = "Chosen"
    End With
End Sub
 

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
Hi Andrew,

Thanks for your assistance. I have modified the code to match correct ranges:

Code:
Private Sub CreateEvent_Click()    Dim r As Long
    Dim c As Long
    With ActiveSheet
        r = Application.Match(ProjectName_Box.Value, .Range("C5:C50"), False)
        c = Application.Match(CLng(DateValue(EventDate.Value)), .Range("D3:LA3"), False)
        .Cells(r, c).Value = "Chosen"
    End With


End Sub
Unfortunately the code doesn't seem to work. Sometimes it doesn't make any changes (no error given either unless date/project name is not found within the specified scopes - but that's fine), sometimes it sets the value "chosen" in the wrong cell.

Example of the second scenario:

ABCDEFGHI
1
2OctoberNovember
3Team's2014-10-272014-10-282014-10-292014-10-302014-10-312014-11-3
4CalendarMonTueWedThuFriMon
5project1
6project2
7project3
8project4
9project5
10project6

<tbody>
</tbody>

When selecting project4 and 2014-10-30 code changes cell D4 ("Mon") into "chosen" instead of G8
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need to start Match at row 1 and column 1 because Cells is referring to the ActiveSheet. Or use:

Rich (BB code):
With ActiveSheet.Range("D5:LA50")
 

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
Now it gives me run-time error "13" type mismatch and highlights the following line:

Code:
r = Application.Match(ProjectName_Box.Value, .Range("C5:C50"), False)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,650
Office Version
365
Platform
Windows
Could you take this sort of approach?

Rich (BB code):
Private Sub CreateEvent_Click()
  Dim proj As Range, Dte As Range
  
  Set proj = Range("C5:C50").Find(What:=ProjectName_Box.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
  Set Dte = Range("D3:AL3").Find(What:=DateValue(EventDate.Value), LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
  Cells(proj.Row, Dte.Column).Value = "Chosen"
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,650
Office Version
365
Platform
Windows
Hmm, not really sure why I moved away from r & c.
Rich (BB code):
Private Sub CreateEvent_Click()
  Dim r As Long, c As Long
  
  r = Range("C5:C50").Find(What:=ProjectName_Box.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Row
  c = Range("D3:AL3").Find(What:=DateValue(EventDate.Value), LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False).Column
  Cells(r, c).Value = "Chosen"
End Sub
 

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
Hi Peter,

Your solution gives Run-time error '91' (Object variable or With block variable not set) and the following code line is highlighted by debug:
Code:
c = Range("D3:AL3").Find(What:=DateValue(EventDate.Value), LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False).Column
Don't know if it may be related or not, but the EventDate.Value is created and stored in YYYY/mm/dd format in order to match the date format on the spreadsheet. It is ensured by the following line in this userform code

Code:
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Me.EventDate = Format(DateClicked, "yyyy/mm/dd")
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,650
Office Version
365
Platform
Windows
Are the dates in row 3 'real' dates (numbers) or text values?
 

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
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).

I have changed the format on some to full YYYY-MM-DD and the macro works correctly, but as s/s looses readability that way, is there a way for it to work in the above-described scenario?

I can manage for it to work by changing the Monthview code to

Code:
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Me.EventDate = Format(DateClicked, "mm/dd")
End Sub
but again, the userform looks incomplete when it returns just mm-dd to the end user.

Appreciate any help
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,090,046
Messages
5,412,036
Members
403,409
Latest member
IHRAcer

This Week's Hot Topics

Top