hey guys i am new to vba, but have some experience in C++.
long story short, i want to categorize my credit card purcahses (manually) and then wrtie a function to return the total amount spent on that category.
this is what my spreadsheet looks like:
A B
1 clothes 10
2 clothes 10
3 food 2
4 gym 5
5 clothes 10
6 food 3
7 trans 3
8 food 2
Below is the code I cam up with. Note I only want this function to return the price of clothes. my plan was to write seperate functions for the other categories. let me know if you know an easy to do all categories at once.
Function clothes(category As Variant, price As Variant)
'category will be column A in the spreadsheet and price will be column b in the spreadsheet'
Dim i As Integer
i = 0
clothes = 0
Do While (i < 100)
If category(i) = "clothes" Then
clothes = clothes + price(i)
i = i + 1
Else
i = i + 1
Loop
End Function
My problems:
1. I don't know how to properly pass the columns of the spreadsheet into the fuction. I was doing =clothes(A1:A8, B1:B8) . It didn't like that, and I don't know how to debug properly yet. So I am struggling heh.
2. I don't know if my do while loop is messed up. I used the condition while i < 100 just because I don't know a good way to create a terminating condition based on the size of the column. The size of the column will obviously change from month to month so any input here would be appreciated.
3. How come the function does not need a return type? If I were writing this in C++ my function declaration would be: int clothes(string [8] category, int [8] price) . That way I would know i could only assign a int value to clothes.
Let me know what is wrong with my code and/or how to accomplish what I want to do. I appreciate the feedback in advance.
long story short, i want to categorize my credit card purcahses (manually) and then wrtie a function to return the total amount spent on that category.
this is what my spreadsheet looks like:
A B
1 clothes 10
2 clothes 10
3 food 2
4 gym 5
5 clothes 10
6 food 3
7 trans 3
8 food 2
Below is the code I cam up with. Note I only want this function to return the price of clothes. my plan was to write seperate functions for the other categories. let me know if you know an easy to do all categories at once.
Function clothes(category As Variant, price As Variant)
'category will be column A in the spreadsheet and price will be column b in the spreadsheet'
Dim i As Integer
i = 0
clothes = 0
Do While (i < 100)
If category(i) = "clothes" Then
clothes = clothes + price(i)
i = i + 1
Else
i = i + 1
Loop
End Function
My problems:
1. I don't know how to properly pass the columns of the spreadsheet into the fuction. I was doing =clothes(A1:A8, B1:B8) . It didn't like that, and I don't know how to debug properly yet. So I am struggling heh.
2. I don't know if my do while loop is messed up. I used the condition while i < 100 just because I don't know a good way to create a terminating condition based on the size of the column. The size of the column will obviously change from month to month so any input here would be appreciated.
3. How come the function does not need a return type? If I were writing this in C++ my function declaration would be: int clothes(string [8] category, int [8] price) . That way I would know i could only assign a int value to clothes.
Let me know what is wrong with my code and/or how to accomplish what I want to do. I appreciate the feedback in advance.
