Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If statement (Excel 2000)

    I need to write a macro that performs this scenario:
    If the month in today's date (B3) is equal to the month in the cell X3, then go to a new file and copy the data from X8:X156 and paste back into the original sheet, cell X6. If they do not match, do nothing. IE. Today's date is 7-21-04 and the date for the July column (column X) is formatted as 7-1-04. The month's would match so the macro would go to the second file and copy the data from the July column (also column x) and paste back into the original file. These file will be in different directories. Can you help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If statement (Excel 2000)

    Try this. Replace the values of the constants at the beginning of the code by the names you have

    Sub Import()
    Const strSourceFile = "C:ExcelTest.xls"
    Const strSourceSheet = "Sheet1"
    Const strTargetSheet = "Sheet2"

    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook

    On Error GoTo ErrHandler

    If Month(Range("B3")) = Month(Range("X1")) Then
    Set wbkTarget = ActiveWorkbook
    Set wbkSource = Workbooks.Open(strSourceFile)
    wbkSource.Worksheets(strSourceSheet).Range("X8:X15 6").Copy _
    wbkTarget.Worksheets(strTargetSheet).Range("X6")
    wbkSource.Close SaveChanges:=False
    End If

    ExitHandler:
    Set wbkSource = Nothing
    Set wbkTarget = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    This is good. Thank you. I will use this macro on different files (112) that are in different directories (4). Is there a way to include a message box that will stop the macro and allow the user to input the specific file name and then the specific directory for both the Source file and the Target file?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    See if the modification to HANS code below does what you want:

    <pre>Option Explicit

    Sub Import()
    Const strSourceSheet = "Sheet1"
    Const strTargetSheet = "Sheet2"

    Dim vSourceFile As Variant, vTargetFile As Variant
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook

    On Error GoTo ErrHandler

    vTargetFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", 1,"Select Target File")
    If vTargetFile = False Then
    Exit Sub
    End If

    vSourceFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", 1,"Select Source File"))
    If vSourceFile = False Then
    Exit Sub
    End If

    Set wbkTarget = Workbooks.Open(vTargetFile)

    If Month(Range("B3")) = Month(Range("X1")) Then
    Set wbkSource = Workbooks.Open(vSourceFile)
    wbkSource.Worksheets(strSourceSheet).Range("X8:X15 6").Copy _
    wbkTarget.Worksheets(strTargetSheet).Range("X6")
    wbkSource.Close SaveChanges:=False
    End If
    wbkTarget.Close SaveChanges:=False

    ExitHandler:
    Set wbkSource = Nothing
    Set wbkTarget = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>


    If you want to loop, asking for files until the user cancels the request for a target file, then you could use this:

    <pre>Sub Import()
    Const strSourceSheet = "Sheet1"
    Const strTargetSheet = "Sheet2"

    Dim vSourceFile As Variant, vTargetFile As Variant
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook

    On Error GoTo ErrHandler

    Do While True
    vTargetFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", 1,"Select Target File"))
    If vTargetFile = False Then
    Exit Sub
    End If

    vSourceFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", 1,"Select Source File"))
    If Not vSourceFile = False Then

    Set wbkTarget = Workbooks.Open(vTargetFile)

    If Month(Range("B3")) = Month(Range("X1")) Then
    Set wbkSource = Workbooks.Open(vSourceFile)
    wbkSource.Worksheets(strSourceSheet).Range("X8:X15 6").Copy _
    wbkTarget.Worksheets(strTargetSheet).Range("X6")
    wbkSource.Close SaveChanges:=False
    End If
    wbkTarget.Close SaveChanges:=False
    End If
    Loop

    ExitHandler:
    Set wbkSource = Nothing
    Set wbkTarget = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If statement (Excel 2000)

    You can use the InputBox function for simplistic prompts:

    Dim strFile As String
    strFile = InputBox("Enter file name")
    If strFile = "" Then Exit Sub

    InputBox does not display a dialog such as File | Open, only a simple box in which the user can type.

    Excel has Application.GetOpenFileName to ask the user for a file name in a dialog:

    Dim varFile As Variant
    varFile = Application.GetOpenFileName("Excel workbooks (*.xls),*.xls")
    If varFile = False Then Exit Sub

    See <post#=383923>post 383923</post#> for code to ask the user to select a folder.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    I have decided that an IF statement will do the same task as this macro. Please review:
    =IF($B$1=W$3,'[BSD-NE 06 04 data Cindy.xls]Link'!W8,'Branch Summary - 60 mos'!P35/1000)

    B1 holds today's date. IE =Now()
    What I want to do is take today's date minus one month. In other words, the numbers for June are not available until July.
    How can I change the If statement to subtract one month from B1? Thank you.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If statement (Excel 2000)

    NOW() returns the current date plus the current time. It is better to use =TODAY(). Comparing NOW() to W3 will only return TRUE if the date AND the time match.

    The date exactly one month before today is

    =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    or, with =TODAY() in cell B1:

    =DATE(YEAR(B1),MONTH(B1)-1,DAY(B1))

    If you want the first of the previous month, use 1 instead of DAY(TODAY()) c.q. DAY(B1)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    <pre>=IF(DATE(YEAR($B$1),MONTH($B$1)-1,DAY($B$1))=W$3,'[BSD-NE 06 04 data Cindy.xls]Link'!W8,'Branch Summary - 60 mos'!P35/1000)
    </pre>

    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    I keep getting an error message.
    $b$1 =Today()
    $w3 = 6/1/04
    $x3 = 7/1/04
    $y3 = 8/1/04, etc

    What is wrong?
    =IF($W3=date(year($b$1),month($b$1)-1,day($b$1),('[BSD-NE 06 04 data Cindy.xls]Link'!W8,'Branch Summary - 60 mos'!P35/1000)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If statement (Excel 2000)

    There is a closing bracket ) missing after DAY($B$1), and a superfluous opening bracket ( before '[BSD-NE ...

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement (Excel 2000)

    You have an extra open parentheses, and you are missing a closed parentheses. Try this:

    =IF($W3=DATE(YEAR($B$1),MONTH($B$1)-1,DAY($B$1)),'[BSD-NE 06 04 data Cindy.xls]Link'!W8,'Branch Summary - 60 mos'!P35/1000)

    Steve

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: If statement (Excel 2000)

    Hi Steve,

    Just an observation and a query: What if 'today' is the 31st, for example, and last month didn't have one? Should the formula do nothing, or return the last valid date for the month?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If statement (Excel 2000)

    The formula will actually return the first day of the current month.
    Legare Coleman

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement (Excel 2000)

    If the day of TODAY is > then the last day of last month, then the formula:

    DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

    will give a date in the current month, the number of days past the last day of the month.

    For example:
    If today is 3/31/2004 then you will get from the formula 3/2/2004 since "2/31/2004" is considered 2 days past 2/29/2004, similarly
    5/31/2004 will give 5/1/2004 (="4/31/2004").

    If you want the last day (if the day is past the last day of the prev month then you can take the date of the result and use that as the month and use the day as zero, which will calc the last day of the previous month ("5/0/2004" = 4/30/2004).

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If statement (Excel 2000)

    Not in the case of March looking at Feb, you will get Mar 3 (or Mar 2 in Leap years) if you calc with Mar 31.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •