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

	A	B	C	D	E	F	G	H	I
1	Project Calendar	2014-12-15	2014-12-16	2014-12-17	2014-12-18	2014-12-19	2014-12-20	2014-12-21	2014-12-22
2	project1
3	project2
4	project3
5	project4
6	project5
7	project6

<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.

 

[Trikson]

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

They are constants (values)

 

[Peter_SSs]

They are constants (values)

Thanks.

Finding dates can be tricky as it depends partly on what your system date format is. Give this one a try.

Private Sub CreateEvent_Click()
  Dim r As Long, c As Long
  Dim sDate As String
  
  sDate = Format(DateSerial(Left(EventDate.Value, 4), Mid(EventDate.Value, 6, 2), Right(EventDate.Value, 2)), "Short Date")
  r = Range("C5:C50").Find(What:=ProjectName_Box.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Row
  c = Range("D3:AL3").Find(What:=CDate(sDate), LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False).Column
  Cells(r, c).Value = "Chosen"
End Sub

BTW, did you try Andrew's modified Match suggestion too?

 

[ElBombay]

Rick,

Thanks for the feedback on my tangent.

Unlike the original question from trikson, which used project ID as unique labels for each row, I usually set up my grids as classic database table with the first column (field) containing a record number or other index-type value and the first row of column headers as field names.

This particular table has the 256 ASCII values in column A (code), then AltKey, CharFunc and Desc for each ‘record’, 0 through 255. The descriptions (unneeded for letters and numbers of course) in Column D was copied from on-line charts and Column C is populated with the CHAR() function. The only way I found to fill Column B was to manually hold the alt key and enter the appropriate number.

I changed the ‘green highlight’ as directed and even switched the lookup value from 17 (“Device control 1”, a left-pointing solid triangle) to the more pedestrian 65 (“A”) but still get a ‘91’ error. The "result range" is actually B2:D257 but I don't want to mangle the code further. I also tried fiddling with the code highlighted in red ("A2:D257") throuh a few variations but returned it to the state I’m providing here. I appreciate the attention.

Sub test2()
'
'Rick Rothstein
'
  Intersect(Range("A2:D257").Find(65, , _
    xlValues, xlWhole, , , False, , False).EntireRow, _
    Range("A1:D1").Find("ChrFunc", , xlValues, _
    xlWhole, , , , , False).EntireColumn) = "Chosen"

End Sub

A table, rather than code, and not aligned but:

VALUE	AltKey	CharFunc	DESC
0			null
1	☺	
	start of heading
2	☻	        start of text
3	♥		end of text
4	♦		end of transmission
5	♣		enquiry
6	♠		acknowledge
7	•		bell
8	◘		backspace
9	○	        horizontal tab
10	◙	        new line
11	♂		vertical tab
12	♀		new page
13	♪	        carriage return
14	♫		shift out
15	☼		shift in
16	►		data link escape
17	◄		device control 1
18	↕		device control 2
19	‼		device control 3
20	¶		device control 4
21	§		negative acknowledge
22			synchronous idle
23	▬		end of trans. block
24	↑		cancel
25	↓		end of medium
26	→		substitute
27	←		escape
28	∟		file separator
29	↔		group separator
30	▲		record separator
31	▼		unit separator
32	 	 	space
33	!	!	exclamation
34	"	"	quotation
35	#	#	hashtag
36	$	$	dollar sign
37	%	%	per cent
38	&	&	ampersand
39		'	single quote
40	(	(	left paren
41	)	)	right paren
42	.*	*	asterisk
43	+	+	plus sign
44	,	,	comma
45	-	-	dash
46	.	.	period
47	  N/P	/	backslash
48	0	0	|
49	1	1	|
50	2	2	|
51	3	3	|
52	4	4	|
53	5	5	|
54	6	6	|
55	7	7	|
56	8	8	|
57	9	9	|
58	:	:	colon
59	;	;	semi-colon
60	<	<	less than
61	=	=	equals
62	>	>	greater than
63	?	?	question mark
64	@	@	at sign
65	A	A	|
66	B	B	|
67	C	C	|
68	D	D	|
69	E	E	|
70	F	F	|
71	G	G	|
72	H	H	|
73	I	I	|
74	J	J	|
75	K	K	|
76	L	L	|
77	M	M	|
78	N	N	|
79	O	O	|
80	P	P	|
81	Q	Q	|
82	R	R	|
83	S	S	|
84	T	T	|
85	U	U	|
86	V	V	|
87	W	W	|
88	X	X	|
89	Y	Y	|
90	Z	Z	|
91	[	[	left bracket
92	\	\	backslash
93	]	]	right bracket
94	^	^	carat
95	_	_	underscore
96	`	`	single quote
97	a	a	|
98	b	b	|
99	c	c	|
100	╝	d	|
101	e.	e	|
102	f	f	|
103	g	g	|
104	h	h	|
105	 	i	|
106	j	j	|
107	k	k	|
108	l	l	|
109	m	m	|
110	n	n	|
111	o	o	|
112	p	p	|
113	q	q	|
114	r	r	|
115	s	s	|
116	t	t	|
117	u	u	|
118	v	v	|
119	w	w	|
120	x	x	|
121	y	y	|
122	z	z	|
123	{	{	left brace
124	|	|	verticle bar
125	}	}	right brace
126	~	~	tilde
127	⌂		
128	Ç	€	
129	ü	�	
130	é	‚	
131	â	ƒ	
132	ä	„	
133	à	…	
134	å	†	
135	ç	‡	
136	ê	ˆ	
137	ë	‰	
138	è	Š	
139	ï	‹	
140	î	Œ	
141	ì	�	
142	Ä	Ž	
143	Å	�	
144	É	�	
145	æ	‘	
146	Æ	’	
147	ô	“	
148	ö	”	
149	ò	•	
150	û	–	
151	ù	—	
152	ÿ	˜	
153	Ö	™	
154	Ü	š	
155	£	›	
156	¥	œ	
157	¥	�	
158	₧	ž	
159	ƒ	Ÿ	
160	á	*	
161	í	¡	
162	>	¢	
163	ú	£	
164	ñ	¤	
165	Ñ	¥	
166	ª	¦	
167	º	§	
168	¿	¨	
169	⌐	©	
170	¬	ª	
171	½	«	
172	¼	¬	
173	¡	*	
174	«	®	
175	»	¯	
176	░	°	
177	▒	±	
178	▓	²	
179	│	³	
180	┤	´	
181	╡	µ	
182	╢	¶	
183	╖	·	
184	╕	¸	
185	╣	¹	
186	║	º	
187	╗	»	
188	╝	¼	
189	╜	½	
190	╛	¾	
191	┐	¿	
192	└	À	
193	┴	Á	
194	┬	Â	
195	├	Ã	
196	─	Ä	
197	┼	Å	
198	╞	Æ	
199	╟	Ç	
200	╚	È	
201	╔	É	
202	╩	Ê	
203	╦	Ë	
204	╠	Ì	
205	═	Í	
206	╬	Î	
207	╧	Ï	
208	╨	Ð	
209	╤	Ñ	
210	╥	Ò	
211	╙	Ó	
212	╘	Ô	
213	╒	Õ	
214	╓	Ö	
215	╫	×	
216	╪	Ø	
217	┘	Ù	
218	┌	Ú	
219	█	Û	
220	▄	Ü	
221	▌	Ý	
222	▐	Þ	
223	▀	ß	
224	α	à	
225	ß	á	
226	Γ	â	
227	π	ã	
228	Σ	ä	
229	σ	å	
230	µ	æ	
231	τ	ç	
232	Φ	è	
233	Θ	é	
234	Ω	ê	
235	δ	ë	
236	∞	ì	
237	φ	í	
238	ε	î	
239	∩	ï	
240	≡	ð	
241	±	ñ	
242	≥	ò	
243	≤	ó	
244	⌠	ô	
245	⌡	õ	
246	÷	ö	
247	≈	÷	
248	°	ø	
249	∙	ù	
250	·	ú	
251	√	û	
252	ⁿ	ü	
253	²	ý	
254	■	þ	
255	N/P	ÿ

 

[Andrew Poulsom]

ChrFunc (code) or CharFunc (table)?

 

[ElBombay]

30 years of coding and I still type as if I'm wearing boxing gloves. Your conceptual help has been outstanding. That just heightens my embarrassment at relying on you for a proof-reading solution that I should have caught myself. I was right when I told Trikson "the fault most likely lies with us" The code just passed tests with ASCII 65 and ASCII 17. Apparently I need error messages that are harsher with me than simply saying "Object variable or With block variable not set"

 

[Trikson]

Thanks.

Finding dates can be tricky as it depends partly on what your system date format is. Give this one a try.


BTW, did you try Andrew's modified Match suggestion too?

Peter: the latest version you provided states "Type Mismatch" error on the date line

sDate = Format(DateSerial(Left(EventDate.Value, 4), Mid(EventDate.Value, 6, 2), Right(EventDate.Value, 2)), "Short Date")

as for Andrew's suggestion - same error' and also date part highlighted

c = Application.Match(CLng(DateValue(EventDate.Value)), .Range("D3:LA3"), False)

I will try to manually retype all dates on Monday - any suggestions regarding the format?

 

[Andrew Poulsom]

Please give an example of what EventDate contains when you get the error.

 

[Trikson]

Here's the code responsible for EventDate

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Me.EventDate = Format(DateClicked, "dd/mm") ' "yyyy" part removed
End Sub

you can see comment regarding YYYY part - I tried it both ways - with and without the year value for EventDate - same error.

and below the values that are generating error:

10-12 , the corresponding date in cell shows 2014-12-10 (10th December)

What is strange though is that for 28-10 cell shows 2014-10-28 (28th October)

This is definitely caused by formatting. I'll look into it more on Monday.

Thanks for your help so far!

 

[Andrew Poulsom]

VBA is US-Centric when it comes to dates. Why can't you use?

Me.EventDate = Format(DateClicked, "yyyy-mm-dd")

which you said you were doing in your original post:

clicks on 19th Dec on MonthView calendar which populates the EventDate field with value: 2014-12-19

 

[Peter_SSs]

My previous suggestion was based on Me.EventDate = Format(DateClicked, "yyyy/mm/dd") from post #8 which is (at least one reason) why it errored. I had forgotten about the change you made to that in post #10.

Like Andrew, I am wondering about the stripping of the year from the date. If you do that, 'EventDate' is just a string containing text representing a month & day, with no year. So when we come to looking in the dates in row 3, we know what month and day to look for but how would we know what year to look for?


As a test, could you change the MonthView1 code back to what it was in post #8 and re-test my code from post #22?