#1   Report Post  
Jeff
 
Posts: n/a
Default 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


  #2   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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




  #3   Report Post  
Jeff
 
Posts: n/a
Default

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





  #4   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 condition Duplicates wal50 Excel Worksheet Functions 1 January 21st 05 07:59 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 02:01 PM
Is it possible to specify multiple condition with SUMIF? Daniel Excel Worksheet Functions 1 November 17th 04 02:38 AM
Add condition to formula Pat Excel Worksheet Functions 0 November 16th 04 01:23 PM
External reference as a condition Ingeniero1 Excel Worksheet Functions 2 November 12th 04 08:10 PM


All times are GMT +1. The time now is 11:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"