Excel formula to VBA code conversion

[secure]

Hi all,
I just registered to this forum, sorry to start with a question.
I have written two formulas in excel and want to extend to many cells in a certain interval and generalize to other similar worksheets. So, I need to write them as vba code. The formulas:

=INDEX('sheet1' !$A2:$BI$7000,M ATCH(MAX('sheet 1'!I2:I7),'shee t1'!I2:I7,0),CO LUMN('sheet1'!A 1))

=IF(C3=W$3,100, IF(C3<W$4,TREND ($V$3:$V$4,W$3: W$4,C3),IF(C3=W $4,85,IF(C3<W$5 ,TREND(V$4:V$5, W$4:W$5,C3),IF( C3=W$5,5,IF(C3< W$6,TREND($V$5: $V$6,W$5:W$6,C3 ),0))))))

I appreciate if any1 can help me to come up with vba codes.

thanks in advance

[Guido Geurs]

dear,

I hope this will help You:

   If Range("C3") = Range("W$3") Then
      ActiveCell = 100
   Else
      If Range("C3") < Range("W$4") Then
         ActiveCell.Value = WorksheetFunction.Trend("$V$3:$V$4,W$3:W$4,C3")
      Else
         If Range("C3") = Range("W$4") Then
            ActiveCell = 85
         Else
            If Range("C3") < Range("W$5") Then
               ActiveCell.Value = WorksheetFunction.Trend("V$4:V$5,W$4:W$5,C3")
            Else
               If Range("C3") = Range("W$5") Then
                  ActiveCell = 5
               Else
                  If Range("C3") < Range("W$6") Then
                     ActiveCell.Value = WorksheetFunction.Trend("$V$5:$V$6,W$5:W$6,C3")
                  Else
                     ActiveCell = 0
                  End If
               End If
            End If
         End If
      End If
   End If

Question:
Range("...") is allways a fix cell (even with or without $).
The input data cells don't' move with the move of the calculation to an other cell.

If you want to execute the formula to a range of cells, you have also to readdress the datacells with "offset"

Is it possible to attach a example of the sheet?


br,

[Guido Geurs]

dear,

IF's on a same item can also be writen as SELECT CASE like this:

  Select Case Range("C3")
      Case Is = Range("W$3")
         ActiveCell = 100
      Case Is < Range("W$4")
         ActiveCell = WorksheetFunction.Trend("$V$3:$V$4,W$3:W$4,C3")
      Case Is = Range("W$4")
         ActiveCell = 85
      Case Is < Range("W$5")
         ActiveCell = WorksheetFunction.Trend("V$4:V$5,W$4:W$5,C3")
      Case Is = Range("W$5")
         ActiveCell = 5
      Case Is < Range("W$6")
         ActiveCell = WorksheetFunction.Trend("$V$5:$V$6,W$5:W$6,C3")
      Case Else
         ActiveCell = 0
   End Select

br,

[Guido Geurs]

dear,

is this a solution?

Dim INDEXrange As Range
Dim MATCHrange As Range
Dim MAXrange As Range
Dim COLUMNrange As Range

Set INDEXrange = Worksheets("sheet1").Range("A2:$BI$7000")
Set MATCHrange = Worksheets("sheet1").Range("I2:$I$7,0")
Set MAXrange = Worksheets("sheet1").Range("I2:I7")
Set COLUMNrange = Worksheets("sheet1").Range("A1")

ActiveCell = WorksheetFunction.Index(INDEXrange, _
                        WorksheetFunction.Match( _
                                 WorksheetFunction.Max(MAXrange), _
                                 MATCHrange), _
                        COLUMNrange)

br,