Using variables as cell coordinates

[Shurperro]

Hello everyone!

First and foremost, please keep in mind I'm a complete noob in excel programming, so don't get frustrated if I my doubts are a bit silly.

I'm trying to work around a problem that might seem easy, but I've really no idea what I'm doing wrong. This is what I want to achieve:
1. Keep record of the "sold amount" and the "money collected" just by introducing those values in two cells ("entry cells"), so that in another two cells ("output cells") there is a sum of every value ever introduced in the first two cells.
2. Change the column coordinates of the "output cells" accordingly to the month I have selected in a list, so that every month has a separate column with its "total record".
3. Change the row coordinates of the "output cells" accordingly to a Membership ID Number, also introduced manually in another cell.

I managed to work out the first objective, but I'm incapable of doing the last 2. What I tried to do is creating 3 variables: one for the row coordinate (z), one for the "sold amount" column coordinate (x) and another for the "money collected" column coordinate (y). Then, use a formula in two cells in a separate worksheet ("Random") that gives me the appropiate column coordinate and set the value of x and y to the value of this cells (I'm sure theres is a way to do this in-code, but as I said before, I'm a complete noob). These formulas are working, I'm getting the appropiate coordinates for the month I've selected, but, as far as I'm concerned, I'm facing either of these problems:
1. I'm not setting the variables' value correctly.
2. I'm not using the proper syntax to use these variable as coordinates.
3. I have no clue where I am, which is the most probable one.

As asked by jomili, I'm attaching the spreadsheet and explaining exactly what I want it to do:
1. In the "Entry" sheet, I select the month we're in (A7).
1. I introduce the ID Number (A2) and the product that member wants to buy (D2).
2. I introduce the quantity he wants (E2) or the money he wants to expend (F2).
3. Cells E3 and F3 show the quantity and its price. To this point, everything is done using excel formulas. Now comes what I want the VBA code to do.
4. The quantity and price shown in E3 and F3, add up to the total quantity the member has bought this month (column H of the "Database" sheet, for January) and to the total speding he has made this month (column T of the "Database" sheet, for January).
5. Whenever I change the month, the next introduced quantities and prices add up to the its appropiate column in the "Database" sheet.

Option Explicit
Dim quantity As Long
Dim price As Long
Dim adding As Integer
Dim z As Long
Dim x As Integer
Dim y As Integer
z = ActiveWorkbook.Sheets("Entry").Range("A2").Value + 1
x = ActiveWorkbook.Sheets("Random").Range("B1").Value
y = ActiveWorkbook.Sheets("Random").Range("B2").Value


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$2" Or Target.Address = "$E$2" Then
        adding = adding + 1
        
        If adding > 1 Then
            Exit Sub
        End If
        
        quantity = quantity + Sheets("Entry").Range("E3").Value
        ActiveWorkbook.Sheets("Database").Cells(z, x).Value = quantity
        price = price + Sheets("Entry").Range("F3").Value
        ActiveWorkbook.Sheets("Database").Cells(z, y).Value = price
    End If

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    quantity = 0
    price = 0
    adding = 0
    
    If Target.Address = "$F$2" Or Target.Address = "$E$2" Then
        quantity = ActiveWorkbook.Sheets("Base").Cells(z, x).Value
        price = ActiveWorkbook.Sheets("Base").Cells(z, y).Value
    End If

End Sub

Thank you in advance for any helpful comments!!

[jomili]

Shurperro,

Your English is very good. Will you please attach an example of your spreadsheet so we can see it and more easily understand what you're wanting to do?

[Shurperro]

Jomili,

I attached the speadsheet, explained a bit better in the first post what I want it to do and changed everything to English so it is better understood. Thanks for your reply!!

[jomili]

Okay, so my first question is what happens if your customer changes his mind, or you input a wrong value? If you're triggering the action from a Worksheet_Change event, it'll be hard to have a rollback procedure. Instead of a Worksheet_Change, would you be open to a button to initiate saving the values?

Second, going to Structure, your database sheet may need some changes. For instance, say John Doe inputs a quantity of 2 for January. So, we dutifully put in a "2" in column H, and in column F we have a sum formula which adds up the values in the Quantity columns. But we need to keep track of the fact that this is just January quantities. It's easy to change the title in H1 to say "Jan Qty 1", and update our formula in F to look for "Jan", but then in February Erick Bana inputs a value of 3. If we put the value in Column H. we'd need to change our title to "Feb Qty 1", which would mess up John Doe's totals.

So seems to me we'd need a sheet for each month at the very least. Or, a sheet for each Name/Surname combo.

IF we do a sheet for each Name/Surname combo, we could do it as a 3-column sheet, with Month, Qty, and Spending as the headers. Next to that we could have a pivot (or SumIF formulas) to populate a table to show you the monthly totals.

I won't start on anything until you decide the way you want to handle it.

[Shurperro]

