From Row and Column to Cell Position

Tom59593

New Member
Joined
Jul 2, 2008
Messages
16
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:
Code:
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-
 
Last edited:

Tom59593

New Member
Joined
Jul 2, 2008
Messages
16
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:

Code:
'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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 2, 2008
Messages
16
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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. :)
 

Forum statistics

Threads
1,082,102
Messages
5,363,146
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top