cbobox column header string search + cbobox row header string search-> returns intersect string

[Chemaes]

Hi,
I'm an Excel 2010 user. Last time I tried to code "Fortran" was being taught but, a lot of it has come back to me.

Simplified: through vb code I've created a form with cbobox1 for column header selection (Jan, Feb, etc.) and cbobox2 for row header (wk1, wk 2, etc.). Done.
The form also captures values for calculation etc and this is done.

Need held with a function call or something that does the following:

If cbobox1 = Mar And Cbobox2 = Wk4
desire intersect string "F66" be returned, not the location D5 of F66.

Then, I basically want Range("F66").select or turn that returned string into the activecell for data entry for other completed calculations.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Wk 1[/TD]
[TD]F20[/TD]
[TD]F21[/TD]
[TD]F22[/TD]
[TD]F23[/TD]
[/TR]
[TR]
[TD]Wk 2[/TD]
[TD]G20[/TD]
[TD]G21[/TD]
[TD]G22[/TD]
[TD]G23[/TD]
[/TR]
[TR]
[TD]Wk 3[/TD]
[TD]H21[/TD]
[TD]H22[/TD]
[TD]H23[/TD]
[TD]H24[/TD]
[/TR]
[TR]
[TD]Wk 4[/TD]
[TD]F64[/TD]
[TD]F65[/TD]
[TD]F66[/TD]
[TD]F67[/TD]
[/TR]
</tbody>[/TABLE]

I've completed the task using 144 lines of If Then, Else If statements but I know the code can be reduced to 1/10th of this. I'm not being lazy, I've tried a dozen variants of Intersect and Union Match functionality but no luck. My problem is probably a combination of setting the proper Dim of the variables, Calling of the Function, and Return of the function answer.

Please help.

 

[Chemaes]

I solved this with a workaround. Got rid of 144 lines of If Then, Else If statements with 6 lines of code.

The workaround is to use built in Intersect functionality within the workbook, then have vb reference it.

1) For the Table above located somewhere in a worksheet, goto the Formulas Tab of Excel.
2) Using mouse, highlight select top left corner of table (above wk 1, to the left of Jan) down to bottom right corner of table (F67)
3)In Formulas Tab of Excel, in [Defined Names] group, select "create from selection"
4)Ensure "top row" & "left column" are checked, hit ok

Side Note: If you go to "Name Manager" in [Defined Names] of Formulas Tab and select you will see that excel has created range groups for each column and row. ie Jan represents (F20,G20,H21,F64) and Wk 4 represents (F64,F65,F66,F67). If you now goto a cell anywhere in your sheet and type "=Jan Wk4", that cell will show F64 (the string)

Utilizing my cbobox selections:

cbomonth is the name of the drop down month selection. So if you select any month Jan through Dec, it is stored as cbomonth.
cbowk is the name of the drop down wk selection. So if you select any week 1 to week 4, it is stored as cbowk. A trick here is to make sure Week_1 is a selection choice and not Week 1.

Cell "p200" is the cell where I want my initial intersect information placed (later I'll format the fonts in this table to white to make it invisible and lock hide the cell with protect sheet), the code is

Range("p200").Select
ActiveCell = "=" & cbowk & " " & cbomonth 'It's important for syntax that the second set of quotations has a space in it. This code places in cell p200 the intersect of Jan and Week_1 "F64"
Dim Inter As string 'inter is my variable name, you can not use intersect because a function exists in vb named that
Inter = ActiveCell.Value
Range(Inter).Select 'a mistake I kept making here was writing Inter in "", so Range("Inter").Select is wrong. Anyhow, F64 is now the activecell for whatever you want
ActiveCell.value = x 'my form asks for data, calculations are performed = x. i now want x placed in this cell F64.

Viola 144 lines of IF THEN ELSEIF gone with 6 lines of vb and and built in intersect functionality from the workbook.
Chem