Condition IF

Hello,

I have in workbook “B� a VBA macro with a condition “IF� to copy values from
workbook “Forecast Template.xls.� This condition is triggered by the current
date.
Today, the workbook “B� row D13, the condition “IF� applies. Therefore I
copied values D15:D24. However, tomorrow the conditions “IF� will no longer
apply. How can I keep in values D15:D24 ?

Thank you inadvance for any help,
Jeff

Jeff
It's not so easy to follow what you are saying. Please post your macro.
Not your file, just the text of the macro. HTH Otto
"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have in workbook "B" a VBA macro with a condition "IF" to copy values
> from
> workbook "Forecast Template.xls." This condition is triggered by the
> current
> date.
> Today, the workbook "B" row D13, the condition "IF" applies. Therefore I
> copied values D15:D24. However, tomorrow the conditions "IF" will no
> longer
> apply. How can I keep in values D15:D24 ?
>
> Thank you inadvance for any help,
> Jeff
>
>

Hi Otto,

Sub Auto_Open()
Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks("FORECAST TEMPLATE.xls")
On Error GoTo 0
If wb Is Nothing Then
MsgBox "Workbook B not open"
Else
If ThisWorkbook.ActiveSheet.Range("R13").Value = _
wb.ActiveSheet.Range("E1").Value Then
'run macro
Range("R14").Select
Range("R14").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$D$5,0)"
Range("R15").Select
Range("R15").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$8,0)"
Range("R16").Select
Range("R16").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$9,0)"
Range("R17").Select
Range("R17").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$10,0)"
Range("R18").Select
Range("R18").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$11,0)"
Range("R19").Select
Range("R19").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$12,0)"
Range("R20").Select
Range("R20").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$13,0)"
Range("R21").Select
Range("R21").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$14,0)"
Range("R22").Select
Range("R22").Formula = "=IF(R15='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$15,0)"
Range("R24").Select
Range("R24").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$18,0)"
Range("R25").Select
Range("R25").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$19,0)"
Range("R26").Select
Range("R26").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$20,0)"
Range("R27").Select
Range("R27").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$21,0)"
Range("R28").Select
Range("R28").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$22,0)"
Range("R29").Select
Range("R29").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$23,0)"
Range("R30").Select
Range("R30").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$24,0)"
Range("R31").Select
Range("R31").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$25,0)"
Range("R32").Select
Range("R32").Formula = "=IF(R13='[FORECAST
TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$26,0)"
End If
End If

"Otto Moehrbach" wrote:

> Jeff
> It's not so easy to follow what you are saying. Please post your macro.
> Not your file, just the text of the macro. HTH Otto
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have in workbook "B" a VBA macro with a condition "IF" to copy values
> > from
> > workbook "Forecast Template.xls." This condition is triggered by the
> > current
> > date.
> > Today, the workbook "B" row D13, the condition "IF" applies. Therefore I
> > copied values D15:D24. However, tomorrow the conditions "IF" will no
> > longer
> > apply. How can I keep in values D15:D24 ?
> >
> > Thank you inadvance for any help,
> > Jeff
> >
> >
>
>
>

Jeff
To start with, delete all the
Range(xxxx).Select
lines in your code. You do not need to select a cell to put something in
the cell.
As I read your code, your If statement checks to see if R13 of ThisWorkbook
equals E1 of the wb workbook. If it does, then you want to insert a bunch
of formulas into a bunch of cells of ThisWorkbook. And each of those
formulas incorporates essentially the same IF statement and if that IF
statement is true then, the content of one cell in one workbook is copied to
a cell in the other workbook.
My question is this. Is it your intent to place those formulas into those
cells or is it your intent to do the copying task? IOW, do you want those
formulas in those cells or do you want the resulting values in those cells?

I also do not understand what you are saying when you say:
Today, the workbook "B" row D13, the condition "IF" applies. Therefore I
copied values D15:D24. However, tomorrow the conditions "IF" will no longer
apply. How can I keep in values D15:D24 ?
On second thought, I think I now understand what you are saying. Because
you placed formulas in those cells, tomorrow they will evaluate to "0". Is
that the problem? The solution is to not place formulas in those cells.
Simply do the copy task wherein the values are copied and there are no
formulas involved. Something like:
With Windows(wp)
.Range("D5").Copy [R14]
.Range("B8").Copy [R15]
'And so on
End With

Note that you must have a space after the word "Copy".
Note also that, as written, the D5 and D8, etc, are in the wp workbook and
the R14 & R15 & etc are in ThisWorkbook.
If you need further help with this, if you wish, you can contact me direct.
My email address is [email protected]. Remove the "nop" from this
address. HTH Otto






"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hi Otto,
>
> Sub Auto_Open()
> Dim wb As Workbook
>
> On Error Resume Next
> Set wb = Workbooks("FORECAST TEMPLATE.xls")
> On Error GoTo 0
> If wb Is Nothing Then
> MsgBox "Workbook B not open"
> Else
> If ThisWorkbook.ActiveSheet.Range("R13").Value = _
> wb.ActiveSheet.Range("E1").Value Then
> 'run macro
> Range("R14").Select
> Range("R14").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$D$5,0)"
> Range("R15").Select
> Range("R15").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$8,0)"
> Range("R16").Select
> Range("R16").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$9,0)"
> Range("R17").Select
> Range("R17").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$10,0)"
> Range("R18").Select
> Range("R18").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$11,0)"
> Range("R19").Select
> Range("R19").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$12,0)"
> Range("R20").Select
> Range("R20").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$13,0)"
> Range("R21").Select
> Range("R21").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$14,0)"
> Range("R22").Select
> Range("R22").Formula = "=IF(R15='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$B$15,0)"
> Range("R24").Select
> Range("R24").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$18,0)"
> Range("R25").Select
> Range("R25").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$19,0)"
> Range("R26").Select
> Range("R26").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$20,0)"
> Range("R27").Select
> Range("R27").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$21,0)"
> Range("R28").Select
> Range("R28").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$22,0)"
> Range("R29").Select
> Range("R29").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$23,0)"
> Range("R30").Select
> Range("R30").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$24,0)"
> Range("R31").Select
> Range("R31").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$25,0)"
> Range("R32").Select
> Range("R32").Formula = "=IF(R13='[FORECAST
> TEMPLATE.xls]cashmngt'!$E$1,'[FORECAST TEMPLATE.xls]cashmngt'!$C$26,0)"
> End If
> End If
>
> "Otto Moehrbach" wrote:
>
>> Jeff
>> It's not so easy to follow what you are saying. Please post your
>> macro.
>> Not your file, just the text of the macro. HTH Otto
>> "Jeff" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have in workbook "B" a VBA macro with a condition "IF" to copy values
>> > from
>> > workbook "Forecast Template.xls." This condition is triggered by the
>> > current
>> > date.
>> > Today, the workbook "B" row D13, the condition "IF" applies. Therefore
>> > I
>> > copied values D15:D24. However, tomorrow the conditions "IF" will no
>> > longer
>> > apply. How can I keep in values D15:D24 ?
>> >
>> > Thank you inadvance for any help,
>> > Jeff
>> >
>> >
>>
>>
>>