PDA

View Full Version : Help with VBA in excel



x[Warrior]x3500
06-30-2010, 09:46 PM
AFAIK VBA is very similar to VB thus i am writing it in this section.

For work, i am writing an excel sheet, but i need to use VBA for a major section.

Provided is the excel sheet.

What it should do:

1. Page 1 is the "overall" sheet where it displays information. Under the "update" button, there is a little table thing (the update button updates that table)

2. Page 2 is where you instert information.

3. click the button and it should go through page 2 and find all of the similar weights (sheet 2, row 2)

4. then it takes all the numbers underneath the weight and averages them all together (then displays it on the bottom table on sheet 1)


thus far, it is doing that correctly... but it will only go up to weight 4.3 (starts at weight 4.0) and i need it to go to weight 6.3

i have no clue why it is not working, any help?


EDIT: there is a "high securtiy alert" whenever i try to upload my rar file (only way i can give u guys the xls sheet) so i will post the VBA script here instead -





Sub AverageWeight(weight As Single)
Dim x, y, z, v, a As Integer
Dim CellStr, CellInt As Integer
Dim StartInt, StartStr As Integer
Dim FivePointNineIntArray(50), w As Integer
Dim TotalInt, FinalInt As Double
Dim add As Integer

add = (weight - 4) * 10 'Changes the column number so that it does not write data over itself
z = 2 'Var that increases the row number while printing the information
For a = 1 To 28 'For loop that goes down the rows, storing the information from the correct columns
w = 1 'Number of the similar weights found (like if it found 2 4.0lbs - w = 2)
StartInt = 51 'Starting row to display on sheet 1
StartStr = 5 + add 'Starting column to display on sheet 1
TotalInt = 0 'Just resets the Total int to zero
CellStr = 2 'Sets which column to start analyzing weights on in the second sheet
For x = 0 To 30
CellInt = 2 'Sets which row to start analyzing weights on in the second sheet
If (Worksheets(2).Cells(CellInt, CellStr).Value = weight) Then 'example: if C1(from sheet 2) = 4.1 then...
FivePointNineIntArray(w) = Worksheets(2).Cells(CellInt + z, CellStr).Value 'stores the value in an array
TotalInt = FivePointNineIntArray(w) + TotalInt 'stores the value in total int / updates total int
FinalInt = TotalInt / w 'takes the total int and gets an average
Worksheets(1).Cells(StartInt + z - 2, StartStr).Value = FinalInt 'displays the final average on page 1
w = w + 1 'increase of w (in the array, and used to calculate average)
End If
CellStr = 1 + CellStr 'Goes to the next column and does it all over again
Next x
z = 1 + z 'Increases the row for printing
For x = 0 To 50
FivePointNineIntArray(x) = 0 'clears array
Next x
Next a
End Sub


Sub Button1_Click()
Dim b, e As Integer
Dim d As Single

d = 4
For b = 1 To 23
AverageWeight (d)
d = d + 0.1 'increases weight by .1
Next b
End Sub