1. I thought of the first problem you mentioned, and that's why theres also a "calculator" put aside in the "Entry" sheet, so that you can do calculations there before entering the definitive value. But obviously your idea of a button that initiate the sequence is waaaay better, so of course I'd be open to that.
2. Maybe I was a little careless when naming the columns in the "Database" sheet, and I sure didn't explain them at all. Quantity 1-12 represent the 12 months of the year. So being in January, the sold quantities would add up in column H. Come February, they'd add up in column I, etc. The same goes for the columns Spending 1-12. That's what I was refering I was having problems with, creating a way to change the column this "monthly add up" goes to just by selecting the month in the "Entry" sheet. There's more than 300 hundred members in our association, so I don't think it'd be too handy nor tidy to have separete sheets for each one.

Okay, so my first question is...

Just to explain myself a little better, I've made a little spreadsheet where the basic function I want to make works just fine (you enter a value in A1 and it adds up to previous value in A2), I just want to be able to change where the total sum goes to (A2 in this spreadsheet) depending on the month and the member ID number.

Option Explicit
Dim quantity As Long
Dim adding As Integer
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        adding = adding + 1
        
        If adding > 1 Then
            Exit Sub
        End If
        
        quantity = quantity + Sheets("Hoja1").Range("A1").Value
        Sheets("Hoja1").Range("A2").Value = quantity
    End If

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    quantity = 0
    adding = 0
    
    If Target.Address = "$A$1" Then
        quantity = Sheets("Hoja1").Range("A2").Value
        
    End If

End Sub

[jomili]

Okay, I think I understand. So if John Doe inputs 3 on January 1, value at intersection of John Doe and Quantity 1 (AKA "Jan Qty") would be 3. If John Doe later inputs 4 (say, on January 28th) value value at intersection of John Doe and Quantity 1 (AKA "Jan Qty") would be 7 (3 initial plus 4), whereas if he waited until February 3 then "Jan Qty" would be 3 and "Feb Qty" would be 4. Is that they way you invision it?

Okay, more questions:
You mentioned "more than 300 hundred members in our association"; would you want those prepopolated in your database sheet, or would you want your database sheet to be populated by values from B2 and C2? And if so, would you want the ID Number (A2) to automatically increment with each new entry?
What about mispellings? If I accidentally type "John Dor" instead of "John Doe", does that get caught, or is John Doe ID Number 1, and John Dor is ID Number X? Or as John Doe should I always remember my ID Number is 1? And what DO you use the ID Number for? What's it's purpose?

For columns F and G, would you want the values to be this month's totals, or would you rather be able to select a month and see the totals by person in F and G?

[Shurperro]

That's exactly how I pictured it, you couldn't have explained it better.

Every member of the association must bring his Member ID Card to make the purchase, so knowing his ID number is not a problem and every purchase is registered with his ID number, which avoids the problem of mispelling the member's name. I was able to make the member's name appear in B2 and C2 when you put his ID number in A2, but not the other way around, maybe you could help me with that too, I think it'd be handy.

Regarding the population of the database, I'd rather fill it in manually, because I have already another spreadsheet with it, so it'd be just a matter of copy&paste. Also, the rate of new members is quite low, so I think there is no need of implementing a function for this purpose, I can easily put the new members in the database manually.

I wanted columns F and G of the database sheet to be the mean monthly of the member's purchased quantity and spending. I didn't put the formulas in just because I was first trying to solve the problem you're helping me with.

[jomili]

Okay, gotcha. I'll resolve the reverse lookup for you, and leave you to handle columns F and G.

[Shurperro]

Much obliged! You'd be my actual hero if you solved this, It's been driving me nuts for days

[jomili]

Sorry, didn't get to resolve the reverse lookup, and have to go. See what I've done so far, see if that works for you. I've tried to comment everything so you can change as required. All the code is in Module 1. I added a msgbox to tell you when the process finishes so you'll know it's done.

[Shurperro]

Wow, never mind about the reverse lookup, you did an amazing job!! This is exactly what I wanted. I'm really, really grateful. I hope it didn't cause you much trouble. Again, thank you very much!!

[LeoTaxi]

With Totals and average

Sub test()
mymonth = Month(Date)
If mymonth = 1 Then mymonth2 = 12
If mymonth > 1 Then mymonth2 = mymonth - 1
mycol = mymonth + 7
mycol2 = mymonth + 19
myrow = Application.Match(Range("A2"), Sheets("Database").Range("A:A"), 0)
With Sheets("Database")
    .Cells(myrow, mycol) = .Cells(myrow, mycol) + Range("E2")
    .Cells(myrow, mycol2) = .Cells(myrow, mycol2) + Range("F2")
    .Cells(myrow, 4) = Application.Sum(.Range(.Cells(myrow, 8), .Cells(myrow, mycol)))
    .Cells(myrow, 5) = Application.Sum(.Range(.Cells(myrow, 20), .Cells(myrow, mycol2)))
    .Cells(myrow, 6) = .Cells(myrow, 4).Value / mymonth2
    .Cells(myrow, 7) = .Cells(myrow, 5).Value / mymonth2
End With
MsgBox Range("E2").Value & ",00g is added by " & Chr(13) & Range("B2").Value & " " & Range("C2").Value & _
    Chr(13) & "for the month: " & Chr(13) & Format(Date, "mmmm")
End Sub

Cheers
Leo

[jomili]

