[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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Are the dates in row 3 'real' dates (numbers) or text values?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top