1. 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. 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. [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. 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. 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. Code:
```With RngFind.Offset(0, 9)
.FormulaR1C1 = "=IF(RC[-16]>0,RC[-16],RC[-15])"
.NumberFormat = "#,##0.00"
End With```

7. 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.

8. RngFind.Offset(0, 6).FormulaR1C1 = "=TRIM(MID(RC[-18],18,52))"

9. 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
•