From Row and Column to Cell Position

[Tom59593]

Hey guys,

I searched the forum for a while and couldn't find anything related to this, so hopefully someone knows a simple way to accomplish what I'm trying to do. What I have going on in a little application I'm developing for a sports league is this:

1) The user selects a name from a data-validation drop down on a sheet ("Form"). The list of names resides in another sheet ("Database").
2) The user then inputs the results of the match (Wins, Losses, Points) in the sheet ("Form").
3) The user clicks a submit button to add the results to the sheet ("Database").

I can get that far just fine. Now, here's where I need some help...I want the Submit button to:

1) Find the row containing the name the user inputted in ("Database") [I think I could use the MATCH function for this, right?)
2) Add (not replace!) the inputted value for Wins into that row's appropriate Wins column
3) Add (not replace!) the inputted value for Losses into that row's appropriate Losses column
4) Add (not replace!) the inputted value for Points into that row's appropriate Points column

The main problems are in steps 2-4. How do I tell Excel where to add those values? While I know which column is Wins, Losses, Points (columns E, G, H respectively), I don't know how to tell Excel to combine the row it found using MATCH() and the columns to get an exact cell (for example H37) that it needs to update, since the 'H' comes from my own knowledge and the '37' comes from the MATCH function...

Once Excel knows the exact cell to update, I am going to use the following to add the inputted values into the cell:

Dim PointsEarned As Integer
PointsEarned = Worksheets("Form").Range("A1").value
Worksheets("Database").Range("H37").value = Worksheets("Database")Range("H37").value + PointsEarned

Where "A1" is the location of the inputted value that I need to add to "H37".

Hopefully this was clear enough! Let me know if you have any questions!

THANKS FOR YOU HELP!!!

-Tom-

 

[Tom59593]

Hey guys,

So I got an idea, and wanted to know your two cents:

Since I know the columns that need to be updated, and I can find the row that needs to be updated with MATCH(), then I could do something like the following:

'RTU is RowToUpdate
Dim RTU As Integer
RTU = MATCH(Form!B1,Database!C1:C100,0)
 
Dim PointsEarned As Integer
PointsEarned = Worksheets("Form").Range("A1").value
 
'Add PointsEarned to current value in player's Points
Worksheets("Database").Range("H" & RTU).value = Worksheets("Database")Range("H" & RTU).value + PointsEarned

Where Form!A1 is the inputted Points, Form!B1 is the player's name, and Database!C1:C100 is the list of names in the Database,

Right?

THANKS!!!

-Tom-

 

[Peter_SSs]

Tom

I would be using the vba Find method instead of Match. You didn't clarify too well just what you have in what columns/cells in each of the sheets, so I have had a stab at figuring that out. My assumptions are:

'Database' sheet
Column C: Names
Column E: Wins
Column G: Losses
Column H: Points

'Form' sheet
A1: Points
B1: Name chosen from drop-down list based on Columns C of 'Database' sheet.
C1: Wins
D1: Losses

If that looks right, then try this on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsF <SPAN style="color:#00007F">As</SPAN> Worksheet, wsDB <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> RTU <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsF = Worksheets("Form")<br>    <SPAN style="color:#00007F">Set</SPAN> wsDB = Worksheets("Database")<br>    myName = wsF.Range("B1").Value<br>    <SPAN style="color:#00007F">If</SPAN> myName = "" <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "No name entered"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        RTU = wsDB.Columns("C").Find(What:=myName, LookAt:=xlWhole, _<br>            MatchCase:=False, SearchFormat:=False).Row<br>        <SPAN style="color:#00007F">With</SPAN> wsDB<br>            .Cells(RTU, "E").Value = .Cells(RTU, "E").Value + wsF.Range("C1").Value<br>            .Cells(RTU, "G").Value = .Cells(RTU, "G").Value + wsF.Range("D1").Value<br>            .Cells(RTU, "H").Value = .Cells(RTU, "H").Value + wsF.Range("A1").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Tom59593]

Is the main reason behind your method that I don't have to utilize cell values to get the job done. For instance, in the idea I posted that uses MATCH(), I have to set a variable equal to a cell's value. This is different than your approach in that you can do everything within VBA.

I guess the main question I have is what is better about using VBA's Search function instead of MATCH()? Is it that I can search a whole column? Or does the Search function have more options, etc?

Either way, I tried what you sent me and it appears its doing what I want, so thank you for that. I didn't want to state exactly how my data was arranged since I am only working on the conceptual stage at this point. I prefer to not copy and paste other people's solutions when I actually generate an application, but it's helpful when I'm thinking through the logical flow of things...

THANK YOU!!!

-Tom-

 

[Peter_SSs]

Tom

It just makes sense to me to use a native vba method. I don't know the theory here so I'm guessing but I would think in a vba procedure, Find would be faster than Match though of course in many circumstances any difference in speed is not noticeable.

In the end, so long as the procedure works, and does so speedily enough, I guess the method is not critical.