Excel: Simple macro setting formula question

[azzi2000]

I am trying to get this formula works inside a macro:

I need to set columnD, currentRow = Column E, Current Row + Column K, Current Row
ie:
Cell D2 = Cell E2 + Cell K2
Cell D3 = Cell E3 + Cell K3
....

Please advice,
Thank you.
Dré

 

[PHV]

Take a look at the Offset property of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

Dré - I have already given you the answer to this in my last post in the MSOffice forum. Please take the time to read and try out what people are suggesting before just posting anyway:

the following is all you need:

Range("D2:D3").formula = "=E2+K2"

as I have already explained

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[azzi2000]

xlbo:
Thank you for your reply.
Yes your formula does work. I was referening to get currentow, and cell.

Thank you for your help.
Dré

Code:
CurrentRow = Trim(Str(ActiveCell.Row))
CurrentCol = Trim(Str(ActiveCell.Column))
CurrentRange = "D" & CurrentRow & "" & CurrentRow
Select Case CellText
Case "1"
CurFormula = "=E" & CurrentRow & "+K" & CurrentRow
Range(CurrentRange).Formula = CurFormula '=E2+K2

 

[SkipVought]

Your method is very awkward

    With ActiveCell
        Select Case .Value
           Case 1
              Cells(.Row, "D").Formula = _
                "=" & Cells(.Row, "E").Address(False, False) & "+" & _
                        Cells(.Row, "K").Address(False, False)
        End Select
    End With

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[azzi2000]

There is nothing awkward about it.
It does work, doesn't it ?
Thank you though.
Dré

 

[SkipVought]

Never said it did not work.

R1 = 2
R2 = 10
For r = R1 to R2
  Select Case Cells(r, "A").Value
    Case 1
      Cells(r, "D").Formula = _
         "=" & Cells(r, "E").Address(False, False) & "+" & _
            Cells(r, "K").Address(False, False)
  End select
Next

maybe???

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[azzi2000]

Thank you.
Dré

 

[azzi2000]

How can I get the value of that cell.
nQty=Cells(r, "E").Address(False, False).Value
but it is not working.

- Also where Can I get a help file withing excel to learn.

Thank you.
Please advice,
Dré

 

[PHV]

where Can I get a help file withing excel
If the F1 don't launch the VBA help when in VBE the repair your installation of office.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbrodsky]

For something like this, I usually use the R1C1 notation.
So something like

Cell D2 = Cell E2 + Cell K2
Cell D3 = Cell E3 + Cell K3

becomes
Cell D2 = "=RC[1] + RC[7]"

and the code becomes

Range("D23").formulaR1C1 = "=RC[1] + RC[7]

 

[pbrodsky]

Azzi

If you are not familiar with th R1C1 notation,
R or C means the same row or column
R3 means row 3 (fixed)
R[3] means the current row +3 (relative)

 

[SkipVought]

...
   nQty = Cells(r, "E").Value 
...

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[azzi2000]

Thank you.
Dré