ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Condition IF (https://www.excelbanter.com/excel-discussion-misc-queries/13183-condition-if.html)

Jeff
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

Otto Moehrbach
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" wrote in message
...
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
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" wrote in message
...
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

Otto Moehrbach
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 . Remove the "nop" from this
address. HTH Otto






"Jeff" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 04:13 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com