Results 1 to 14 of 14

Thread: Syntax in macro

  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Syntax in macro

    I am creating a macro in File A to copy values from File B into File A, depending on a condition in File B. I tried this, but it doesn't work.

    Range("R1:C1").Select
    If "'File B.xlsx'!R2C2" = "" Then ActiveCell.FormulaR1C1 = "='File B'!R2C3" Else ActiveCell.FormulaR1C1 = "='File B'!R2C4"

    The problem seems to be with testing the condition. I also tried:
    - If IsEmpty ("'File B.xlsx'!R2C2") Then ...
    - If IsNull ("'File B.xlsx'!R2C2") Then ...

    What is the correct syntax?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Try this code running it with File A as the active workbook. You did state that you wanted to copy the values not the formulas.

    HTH,
    Maud

    Code:
    Public Sub TransferData()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    Range("A1").Select
    With wb2.Sheets(1)
    If .Cells(2, 2) = "" Then
        ActiveCell.Value = .Cells(2, 3)
    Else
        ActiveCell.Value = .Cells(2, 4)
    End If
    End With
    End Sub

  4. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for the reply.

    It looks like the copy destination cell in File A is set as Range ("A1") - is that right? Can it be "R1C1"?

    If I want to do multiple copies into various cells with various conditions, do I need to repeat the whole routine, or will this work?

    Public Sub TransferData()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")

    Range("A1").Select
    With wb2.Sheets(1)
    If .Cells(2, 2) = "" Then
    ActiveCell.Value = .Cells(2, 3)
    Else
    ActiveCell.Value = .Cells(2, 4)
    End If
    End With

    Range("A2").Select
    With wb2.Sheets(1)
    If .Cells(2, 2) = "" Then
    ActiveCell.Value = .Cells(2, 5)
    Else
    ActiveCell.Value = .Cells(2, 6)
    End If
    End With


    End Sub

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    You could write repetitive code but more efficiently, you could loop the same code.

    Change the loop limit 5 to as many times as you need. The following example will loop 5 times from A1 to A5 (File A) using the condition in B2 (File B) to select the conditional responses in the consecutive cells C2 thru L2 (File B)

    Condition1.png

    condition2.png

    Code:
    Public Sub TransferData()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
    J = 1
    For I = 1 To 5  'CHANGE LOOP LIMIT
    Cells(1, I).Select
        J = J + 2
        If .Cells(2, 2) = "" Then
            ActiveCell.Value = .Cells(2, J)
        Else
            ActiveCell.Value = .Cells(2, J + 1)
        End If
    Next I
    End With
    End Sub
    "Exit, stage left"
    Last edited by Maudibe; 2014-02-04 at 06:35. Reason: SnagglePuss Quote

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,202
    Thanks
    14
    Thanked 330 Times in 323 Posts
    Murgatroyd
    Are you trying to enter formulas or values?
    You don't have to use formulaR1C1, you can also use just the Formula property and enter the formula more conventionally. The R1C1 works better for relative referencing, but if you are going to do absolute referencing the Formula property works as well.

    If you are going to a block from one to another, a copy-paste or copy-pastevalue could be more efficient than looping.

    Maudibe,
    It is more more efficient code not to select cells in code. I suggest using something like:

    Code:
    Option Explicit
    Public Sub TransferData()
    dim I as integer
    dim J as integer
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
    J = 1
    For I = 1 To 5  'CHANGE LOOP LIMIT
        J = J + 2
        If .Cells(2, 2) = "" Then
            Activesheet.Cells(1, I).Value = .Cells(2, J)
        Else
            Activesheet.Cells(1, I).Value = .Cells(2, J + 1)
        End If
    Next I
    End With
    End Sub
    In addition I explicitly added the Activesheet to the cell reference which was only implicitly there, to ensure that people notice that the source and destination are not referring to the same sheet [It can be easy to miss lack of the period distinction]. I have also dimmed the other variables since this is something I also recommend.

    Steve

  7. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Try

    if len(activecell) < 1 then ...

    I find checking for the length of a cell works best.

    Alan

  8. #7
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Good point sdckapr. Thanks for the tweak

  9. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for your further replies, from which I'm now wondering whether there may be a simpler way to do what I need. Here is a cut down example of what I am trying to do.

    File A:
    FileA.png

    File B:
    FileB.png

    Macro needed in File A:

    IF FileB:I2 <> "" AND FileB:K2 <> ""
    THEN
    A1 = FileB:G2, B1 = FileB:H2, C1 = FileB:I2, A2 = FileB:J2, A3 = FileB:K2, A4 = FileB:L2
    ELSE
    A1 = FileB:A2, B1 = FileB:B2, C1 = FileB:C2, A2 = FileB: D2, A3 = FileB:E2, A4 = FileB:F2

    What is the best way to do this?

  10. #9
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Code:
    Public Sub TransferData_revised()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
        If .Cells(2, 9) <> "" And .Cells(2, 11) <> "" Then
            Cells(1, 1) = .Cells(2, 7)
            Cells(1, 2) = .Cells(2, 8)
            Cells(1, 3) = .Cells(2, 9)
            Cells(2, 1) = .Cells(2, 10)
            Cells(3, 1) = .Cells(2, 11)
            Cells(4, 1) = .Cells(2, 12)
        Else
            Cells(1, 1) = .Cells(2, 1)
            Cells(1, 2) = .Cells(2, 2)
            Cells(1, 3) = .Cells(2, 3)
            Cells(2, 1) = .Cells(2, 4)
            Cells(3, 1) = .Cells(2, 5)
            Cells(4, 1) = .Cells(2, 6)
        End If
    End With
    End Sub
    Last edited by Maudibe; 2014-02-04 at 17:18.

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    If you want to loop multiple lines then:

    Code:
    Public Sub TransferData_revised()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
    Cells.ClearContents
    J = 0
    For I = 1 To 5
        If .Cells(I + 1, 9) <> "" And .Cells(I + 1, 11) <> "" Then
            Cells(I + J, 1) = .Cells(I + 1, 7)
            Cells(I + J, 2) = .Cells(I + 1, 8)
            Cells(I + J, 3) = .Cells(I + 1, 9)
            Cells(I + 1 + J, 1) = .Cells(I + 1, 10)
            Cells(I + 2 + J, 1) = .Cells(I + 1, 11)
            Cells(I + 3 + J, 1) = .Cells(I + 1, 12)
        Else
            Cells(I + J, 1) = .Cells(I + 1, 1)
            Cells(I + J, 2) = .Cells(I + 1, 2)
            Cells(I + J, 3) = .Cells(I + 1, 3)
            Cells(I + 1 + J, 1) = .Cells(I + 1, 4)
            Cells(I + 2 + J, 1) = .Cells(I + 1, 5)
            Cells(I + 3 + J, 1) = .Cells(I + 1, 6)
        End If
        J = J + 4
    Next I
    End With
    End Sub
    FileB1.png

    FileB2.png

    HTH,
    Maud

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    Murgatroyd (2014-02-05)

  13. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks, that works fine.

  14. #12
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts
    This method has a one-to-one correspondence between cells in the source and destination worksheets; e.g.,
    Code:
    Cells(1, 1) = .Cells(2, 7)
    Cells(1, 2) = .Cells(2, 8)
    Cells(1, 3) = .Cells(2, 9)
    However, this results in irregular spacing, because of the fixed size of the cells; e.g., [Mr________] [John______] [Smith_____].

    Can I concatenate the contents of cells from the source into a single wide cell in the destination worksheet, so that it results in "[Mr John Smith]"? I tried a couple of things including this, but it didn't work.

    Code:
    Cells(1, 1) = .Cells(2, 7) & " " & .Cells(2, 8) & " " & .Cells(2, 9)
    Last edited by Murgatroyd; 2014-03-06 at 20:44.

  15. #13
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Snaglepus,

    You had the right formula but there were uneeded spaces that were removed. Did you place the revised line in both the If and Else statements? Here is the code for both single and multiple conversions

    Maud

    Snaglepus.png

    Code:
    Public Sub TransferData_Single()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
        If .Cells(2, 9) <> "" And .Cells(2, 11) <> "" Then
            Cells(1, 1) = .Cells(2, 7) & " " & .Cells(2, 8) & " " & .Cells(2, 9)
            Cells(2, 1) = .Cells(2, 10)
            Cells(3, 1) = .Cells(2, 11)
            Cells(4, 1) = .Cells(2, 12)
        Else
            Cells(1, 1) = .Cells(2, 1) & " " & .Cells(2, 2) & " " & .Cells(2, 3)
            Cells(2, 1) = .Cells(2, 4)
            Cells(3, 1) = .Cells(2, 5)
            Cells(4, 1) = .Cells(2, 6)
        End If
    End With
    End Sub
    
    Public Sub TransferData_Multiple()
    Dim wb2 As Workbook
    Set wb2 = Workbooks("File B.xlsx")
    With wb2.Sheets(1)
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Cells.ClearContents
    J = 0
    For I = 1 To LastRow - 1
        If .Cells(I + 1, 9) <> "" And .Cells(I + 1, 11) <> "" Then
            Cells(I + J, 1) = .Cells(I + 1, 7) & " " & .Cells(I + 1, 8) & " " & .Cells(I + 1, 9)
            Cells(I + 1 + J, 1) = .Cells(I + 1, 10)
            Cells(I + 2 + J, 1) = .Cells(I + 1, 11)
            Cells(I + 3 + J, 1) = .Cells(I + 1, 12)
        Else
            Cells(I + J, 1) = .Cells(I + 1, 1) & " " & .Cells(I + 1, 2) & " " & .Cells(I + 1, 3)
            Cells(I + 1 + J, 1) = .Cells(I + 1, 4)
            Cells(I + 2 + J, 1) = .Cells(I + 1, 5)
            Cells(I + 3 + J, 1) = .Cells(I + 1, 6)
        End If
        J = J + 4
    Next I
    End With
    End Sub

  16. #14
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    531
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for your reply. I tried again and it worked this time ... must have had a typo before.

Posting Permissions

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