Condition IF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 (e-mail address removed). Remove the "nop" from this
address. HTH Otto






Jeff said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formatting match month 2
Copy Row 3
copy conditional formulas that reference one cell 2
help on conditional 2
@Sum Conditional Values within a Macro 1
Cell values to an array, then array transpose to another workbook 8
macro - copy/add to another sheet 1
Using a conditional function to sum the difference between two col 3

Back
Top