Help with macros

[Marina]

Forgive me if this posting is in the wrong area of this forum. I would like
to create a macro that inserts a formula into each cell in column I where the
column A is populated for that row.

The formula I'd like to use is: =LOOKUP(B2,'06-2005'!B:B,'06-2005'!J:J)

I want the formula to reflect the previous worksheet (month-1) rather than a
fixed name ('06-2005') putting in a fixed sheet name.

Is this possible? If so, how?

Thanks in advance,
Marina Garrison

[Bob Phillips]

Here is some code

Dim sDate As String
sDate = Format(DateSerial(Year(Date), Month(Date), 0), "mm-yyyy")
Range("I1").Formula = "=LOOKUP(B2," & sDate & "!B:B,'" & sDate &
"'!J:J)"
Range("I1").AutoFill Range("I1").Resize(Cells(Rows.Count,
"A").End(xlUp).Row)


--
HTH

Bob Phillips

"Marina" <[email protected]> wrote in message
news:[email protected]...
> Forgive me if this posting is in the wrong area of this forum. I would
like
> to create a macro that inserts a formula into each cell in column I where
the
> column A is populated for that row.
>
> The formula I'd like to use is: =LOOKUP(B2,'06-2005'!B:B,'06-2005'!J:J)
>
> I want the formula to reflect the previous worksheet (month-1) rather than
a
> fixed name ('06-2005') putting in a fixed sheet name.
>
> Is this possible? If so, how?
>
> Thanks in advance,
> Marina Garrison

[Rowan]

Marina

If I understand you correctly then I think the formula you are looking for
is a VLOOKUP which will return the value in column J from the previous sheet
where the value in Column B is found.

You can use a macro like this:

Sub InsForm()
Dim UsedRows As Long
Dim PrevSheet As String
Dim ColA As Range
Dim Cell As Range

If ActiveSheet.Index > 1 Then
PrevSheet = Sheets(ActiveSheet.Index - 1).Name
UsedRows = Cells(Rows.Count, 1).End(xlUp).Row
Set ColA = Range(Cells(2, 1), Cells(UsedRows, 1))
For Each Cell In ColA
If Cell.Value <> Empty Then
Cell.Offset(0, 8).FormulaR1C1 = _
"=VLOOKUP(RC[-7],'" & PrevSheet & "'!C2:C10,9,0)"
End If
Next Cell
End If
End Sub

which will (as requested) only insert the formula into rows where column A
is populated. Or you can use the code below which will insert a formula into
every row which tests to see that column A is populated before performing the
vlookup. This might be a better option if you are likely to go back and
populate cells in column A which may initially be blank. This version also
tests to see that the lookup value in column B actually exists on the
previous sheet i.e. it will not return #N/A errors.

Sub InsForm2()
Dim UsedRows As Long
Dim PrevSheet As String

If ActiveSheet.Index > 1 Then
PrevSheet = Sheets(ActiveSheet.Index - 1).Name
UsedRows = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 9), Cells(UsedRows, 9)).FormulaR1C1 = _
"=IF(RC[-8]<>"""",IF(ISNA(VLOOKUP(RC[-7],'" _
& PrevSheet & "'!C2:C10,9,0)),"""",VLOOKUP(RC[-7],'" _
& PrevSheet & "'!C2:C10,9,0)),"""")"
End If
End Sub

Hope this helps
Rowan

"Marina" wrote:

> Forgive me if this posting is in the wrong area of this forum. I would like
> to create a macro that inserts a formula into each cell in column I where the
> column A is populated for that row.
>
> The formula I'd like to use is: =LOOKUP(B2,'06-2005'!B:B,'06-2005'!J:J)
>
> I want the formula to reflect the previous worksheet (month-1) rather than a
> fixed name ('06-2005') putting in a fixed sheet name.
>
> Is this possible? If so, how?
>
> Thanks in advance,
> Marina Garrison