Results 1 to 10 of 10

Thread: Logic

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can anybody spot the problem with my logic, I'm getting #NAME.

    RngFind.Offset(0, 3) = "=IF(RngFind.Offset(0, -7)>0,""Debit"",""Credit"")"

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're trying to set the formula of a cell. This formula should use worksheet functions, but RngFind.Offset(0, -7) is VBA, not a worksheet function. A cell formula doesn't have the slightest idea what RngFind is, and OFFSET works differently in formulas than in VBA.

    You could use

    RngFind.Offset(0, 3).FormulaR1C1 = "=IF(RC[-10]>0,""Debit"",""Credit"")"

    Homework: try to figure out why the formula uses RC[-10].

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787561' date='04-Aug-2009 20:47']Homework: try to figure out why the formula uses RC[-10].[/quote]
    10 columns to the left

    Thanks, that works.

    I'm now using:

    Code:
    		RngFind.Offset(0, 3).FormulaR1C1 = "=IF(RC[-10]>0,""Debit"",""Credit"")"
    		RngFind.Offset(0, 5).FormulaR1C1 = "=TEXT(RC[-10],""dd/mm/yy"")"
    		RngFind.Offset(0, 6).FormulaR1C1 = "=MID(RC[-18],18,52)"
    		RngFind.Offset(0, 8).FormulaR1C1 = "=MID(RC[-19],71,4)&"" ""&MID(RC[-19],77,3)"
    		RngFind.Offset(0, 9).FormulaR1C1 = "=IF(RC[-16]>0,RC[-16],RC[-15])"
    Using R1C1 though, how can I get the result of the formula as opposed to the actual formula?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to set the value, perform the If in VBA:

    Code:
    If RngFind.Offset(0, -7) > 0 Then
      RngFind.Offset(0, 3) = "Debit"
    Else
      RngFind.Offset(0, 3) = "Credit"
    End If

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

    I think I will use paste special values later on which will mean I can still see the formula's by commenting out the command, should I need to.

    RngFind.Offset(0, 9).FormulaR1C1 = "=IF(RC[-16]>0,RC[-16],RC[-15])"

    What is the correct syntax for adding ".NumberFormat = "#,##0.00"" here?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Code:
    With RngFind.Offset(0, 9)
      .FormulaR1C1 = "=IF(RC[-16]>0,RC[-16],RC[-15])"
      .NumberFormat = "#,##0.00"
    End With

  7. #7

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RngFind.Offset(0, 6).FormulaR1C1 = "=MID(RC[-18],18,52)"

    Would it be possible to remove trailing spaces here? I have to take 52 as this is the maximum, but I would like to ignore any space characters at the end.

    TIA.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    RngFind.Offset(0, 6).FormulaR1C1 = "=TRIM(MID(RC[-18],18,52))"

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks

    RngFind.Offset(0, 6).FormulaR1C1 = "=TRIM(MID(RC[-18],18,52)" ")" ...... is what I was trying

Posting Permissions

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