LeoTaxi,
Pretty slick; I like the formatting changes. On the entry page I think you lost some of Shurperro's functionality, namely the two-way lookups in E3 and F3.

Shurperro,
LeoTaxi's code isn't commented, so you might want to study it carefully to understand how it's doing what it's doing. While in this case the "mymonth + 7" and "mymonth + 19" works to select the columns, if you ever need to separate the data tables those values will need to be changed. The "rFind" routine in my code (copied below) can handle that without updating as long as the Header Row stays on the same row.

Also, if someone else ever inherits your workbook, it might be better to have actual formulas in D:G columns in case the new owner hoses whichever macro you use.

Option Explicit

Sub UpdateDB()
    Dim Mo As String
    Dim ID As String
    Dim Qty As Long
    Dim Spnd As Long
    Dim Val As Long
    Dim rFind As Range
    Dim DataRange As Range
    Dim FirstFilteredRow As Long
    
    Application.ScreenUpdating = False
    'Figure what month we want to update
    Mo = Format(Date, "mmm")
    
    'What are our variable amounts?
    Qty = Sheets("Entry").Range("E2").Value
    Spnd = Sheets("Entry").Range("F2").Value
    
    'Whose ID do we want to update?
    ID = Sheets("Entry").Range("A2").Value
    
    Sheets("Database").Activate
    With ActiveSheet
        'Filter our Database with the ID Number
        .Rows(4).AutoFilter Field:=1, Criteria1:=ID
        
        'What Row will we be working in?
        Set DataRange = .Range(.Cells(4, 1), .Cells(.Rows.Count, 1).End(xlUp))
        FirstFilteredRow = DataRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Row
        
        'Quantity first; what column?
        Set rFind = .Range("A4:AE4").Find(What:=Mo & " Qty", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        
        If Not rFind Is Nothing Then
            Val = .Range(ColumnLetters(rFind) & FirstFilteredRow).Value
            
            If Val <> 0 Then
                .Range(ColumnLetters(rFind) & FirstFilteredRow).Value = Val + Qty
            Else
                .Range(ColumnLetters(rFind) & FirstFilteredRow).Value = Qty
            End If
        End If
        
        'Now the Spend; what column?
        Set rFind = .Range("A4:AE4").Find(What:=Mo & " Spending", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        
        If Not rFind Is Nothing Then
            Val = .Range(ColumnLetters(rFind) & FirstFilteredRow).Value
            
            If Val <> 0 Then
                .Range(ColumnLetters(rFind) & FirstFilteredRow).Value = Val + Spnd
            Else
                .Range(ColumnLetters(rFind) & FirstFilteredRow).Value = Spnd
            End If
        End If
        
        
        Sheets("Database").AutoFilterMode = False
    End With
    Sheets("Entry").Activate
    Application.ScreenUpdating = True
    MsgBox "Values Updated"
End Sub


Function ColumnLetters(r As Range) As String
    Dim i As String
    i = r.Address(False, False)
    ColumnLetters = Left(i, Len(i) - Len(Format(r.Row, "0")))
End Function

[Shurperro]

Thank you both for your input, I've deeply studied both codes and they're both amazing, there's so much I have to learn about VBA...

[jomili]

I think I've solved your reverse lookup. See the attached, change values in A2, B2, C2, E2, or F2. All run by worksheet_change.

UPDATE: Spotted an error, fixed in attached and code below.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
    If Not Intersect(Target, Range("E2")) Is Nothing Then
    Application.EnableEvents = False
        Range("A2:F2").Value = Range("A2:F2").Value
        Range("F2").FormulaR1C1 = "=IFERROR(RC[-1]*VLOOKUP(RC[-2],R[-1]C[3]:R[498]C[4],2,FALSE),"""")"
    End If
    
    If Not Intersect(Target, Range("F2")) Is Nothing Then
    Application.EnableEvents = False
        Range("A2:F2").Value = Range("A2:F2").Value
        Range("E2").FormulaR1C1 = "=IFERROR(RC[1]/VLOOKUP(RC[-1],R[-1]C[4]:R[498]C[5],2,FALSE),"""")"
    End If

    If Not Intersect(Target, Range("A2")) Is Nothing Then
    Application.EnableEvents = False
        Range("A2:F2").Value = Range("A2:F2").Value
        Range("B2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Database!C2:C4,2,FALSE),"""")"
        Range("C2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Database!C2:C4,3,FALSE),"""")"
    End If

    If Not Intersect(Target, Range("B2")) Is Nothing Then
    Application.EnableEvents = False
        Range("A2:F2").Value = Range("A2:F2").Value
        Range("C2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Database!C:C[1],2,FALSE),"""")"
        Range("A2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],Database!C:C[1],2,FALSE),"""")"
    End If

    If Not Intersect(Target, Range("C2")) Is Nothing Then
    Application.EnableEvents = False
        Range("A2:F2").Value = Range("A2:F2").Value
        Range("B2").FormulaR1C1 = "=IFERROR(INDEX(Database!C[1], MATCH(Entry!RC[1], Database!C[2], 0),0),"""")"
        Range("A2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],Database!C:C[1],2,FALSE),"""")"
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub