Community archive

Ask something
Discussion Locked

Macros in Excel (Arguments)

0 helpful
1 reply

by rkwvlm

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am writing a macro which works on a template in excel. The template has a matrix which can vary in size. The objective is to calculate basic time from observed times. There are x amount of actions for which times are observed and y number of observations. The actions occur in cycles and therefore the observed times move down a column and back up to the next column. In other words The basic time for the first action in column E would have the formula:

(Last observed time in coloumn D -first observed time in column E).

My Question is: How do I insert a formula into say cell E2 if the macro must determine the last observed time?
An example of code for an ordinary formula is as follows:

ActiveCell.FormulaR1C1 = "=R[-1]C-R[20]C[-1]"

This is however for a fixed amount of actions.

Is there some method by which I can pass an integer variable? For example:

i = { the index of the last cell containing last observed time value)

ActiveCell.FormulaR1C1 = "=R[-1]C-R[i]C[-1]"

Can anyone help or suggest an alternative. It would be appreciated.

1 reply

by gopindia

Sub test1()
'
' test Macro
' Macro recorded 30/10/2002 by gopinathk
' Keyboard Shortcut: Ctrl+K
'
On Error GoTo mod_err

Dim test As String
Dim i As Long
Dim src_chr As Long
Dim J As String
Dim h As String
Dim rows As Long
rows = InputBox("Enter no of rows to be search ", "APPROVED MRC, SEARCH ON REJECTED MRC")
J = InputBox("FOR EXAMPLE TYPE B5", "ENTER THE CELL TO BE SELECT FOR SEARCH")
h = InputBox("FOR EXAMPLE TYPE A TO SELECT - COLUMN A", "ENTER THE COLUMN TO BE SEARCH ")
For i = 1 To rows
Range(J).Select
ActiveCell.Offset(i - 1, 0).Select
Selection.Copy
src_chr = Trim(Selection.Text)
Columns(h).Select
Selection.Replace What:=src_chr, Replacement:="ok", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Next
Exit Sub
mod_err:
MsgBox Err.Description
End Sub

This discussion is closed to new replies.