help me with this code

sham · March 6th, 2005, 08:36 PM

Hi!

I have a data which look like this:

KLM 130205 1.30 SDF 130205 0.90 FGH 130205 2.50
KLM 140205 1.29 SDF 140205 1.20 FGH 140205 2.30
                          SDF 150205 1.29

Now, I need to find the differences between the price of each company.
Below is a code I did but it can only calculate the difference of one company.

Can someone help me to edit the code so it will calculate the difference for other company too?

Sub Get_Differences()
Dim X As Variant, DX() As Single
Dim i As Long
Dim Top_Cell As Range, Bottom_Cell As Range, first_cell As Range, last_cell As Range

Sheet8.Activate

Set Top_Cell = Sheet8.Range("C1")
Set Bottom_Cell = Sheet8.Range("C1").End(xlDown)
Set first_cell = Sheet2.Range("C1")
Set last_cell = Sheet2.Range("C1").End(xlDown)

X = Range(Top_Cell, Bottom_Cell).Value
ReDim DX(1 To Range(Top_Cell, Bottom_Cell).Count)

For i = 2 To UBound(X, 1)
    DX(i - 1) = X(i, 1) - X(i - 1, 1)

    Range(first_cell, last_cell).Cells(i - 1) = DX(i - 1)

Next i

End Sub

thanks in advance

mega · March 7th, 2005, 05:14 AM

You need to do some kind of looping, like "For Each" rows in a sheet. I'm not sure exactly what you are trying to do but one way could be something like this:

Code:

  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet
  Dim i As Single
  Dim X As Excel.Range
  For Each oCell In oSheet.Range("C:C")
    i = 1
    For i = 2 To UBound(X, 1)
      ' Do your processing...
    Next
  Next

- mega
Moving to C# .NET

bdancis · March 7th, 2005, 01:21 PM

Hi--

  You need 2 loops, one to calculate the deltas for each column and the other the cycle through the companies

The code below is my sample solution. Note that the names are more meaningful, use of constants, elimnation of several variables without loss of clarity, use of offsets for relative ranges, only 2 uses of absolute ranges, etc.

  If there is a lot of data, you may want to put all the results for
a company into an array and then copy the array to the target sheet in a single command

Enjoy!

Barry

<code>
Sub Get_Differences()
Dim j As Long
Dim Source_Cell As Range, Target_Cell As Range
Dim Source, Target As Range
Const COMPANY_OFFSET = 3
Const COMPANY_COUNT = 3
Sheet3.Activate

Set Source_Cell = Sheet8.Range("C1")
Set Target_Cell = Sheet2.Range("C1")
For j = 1 To COMPANY_COUNT ' Loop through companies
  'Set range of Source from top to next to bottom of values
  Set Source = Range(Source_Cell,
                     Source_Cell.End(xlDown).Offset(-1, 0))
  Set Target = Target_Cell
  Source.Select
  For Each Cell In Source
    Target.Value = Cell.Offset(1, 0) - Cell.Value
    Set Target = Target.Offset(1, 0) ' Move target to next row
  Next
  Set Source_Cell = Source_Cell.Offset(0, COMPANY_OFFSET)
  Set Target_Cell = Target_Cell.Offset(0, COMPANY_OFFSET)
Next j

End Sub
</code>

sham · March 7th, 2005, 04:00 PM

hi barry, thanks a lot for the code. It works great. Thanks :)