This is stupid but I need help with VBA code in Excel. I'm a web developer and as I'm writing these functions I keep reverting back to PHP code structure which obviously doesn't work. Not gonna lie, it's my sister's project, I'm doing it because I have no idea why she is a CS minor and she doesn't have a damn clue what's going on in this particular class. Luckily the class is insignificant to her graduation etc, she just needs this crap done which is why my parents asked me to finish this shit for her. The majority of the code is already done...
For shit's and giggles, any help on this project?
1. Load the sales data into a two-dimensional array using the sub procedure Load_Array (from a data file) and item prices into a one-dimensional array. You will also create a string array for the cities and a string array for the items the stores sell.
Private Sub Load_Arrays(sales() As Integer, itemcost() As Double, city() As String, items() As String)
Dim m As Integer, n As Integer, k As Integer, l As Integer, i As Integer
Open "z:\cs385\Spring06\Sales_Data.txt" For Input As #1
Do Until EOF(1) = True
For m = 1 To UBound(sales)
For n = 1 To UBound(sales, 2)
Input #1, sales(m, n)
Next
Next
Loop
Close #1
itemcost(1) = 12
itemcost(2) = 17.95
itemcost(3) = 95
itemcost(4) = 86.5
itemcost(5) = 78
city(1) = "New York "
city(2) = "Chicago "
city(3) = "Los Angeles"
items(1) = "Lamp "
items(2) = "Chair"
items(3) = "Sofa "
items(4) = "Table"
items(5) = "Desk "
End Sub
2. Write the initial data to the spreadsheet by city, sales item name, sales item, and price. You should also provide column headings for the above mentioned information which should be bold and underlined.
For i = 1 To UBound(Company_Array)
Sheet1.Cells(x, 1) = "City = " & City_Array(i)
x = x + 5
For j = LBound(Company_Array, 2) To UBound(Company_Array, 2)
Sheet1.Cells(y, 2) = Items_Array(j)
Sheet1.Cells(y, 3) = Company_Array(i, j)
Sheet1.Cells(y, 4) = FormatCurrency(Item_Cost(j), 2)
Store_Array(i) = Store_Array(i) + Item_Cost(j) * Company_Array(i, j)
Item_Array(j) = Item_Array(j) + Company_Array(i, j)
Store_Items(i) = Store_Items(i) + Company_Array(i, j)
y = y + 1
Next
Next
3. Write sales by store to the spreadsheet which includes sales and revenue for each store (output should include the stores’ city). You should also provide column headings which should be bold and underlined.
For i = 1 To UBound(Store_Array)
Sheet1.Cells(z, 6) = City_Array(i)
Sheet1.Cells(z, 7) = Store_Items(i)
Sheet1.Cells(z, 8) = FormatCurrency(Store_Array(i), 2)
SumRevenue = SumRevenue + Store_Array(i)
z = z + 1
Next
4. Write number of items sold to the spreadsheet which includes item name, number sold, and revenue generate. You should also provide column headings which should be bold and underlined.
For i = 1 To UBound(Item_Array)
Sheet1.Cells(z, 6) = Items_Array(i)
Sheet1.Cells(z, 7) = Item_Array(i)
Sheet1.Cells(z, 8) = FormatCurrency(Item_Array(i) * Item_Cost(i), 2)
SumItems = SumItems + Item_Array(i)
z = z + 1
Next
5. Write total number of items sold to the spreadsheet.
6. Write total revenue for all stores to the spreadsheet.
Range("g16").Value = "Total number of items sold = " & SumItems
Range("g17").Value = "Total Revenue for all stores = " & FormatCurrency(SumRevenue, 2)
For shit's and giggles, any help on this project?
1. Load the sales data into a two-dimensional array using the sub procedure Load_Array (from a data file) and item prices into a one-dimensional array. You will also create a string array for the cities and a string array for the items the stores sell.
Private Sub Load_Arrays(sales() As Integer, itemcost() As Double, city() As String, items() As String)
Dim m As Integer, n As Integer, k As Integer, l As Integer, i As Integer
Open "z:\cs385\Spring06\Sales_Data.txt" For Input As #1
Do Until EOF(1) = True
For m = 1 To UBound(sales)
For n = 1 To UBound(sales, 2)
Input #1, sales(m, n)
Next
Next
Loop
Close #1
itemcost(1) = 12
itemcost(2) = 17.95
itemcost(3) = 95
itemcost(4) = 86.5
itemcost(5) = 78
city(1) = "New York "
city(2) = "Chicago "
city(3) = "Los Angeles"
items(1) = "Lamp "
items(2) = "Chair"
items(3) = "Sofa "
items(4) = "Table"
items(5) = "Desk "
End Sub
2. Write the initial data to the spreadsheet by city, sales item name, sales item, and price. You should also provide column headings for the above mentioned information which should be bold and underlined.
For i = 1 To UBound(Company_Array)
Sheet1.Cells(x, 1) = "City = " & City_Array(i)
x = x + 5
For j = LBound(Company_Array, 2) To UBound(Company_Array, 2)
Sheet1.Cells(y, 2) = Items_Array(j)
Sheet1.Cells(y, 3) = Company_Array(i, j)
Sheet1.Cells(y, 4) = FormatCurrency(Item_Cost(j), 2)
Store_Array(i) = Store_Array(i) + Item_Cost(j) * Company_Array(i, j)
Item_Array(j) = Item_Array(j) + Company_Array(i, j)
Store_Items(i) = Store_Items(i) + Company_Array(i, j)
y = y + 1
Next
Next
3. Write sales by store to the spreadsheet which includes sales and revenue for each store (output should include the stores’ city). You should also provide column headings which should be bold and underlined.
For i = 1 To UBound(Store_Array)
Sheet1.Cells(z, 6) = City_Array(i)
Sheet1.Cells(z, 7) = Store_Items(i)
Sheet1.Cells(z, 8) = FormatCurrency(Store_Array(i), 2)
SumRevenue = SumRevenue + Store_Array(i)
z = z + 1
Next
4. Write number of items sold to the spreadsheet which includes item name, number sold, and revenue generate. You should also provide column headings which should be bold and underlined.
For i = 1 To UBound(Item_Array)
Sheet1.Cells(z, 6) = Items_Array(i)
Sheet1.Cells(z, 7) = Item_Array(i)
Sheet1.Cells(z, 8) = FormatCurrency(Item_Array(i) * Item_Cost(i), 2)
SumItems = SumItems + Item_Array(i)
z = z + 1
Next
5. Write total number of items sold to the spreadsheet.
6. Write total revenue for all stores to the spreadsheet.
Range("g16").Value = "Total number of items sold = " & SumItems
Range("g17").Value = "Total Revenue for all stores = " & FormatCurrency(SumRevenue, 2)
Comment